（实践应用）Oracle 11g R2 测试schema生成

来源：互联网发布：js获取身份证号码编辑：程序博客网时间：2024/06/10 07:52

Oracle测试schema包含HR、SH等用户，在Oracle 11g R2下可以按照如下方式生成
1. 检查字符集，若不是US7ASCII，则要修改字符集，否则会在mkplug.sql restore的时候报错¹:

ERROR at line 1:
ORA-19583: conversation terminated due to error
ORA-19870: error while restoring backup piece
/opt/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example01.dfb
ORA-19615: some files not found in backup set
ORA-19613: datafile 6 not found in backup set
ORA-06512: at “SYS.DBMS_BACKUP_RESTORE”, line 5824
ORA-06512: at line 33

--检查字符集SQL>select userenv('language') from dual;

若不是US7ASCII则修改字符集²

SQL>SHUTDOWN IMMEDIATESQL>STARTUP MOUNT;SQL>ALTER SYSTEM ENABLE RESTRICTED SESSION;SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;SQL>ALTER DATABASE OPEN;SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE US7ASCII;SQL>SHUTDOWN IMMEDIATE;SQL>STARTUP

2.运行mkplug.sql脚本，前7个参数是password，后4-5个参数(参数数量应该和具体版本有关），设置如下³

cd /u01/app/oracle/product/11.2.0/db_1/demo/schemaSQL>@?/demo/schema/mkplug.sql·····specify INPUT metadata import file as parameter 8:       Enter value for 8: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example.dmpspecify INPUT database backup file for tablespace EXAMPLE as parameter 9:        Enter value for 9: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/example01.dfbspecify OUTPUT database file for tablespace EXAMPLE as parameter 10:        Enter value for 10: /u01/app/oracle/oradata/skyHost/example01.dbfspecify OUTPUT log directory as parameter 11:        Enter value for 11: /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/specify OUTPUT dump file directory as parameter 12:        Enter value for 12: /u01/app/oracle/product/11.2.0/db_1/assistants/dbca/templates/······mkplug.sql DONETO_CHAR(SYSTIMEST-----------------20171127 03:45:07

参考资料

http://www.dadbm.com/how-to-create-sample-schemas-in-oracle-11g-database-ora-19613/
https://www.cnblogs.com/rootq/articles/2049324.html
https://community.oracle.com/thread/3883205?start=15&tstart=0

阅读全文

0 0