Sample Schemas建库后手动创建

最近在网上找到关于oracle10g sample_schema的部分脚本和数据。如下根据这些数据创建sample_schema过程的记录。
[oracle@dbserv1 db10g]$ cd demo
[oracle@dbserv1 demo]$ ls
schema
[oracle@dbserv1 demo]$ cd schema/
[oracle@dbserv1 schema]$ more README.txt
在readme.txt中有安装的步骤，但是安装过程并不那么顺利，按readme中Instructions to create the schemas，我们先得准备sys,system及其他所创建用户的密码：

1、确定用户名和密码
SYSTEM: oracle
SYS: oracle
HR: hr
OE: oe
PM: pm
IX: ix
SH: sh
BI bi

[@more@]

2、创建系统所需的表空间：

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/system01.dbf
/u01/app/oracle/oradata/PROD/undotbs01.dbf
/u01/app/oracle/oradata/PROD/sysaux01.dbf

SQL> create tablespace EXAMPLE datafile
2 '/u01/app/oracle/oradata/PROD/example01.dbf' size 500M ;

Tablespace created.

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/temp01.dbf

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMPTS1
EXAMPLE

SQL>

3、按readme.txt所述，执行如下脚本，具体目录路基按实际情况有所不同或需要创建：

SQL> @?/demo/schema/mksample oracle oracle hr oe pm ix sh bi EXAMPLE TEMPTS1 /u01/app/oracle/db10g/demo/schema/log_dir/

Sample Schemas are being created ...

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
*

................

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



第一次执行出现如下错误，即sh用户无法创建：

Connected.
SP2-0310: unable to open file "/u01/app/oracle/db10g/demo/schema/sales_history/sh_main.sql"
Connected.

没有创建sh用户，查看demo目录发现确实没有sales_history文件夹及相关脚本数据，因此单独找了sh用户脚本放到具体位置后重新执行脚本。
[oracle@dbserv1 schema]$ cp -rf sales_history /u01/app/oracle/db10g/demo/schema/
@?/demo/schema/mksample/sales_history/sh_main.sql oracle oracle sh EXAMPLE TEMPTS1 /u01/app/oracle/db10g/demo/schema/log_dir/

这次有了sh用户，但是数据：
SQL> conn sh/sh
Connected.
SQL> select count(*) from costs;

COUNT(*)
----------
0

4、最终独立执行了sh_main.sql脚本，具体如下：

[oracle@dbserv1 sales_history]$ sqlplus / as sysdba
SQL> @ ?/demo/schema/sales_history/sh_main.sql

specify password for SH as parameter 1:
Enter value for 1: sh

specify default tablespace for SH as parameter 2:
Enter value for 2: example

specify temporary tablespace for SH as parameter 3:
Enter value for 3: tempts1

specify password for SYS as parameter 4:
Enter value for 4: oracle

specify directory path for the data files as parameter 5:
Enter value for 5: /u01/app/oracle/db10g/demo/schema/sales_history/

writeable directory path for the log files as parameter 6:
Enter value for 6: /u01/app/oracle/db10g/demo/schema/log_dir/

specify version as parameter 7:
Enter value for 7: v3

Session altered.

User dropped.

old 1: CREATE USER sh IDENTIFIED BY &pass
new 1: CREATE USER sh IDENTIFIED BY sh

User created.

old 1: ALTER USER sh DEFAULT TABLESPACE &tbs
new 1: ALTER USER sh DEFAULT TABLESPACE example
old 2: QUOTA UNLIMITED ON &tbs
new 2: QUOTA UNLIMITED ON example

当然这中间也出现一些错误....

gathering statistics ...

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> show user
USER is "SH"
SQL> select count(*) from costs;

COUNT(*)
----------
82112

1 row selected.

估计salse_history这个sechema数据量较大原因网上分开放了该sechema数据。后来观察一下sales_history文件夹下sh_main.sql文件内容发现，同修改如下相应标粗行，也能达到一次性执行@?/demo/schema/mksample oracle oracle hr oe pm ix sh bi EXAMPLE TEMPTS1 /u01/app/oracle/db10g/demo/schema/log_dir/成功的目的：

REM PROMPT specify directory path for the data files as parameter 5:
REM DEFINE data_dir = &5
DEFINE data_dir = /u01/app/oracle/db10g/demo/schema/sales_history/PROMPT
PROMPT writeable directory path for the log files as parameter 6:
DEFINE log_dir = &6
PROMPT
REM PROMPT specify version as parameter 7:
REM DEFINE vrs = &7
DEFINE vrs = v3PROMPT

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18841027/viewspace-1060841/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18841027/viewspace-1060841/