• 博客访问: 5440064
  • 博文数量: 450
  • 用 户 组: 普通用户
  • 注册时间: 2011-10-12 14:43
  • 认证徽章:
个人简介

系统架构师,ACOUG MEMBER,Mail:yongjing.star@gmail.com Mobile:13875984558 QQ:409898894 Blog:www.jydba.net

ITPUB论坛APP

ITPUB论坛APP



APP发帖 享双倍积分

文章分类

全部博文(450)

文章存档

2017年(38)

2016年(69)

2015年(83)

2014年(41)

2013年(83)

2012年(121)

2011年(15)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

在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.

相关sample schemas创建成功。

阅读(845) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册