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