手工创建Oracle数据库脚本及说明

1、Win dows 2000 Server，机器内存 128MOracle & R2 (8.1.6) for NT企业版D:ORACLE手工创建数据库的全部脚本及说明 系统环境：1、操作系统：2、数据库：3、安装路径：建库步骤：1、手工创建相关目录D:OracleadmintestD:OracleadmintestadhocD:Oracleadmintestbdu mpD:Oracleadmintestcdu mpD:OracleadmintestcreateD:OracleoradatatestD:OracleoradatatestarchiveD:Oracleadmintestex

2、pD:OracleadmintestpfileD:Oracleadmintestudu mp2、手工创建初始化启动参数文件:D:Oracleadm intestp filei nittest.ora，内容:db name = "test" instance name = test service names = test db files = 1024control_files = ("D:Oracleoradatatestcont 01.ctl","D:Oracleoradatatestcontrol02.ctl", "D

3、:Oracleoradatatestcontrol03.ctl")op en_cursors = 200max enabled roles = 30db file multiblock read count =8 db block buffers = 4096shared_ poo l_size = 52428800large_ pool_size = 78643200java_ pool_size = 20971520log_check point_interval = 10000log_check poin t_timeout = 1800p rocesses = 115p ar

4、allel_max_servers = 5log_buffer = 32768max_du mp_fil e_size = 10240global_names = trueoracle trace collection name =""background_dump_dest = D:Oracleadmintestbdu mpuser_dump_dest = D:Oracleadmintestudu mpdb block size = 16384remote_login_ passwordfile = exclusiveos_authent_ prefix ="&

5、quot;job_queue_ processes = 4job_queue_interval = 60op en_links = 4distributed transactions =10mts_dis patchers = "(P ROTOCOL=TC P)( PRE=oracle.aurora.server.SGio pServer)"mts_dis patchers = "(protocol=TC P)"comp atible = 8.1.0sort area size = 65536sort area retained size = 65536

6、# log_archive_start = true# log_archive_dest_1 = "location=D:Oracleoradataoradbarchive"# log_archive_format=%ORACLE SID%T%TS%S.ARC3、手工创建 D:OracleOra81DATABASEinittest.ora内容：IFILE= ' D:Oracleadmintestpfile'inittest.ora4、使用 orapwd.exe 命令，创建 D:OracleOra81DATABASEPWDtest.ora命令：D:Oracle

7、Ora81bi nora pwd file=D:OracleOra81DATABASEPWDtest.ora p assword=ORACLE en tries=55、通过oradim.exe命令，在服务里生成一个新的实例管理服务，启动方式为手工文件,set ORACLE_SID=testD:OracleOra81b inoradim -new -sid test -startmode manual -pfile "D:Oracleadmi ntestp filei nittest.ora"6、生成各种数据库对象D: >svrmgrl-创建数据库connect INT

8、ERNAL/oraclestart up nomount p file="D:Oracleadmintestpfile'inittest.ora"CREATE DATABASE testLOGFILE ' D:Oracleoradatatestredo01.log'SIZE 2048K,'D:Oracleoradatatestredo02.log'SIZE 2048K,'D:Oracleoradatatestredo03.log'SIZE 2048KMAXLOGFILES 32MAXLOGMEMBERS 2MAXLOG

9、HISTORY 1DATAFILE ' D:Oracleoradatatestsystem01.dbfSIZE 58M REUSE AUTOEXTEND ON NEXT 640KMAXDATAFILES 254MAXINSTANCES 1 CHARACTER SET ZHS16GBK NATIONAL CHARACTER SET ZHS16GBK;控制文件、日志文件在上面语句执行时生成conn ect INTERNAL/oracle-修改系统表空间ALTER TABLES PACE SYSTEM DEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXT

10、ENTS 1 MAXEXTENTS UNLIMITED P CTINCREASE 50);ALTER TABLES PACE SYSTEM MINIMUM EXTENT 64K;-创建回滚表空间CREATE TABLES PACE RBS DATAFILE' D:Oracleoradatatestrbs01.dbf'SIZE 256M REUSEAUTOEXTEND ON NEXT 5120K MINIMUM EXTENT 512K DEFAULT STORAGE ( INITIAL512K NEXT 512K MINEXTENTS 8 MAXEXTENTS 4096);CRE

11、ATE TABLES PACE USERS DATAFILE ' D:Oracleoradatatestusers01.dbf-创建用户表空间SIZE 128M REUSE AUTOEXTEND ON NEXT 1280K MINIMUM EXTENT 128K DEFAULT STORAGE ( INITIAL128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 P CTINCREASE 0);-创建临时表空间'SIZE 32M REUSECREATE TABLES PACE TEMP DATAFILE ' D:Oracleorada

12、tatestte mp 01.dbfAUTOEXTEND ON NEXT640KMINIMUM EXTENT 64KDEFAULT STORAGE ( INITIAL 64K NEXT 64K MINEXTENTS 1 MAXEXTENTSUNLIMITED P CTINCREASE 0) TEMP ORARY;-创建工具表空间CREATE TABLES PACE TOOLS DATAFILE' D:Oracleoradatatesttools01.dbf'SIZE 64M REUSE AUTOEXTEND ON NEXT 320K MINIMUM EXTENT 32KDEFA

13、ULT STORAGE ( INITIAL32K NEXT 32K MINEXTENTS 1 MAXEXTENTS 4096 P CTINCREASE 0);-创建索引表空间DEFAULT STORAGE ( INITIALCREATE TABLES PACE INDX DATAFILE' D:Oracleoradatatestindx01.dbf'SIZE 32M REUSE AUTOEXTEND ON NEXT 1280K MINIMUM EXTENT 128K128K NEXT 128K MINEXTENTS 1 MAXEXTENTS 4096 P CTINCREASE

14、0);-创建回滚段CREATE P UBLIC ROLLBACK SEGMENT RBS0 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS1 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS2 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS3 TABLES PACE

15、RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS4 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS5 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS6 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEG

16、MENT RBS7 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS8 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS9 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS10 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREA

17、TE P UBLIC ROLLBACK SEGMENT RBS11 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS12 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS13 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS14 TABLES PACE RBS STORAG

18、E ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS15 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS16 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS17 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RB

19、S18 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS19 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS20 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS21 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P

20、 UBLIC ROLLBACK SEGMENT RBS22 TABLES PACE RBS STORAGE ( OP TIMAL 4096K ); CREATE P UBLIC ROLLBACK SEGMENT RBS23 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );CREATE P UBLIC ROLLBACK SEGMENT RBS24 TABLES PACE RBS STORAGE ( OP TIMAL 4096K );-使回滚段在线ONLINE; ALTERALTER ROLLBACK SEGMENT "RBS0" ONLI

21、NE; ALTER ROLLBACK SEGMENT "RBS1" ONLINE;ALTER ROLLBACK SEGMENT "RBS2" ONLINE; ALTER ROLLBACK SEGMENT "RBS3"ROLLBACK SEGMENT "RBS4"ONLINE;ALTER ROLLBACK SEGMENT "RBS5" ONLINE; ALTER ROLLBACK SEGMENT "RBS6" ONLINE; ALTER ROLLBACK SEGMENT&quo

22、t;RBS7" ONLINE;ALTER ROLLBACKSEGMENT "RBS8" ONLINE;ALTER ROLLBACK ,ALTER ROLLBACK SEGMENT "RBS9" ONLINE;ALTER ROLLBACK SEGMENT "RBS10" ONLINE;ALTER ROLLBACK SEGMENT "RBS11" ONLINE;SEGMENT "RBS12"ONLINE;ALTER ROLLBACK SEGMENT "RBS13"ONL

23、INE;ALTER ROLLBACK SEGMENT "RBS14"ONLINE;ALTER ROLLBACK SEGMENT "RBS15"ONLINE;ALTER ROLLBACKSEGMENT "RBS16"ONLINE;ALTER ROLLBACK SEGMENT "RBS17"ONLINE;ALTER ROLLBACK SEGMENT "RBS18"ONLINE;ALTER ROLLBACK SEGMENT "RBS19"ONLINE;ALTER ROLLBACKSEGMENT "RBS20"ONLINE;ALTER ROLLBACK SEGMENT "RBS21"ONLINE;ALTER ROLLBACK SEGMENT "RBS22&q