转载

Oracle 12CR2 Install the Sample Schemas

在Oracle 12CR2中在创建数据库时不能安装sample schemas，Oracle将sample schemas的安装脚本存储在GitHub上了，可以通过以下链接地址进行下载

https://github.com/oracle/db-sample-schemas/releases/latest

下载到的是一个zip文件，例如我下载的文件名为db-sample-schemas-12.2.0.1.zip，将其解压

[oracle@jytest1 schema]$ unzip db-sample-schemas-12.2.0.1.zip  [oracle@jytest1 schema]$ ls -lrt total 36584 -rw-r--r--  1 oracle oinstall     2322 Apr  3  2009 drop_sch.sql -rw-r--r--  1 oracle oinstall    16894 Jul  1  2014 sted_mkplug.sql.dbl -rw-r--r--  1 oracle oinstall    27570 Jul  1  2014 mkplug.sql -rw-r--r--  1 oracle oinstall     1685 Nov  6  2015 mk_dir.sql.sbs drwxr-xr-x  2 oracle oinstall        6 Mar 20 19:50 log -rw-r--r--  1 oracle oinstall     1824 Mar 20 19:51 mk_dir.sql drwxr-xr-x  2 oracle oinstall     4096 May 18 17:12 human_resources -rw-r--r--  1 oracle oinstall 37389564 May 18 17:48 db-sample-schemas-12.2.0.1.zip drwxrwxrwx 10 oracle oinstall     4096 May 18 18:46 db-sample-schemas-12.2.0.1

创建sample schemas只需执行db-sample-schemas-12.2.0.1目录下的mksample.sql脚本,其语法如下:

mksample          EXAMPLE TEMP $ORACLE_HOME/demo/schema/log/ localhost:1521/pdb

其参数分别指system,sys,hr,oe,pm,ix,sh,bi用户的密码，与缺省表空间，临时表空间名，以及存储生成日志文件的目录和连接数据库的连接串

[oracle@jytest1 db-sample-schemas-12.2.0.1]$ sqlplus /nolog SQL*Plus: Release 12.2.0.1.0 Production on Thu May 18 18:01:54 2017 Copyright (c) 1982, 2016, Oracle. All rights reserved. SQL> @mksample.sql xxzx7817600 xxzx7817600 hr oe pm id sh bi users temp /u01/app/oracle/product/12.2.0/db/demo/schema/db-sample-schemas-12.2.0.1/log/ jypdb specify password for SYSTEM as parameter 1: specify password for SYS as parameter 2: specify password for HR as parameter 3: specify password for OE as parameter 4: specify password for PM as parameter 5: specify password for IX as parameter 6: specify password for SH as parameter 7: specify password for BI as parameter 8: specify default tablespace as parameter 9: specify temporary tablespace as parameter 10: specify log file directory (including trailing delimiter) as parameter 11: specify connect string as parameter 12: Sample Schemas are being created ... mkdir: cannot create directory 鈥u01/app/oracle/product/12.2.0/db/demo/schema/db-sample-schemas-12.2.0.1鈥 File exists Connected. DROP USER hr CASCADE * ERROR at line 1: ORA-01918: user 'HR' does not exist DROP USER oe CASCADE * ERROR at line 1: ORA-01918: user 'OE' does not exist DROP USER pm CASCADE * ERROR at line 1: ORA-01918: user 'PM' does not exist DROP USER ix CASCADE * ERROR at line 1: ORA-01918: user 'IX' does not exist DROP USER sh CASCADE * ERROR at line 1: ORA-01918: user 'SH' does not exist DROP USER bi CASCADE * ERROR at line 1: ORA-01918: user 'BI' does not exist Connected. SP2-0310: unable to open file "__SUB__CWD__/human_resources/hr_main.sql" Connected. SP2-0310: unable to open file "__SUB__CWD__/order_entry/oe_main.sql" Connected. SP2-0310: unable to open file "__SUB__CWD__/product_media/pm_main.sql" Connected. SP2-0310: unable to open file "__SUB__CWD__/info_exchange/ix_main.sql" Connected. SP2-0310: unable to open file "__SUB__CWD__/sales_history/sh_main.sql" Connected. SP2-0310: unable to open file "__SUB__CWD__/bus_intelligence/bi_main.sql" Connected. not spooling currently SP2-0310: unable to open file "__SUB__CWD__/mkverify.sql"

上面的错误显示不能打开__SUB__CWD__/目录下的相关脚本文件，这里我们需要将__SUB__CWD__/目录使用相关脚本存储的目录的绝对路径来替，下面执行替换

[oracle@jytest1 db-sample-schemas-12.2.0.1]$ perl -p -i.bak -e 's#__SUB__CWD__#'$(pwd)'#g' *.sql */*.sql */*.dat

重新执行

[oracle@jytest1 ~]$ sqlplus /nolog  SQL*Plus: Release 12.2.0.1.0 Production on Thu May 18 19:05:33 2017  Copyright (c) 1982, 2016, Oracle.  All rights reserved.  SQL>@mksample.sql xxzx7817600 xxzx7817600 hr oe pm id sh bi users temp /u01/app/oracle/product/12.2.0/db/demo/schema/db-sample-schemas-12.2.0.1/log/ jypdb  specify password for SYSTEM as parameter 1:  specify password for SYS as parameter 2:  specify password for HR as parameter 3:  specify password for OE as parameter 4:  specify password for PM as parameter 5:  specify password for IX as parameter 6:  specify password for  SH as parameter 7:  specify password for  BI as parameter 8:  specify default tablespace as parameter 9:  specify temporary tablespace as parameter 10:  specify log file directory (including trailing delimiter) as parameter 11:  specify connect string as parameter 12:  Sample Schemas are being created ... ....省略....  Index cardinality (without  LOB indexes)  OWNER  INDEX_NAME                DISTINCT_KEYS   NUM_ROWS ------ ------------------------- ------------- ---------- HR     COUNTRY_C_ID_PK                      25         25 HR     DEPT_ID_PK                           27         27 HR     DEPT_LOCATION_IX                      7         27 HR     EMP_DEPARTMENT_IX                    11        106 HR     EMP_EMAIL_UK                        107        107 HR     EMP_EMP_ID_PK                       107        107 HR     EMP_JOB_IX                           19        107 HR     EMP_MANAGER_IX                       18        106 HR     EMP_NAME_IX                         107        107 HR     JHIST_DEPARTMENT_IX                   6         10 HR     JHIST_EMPLOYEE_IX                     7         10 HR     JHIST_EMP_ID_ST_DATE_PK              10         10 HR     JHIST_JOB_IX                          8         10 HR     JOB_ID_PK                            19         19 HR     LOC_CITY_IX                          23         23 HR     LOC_COUNTRY_IX                       14         23 HR     LOC_ID_PK                            23         23 HR     LOC_STATE_PROVINCE_IX                17         17 HR     REG_ID_PK                             4          4 IX     AQ$_STREAMS_QUEUE_TABLE_Y             0          0 OE     ACTION_TABLE_MEMBERS                132        132 OE     CUSTOMERS_PK                        319        319 OE     CUST_ACCOUNT_MANAGER_IX               4        319 OE     CUST_EMAIL_IX                       319        319 OE     CUST_LNAME_IX                       176        319 OE     CUST_UPPER_NAME_IX                  319        319 OE     INVENTORY_IX                       1112       1112 OE     INV_PRODUCT_IX                      208       1112 OE     ITEM_ORDER_IX                       105        665 OE     ITEM_PRODUCT_IX                     185        665 OE     LINEITEM_TABLE_MEMBERS              132        132 OE     ORDER_ITEMS_PK                      665        665 OE     ORDER_ITEMS_UK                      665        665 OE     ORDER_PK                            105        105 OE     ORD_CUSTOMER_IX                      47        105 OE     ORD_ORDER_DATE_IX                   105        105 OE     ORD_SALES_REP_IX                      9         70 OE     PRD_DESC_PK                        8640       8640 OE     PRODUCT_INFORMATION_PK              288        288 OE     PROD_NAME_IX                       3727       8640 OE     PROD_SUPPLIER_IX                     62        288 OE     PROMO_ID_PK                           2          2 OE     WAREHOUSES_PK                         9          9 OE     WHS_LOCATION_IX                       9          9 PM     ONLINEMEDIA_PK                        9          9 PM     PRINTMEDIA_PK                         4          4 SH     CHANNELS_PK                           5          5 SH     COSTS_PROD_BIX                        0          0 SH     COSTS_TIME_BIX                        0          0 SH     COUNTRIES_PK                         23         23 SH     CUSTOMERS_GENDER_BIX                  2          5 SH     CUSTOMERS_MARITAL_BIX                11         18 SH     CUSTOMERS_PK                      55500      55500 SH     CUSTOMERS_YOB_BIX                    75         75 SH     DR$SUP_TEXT_IDX$RC SH     DR$SUP_TEXT_IDX$X                     0          0 SH     FW_PSC_S_MV_CHAN_BIX                  4          4 SH     FW_PSC_S_MV_PROMO_BIX                 4          4 SH     FW_PSC_S_MV_SUBCAT_BIX               21         21 SH     FW_PSC_S_MV_WD_BIX                  210        210 SH     PRODUCTS_PK                          72         72 SH     PRODUCTS_PROD_CAT_IX                  5         72 SH     PRODUCTS_PROD_STATUS_BIX              1          1 SH     PRODUCTS_PROD_SUBCAT_IX              21         72 SH     PROMO_PK                            503        503 SH     SALES_CHANNEL_BIX                     4         92 SH     SALES_CUST_BIX                     7059      35808 SH     SALES_PROD_BIX                       72       1074 SH     SALES_PROMO_BIX                       4         54 SH     SALES_TIME_BIX                     1460       1460 SH     SUP_TEXT_IDX SH     TIMES_PK                           1826       1826  72 rows selected.  SQL> select username from dba_users;  USERNAME ------------------------------------------------------------------------------------------ SYS SYSTEM XS$NULL LBACSYS OUTLN DBSNMP APPQOSSYS DBSFWUSER GGSYS ANONYMOUS CTXSYS SI_INFORMTN_SCHEMA DVSYS DVF GSMADMIN_INTERNAL ORDPLUGINS MDSYS OLAPSYS ORDDATA XDB WMSYS ORDSYS GSMCATUSER MDDATA SYSBACKUP REMOTE_SCHEDULER_AGENT PDBADMIN GSMUSER SYSRAC HR BI OJVMSYS AUDSYS DIP JY OE PM SYSKM ORACLE_OCM SYS$UMF QS_ADM IX SYSDG SPATIAL_CSW_ADMIN_USR SH  45 rows selected.