数据库脚本

手工创建数据库的全部脚本及说明  
---摘自[Oracle技术网]  
系统环境：  
1、操作系统：Windows   2000   Server，机器内存128M
2、数据库：　Oracle   8i   R2   (8.1.6)   for   NT   企业版
3、安装路径：D:/ORACLE

建库步骤：  
1、手工创建相关目录
D:/Oracle/admin/test
D:/Oracle/admin/test/adhoc
D:/Oracle/admin/test/bdump
D:/Oracle/admin/test/cdump
D:/Oracle/admin/test/create
D:/Oracle/admin/test/exp
D:/Oracle/admin/test/pfile
D:/Oracle/admin/test/udump

D:/Oracle/oradata/test
D:/Oracle/oradata/test/archive

2、手工创建初始化启动参数文件：D:/Oracle/admin/test/pfile/inittest.ora，内容：

3、手工创建D:/Oracle/Ora81/DATABASE/inittest.ora文件，

    内容：IFILE= 'D:/Oracle/admin/test/pfile/inittest.ora '

4、使用orapwd.exe命令，创建D:/Oracle/Ora81/DATABASE/PWDtest.ora

    命令：D:/Oracle/Ora81/bin/orapwd   file=D:/Oracle/Ora81/DATABASE/PWDtest.ora   password=ORACLE   entries=5


5、通过oradim.exe命令，在服务里生成一个新的实例管理服务，启动方式为手工
    set   ORACLE_SID=test
    D:/Oracle/Ora81/bin/oradim   -new   -sid   test   -startmode   manual   -pfile   "D:/Oracle/admin/test/pfile/inittest.ora "

6、生成各种数据库对象
D:/> svrmgrl

--创建数据库
connect   INTERNAL/oracle
startup   nomount   pfile= "D:/Oracle/admin/test/pfile/inittest.ora "
CREATE   DATABASE   test
LOGFILE   'D:/Oracle/oradata/test/redo01.log '   SIZE   2048K,
        'D:/Oracle/oradata/test/redo02.log '   SIZE   2048K,
        'D:/Oracle/oradata/test/redo03.log '   SIZE   2048K
MAXLOGFILES   32
MAXLOGMEMBERS   2
MAXLOGHISTORY   1
DATAFILE   'D:/Oracle/oradata/test/system01.dbf '   SIZE   58M     REUSE   AUTOEXTEND   ON   NEXT   640K
MAXDATAFILES   254
MAXINSTANCES   1
CHARACTER   SET   ZHS16GBK
NATIONAL   CHARACTER   SET   ZHS16GBK;

控制文件、日志文件在上面语句执行时生成


connect   INTERNAL/oracle
--修改系统表空间
ALTER   TABLESPACE   SYSTEM   DEFAULT   STORAGE   (   INITIAL   64K   NEXT   64K   MINEXTENTS   1   MAXEXTENTS   UNLIMITED   PCTINCREASE   50);
ALTER   TABLESPACE   SYSTEM   MINIMUM   EXTENT   64K;

--创建回滚表空间
CREATE   TABLESPACE   RBS   DATAFILE   'D:/Oracle/oradata/test/rbs01.dbf '   SIZE   256M   REUSE
AUTOEXTEND   ON   NEXT   5120K
MINIMUM   EXTENT   512K
DEFAULT   STORAGE   (   INITIAL   512K   NEXT   512K   MINEXTENTS   8   MAXEXTENTS   4096);

--创建用户表空间
CREATE   TABLESPACE   USERS   DATAFILE   'D:/Oracle/oradata/test/users01.dbf '   SIZE   128M   REUSE
AUTOEXTEND   ON   NEXT   1280K
MINIMUM   EXTENT   128K
DEFAULT   STORAGE   (   INITIAL   128K   NEXT   128K   MINEXTENTS   1   MAXEXTENTS   4096   PCTINCREASE   0);

--创建临时表空间
CREATE   TABLESPACE   TEMP   DATAFILE   'D:/Oracle/oradata/test/temp01.dbf '   SIZE   32M   REUSE
AUTOEXTEND   ON   NEXT   640K
MINIMUM   EXTENT   64K
DEFAULT   STORAGE   (   INITIAL   64K   NEXT   64K   MINEXTENTS   1   MAXEXTENTS   UNLIMITED   PCTINCREASE   0)   TEMPORARY;

--创建工具表空间
CREATE   TABLESPACE   TOOLS   DATAFILE   'D:/Oracle/oradata/test/tools01.dbf '   SIZE   64M   REUSE
AUTOEXTEND   ON   NEXT   320K
MINIMUM   EXTENT   32K
DEFAULT   STORAGE   (   INITIAL   32K   NEXT   32K   MINEXTENTS   1   MAXEXTENTS   4096   PCTINCREASE   0);

--创建索引表空间
CREATE   TABLESPACE   INDX   DATAFILE   'D:/Oracle/oradata/test/indx01.dbf '   SIZE   32M   REUSE
AUTOEXTEND   ON   NEXT   1280K
MINIMUM   EXTENT   128K
DEFAULT   STORAGE   (   INITIAL   128K   NEXT   128K   MINEXTENTS   1   MAXEXTENTS   4096   PCTINCREASE   0);

--创建回滚段
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS0   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS1   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS2   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS3   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS4   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS5   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS6   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS7   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS8   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS9   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS10   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS11   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS12   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS13   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS14   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS15   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS16   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS17   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS18   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS19   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS20   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS21   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS22   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS23   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );
CREATE   PUBLIC   ROLLBACK   SEGMENT   RBS24   TABLESPACE   RBS   STORAGE   (   OPTIMAL   4096K   );

--使回滚段在线
ALTER   ROLLBACK   SEGMENT   "RBS0 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS1 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS2 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS3 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS4 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS5 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS6 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS7 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS8 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS9 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS10 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS11 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS12 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS13 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS14 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS15 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS16 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS17 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS18 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS19 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS20 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS21 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS22 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS23 "   ONLINE;
ALTER   ROLLBACK   SEGMENT   "RBS24 "   ONLINE;

--修改sys用户的临时表空间为TEMP
alter   user   sys   temporary   tablespace   TEMP;

--创建数据字典表
@D:/Oracle/Ora81/Rdbms/admin/catalog.sql;
@D:/Oracle/Ora81/Rdbms/admin/catexp7.sql
@D:/Oracle/Ora81/Rdbms/admin/catproc.sql
@D:/Oracle/Ora81/Rdbms/admin/caths.sql

connect   system/manager
@D:/Oracle/Ora81/sqlplus/admin/pupbld.sql

connect   internal/oracle
@D:/Oracle/Ora81/Rdbms/admin/catrep.sql
exit

--生成SQL*Plus帮助系统
sqlplus   SYSTEM/manager
@D:/Oracle/Ora81/sqlplus/admin/help/helpbld.sql   helpus.sql
exit

--修改system用户默认表空间和临时表空间
svrmgrl
connect   internal/oracle
alter   user   system   default   tablespace   TOOLS;
alter   user   system   temporary   tablespace   TEMP;
exit

7、将test实例启动服务设置成自动启动方式
D:/Oracle/Ora81/bin/oradim   -edit   -sid   test   -startmode   auto