Oracle 12CR2 Install the Sample Schemas

eric0435發表於2017-05-18

在Oracle 12CR2中在建立資料庫時不能安裝sample schemas,Oracle將sample schemas的安裝指令碼儲存在GitHub上了,可以透過以下連結地址進行下載


下載到的是一個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.

相關sample schemas建立成功。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26015009/viewspace-2139299/,如需轉載,請註明出處,否則將追究法律責任。

相關文章