Windows Xp上手工创建数据库Oracle9i的完整步骤

系统环境

操作系统：Windows Xp

数据库：Oracle 9i

安装路径：C:/Oracle

1、手工创建相关目录

       C：/Oracle/admin/mydb

       C：/Oracle/admin/mydb/bdump

       C：/Oracle/admin/mydb/udump

       C：/Oracle/admin/mydb/pfile

       C：/Oracle/admin/mydb/cdump

       C：/Oracle/admin/mydb/create

       D：/Oracle/oradata/mydb

       D：/Oracle/oradata/mydb/archive

2、手工创建初始化参数文件c:/oracle/admin/mydb/pfile/init.ora，内容可以copy别的实例init.ora文件后修改。

       db_block_size=4096

       db_cache_size=20971520

       background_dump_dest=C:/oracle/admin/mydb/bdump

       core_dump_dest=C:/oracle/admin/mydb/bdump

       timed_statistics=TRUE

       user_dump_dest=C:/oracle/admin/mydb/udump

       control_files=("D:/oracle/oradata/mydb/CONTROL01.CTL",  

                      "D:/oracle/oradata/mydb/CONTROL02.CTL", 

                      "D:/oracle/oradata/mydb/CONTROL03.CTL")

       log_archive_dest_1='LOCATION=d:/oracle/admin/mydb/archive'

       log_archive_format=%t_s%.dbf

       log_archive_start=true

       compatible=9.2.0.0.0

       db_name=mydb

     db_domain=" "

     remote_login_passwordfile=EXCLUSIVE

       instance_name=mydb

       java_pool_size=31457280

       large_pool_size=1048576

       shared_pool_size=52428800

       processes=150

       fast_start_mttr_target=300

       resource_manager_plan=system_plan

       sort_area_size=524288

       undo_management=AUTO

       undo_tablespace=undotbs

3、手工创建initmydb.ora文件，内容：ifile= c:/oracle/admin/mydb/pfile/init.ora

4、使用orapwd.exe命令，创建口令文件pwdmydb.ora,命令格式如下：

orapwd.exe  file=c:/oracle/ora92/database/pwdmydb.ora  password=mydbDBA  entries=5

5、通过oradim.exe命令，在服务里生成一个新的实例管理服务，启动方式为手工

C:/oracle/ora92/bin/oradim -new  -sid mydb -startmode m

-pfile C:/oracle/ora92/database/initmydb.ora

C:/oracle/ora92/bin/net start OracleServiceMYDB

6、声明ORACLE_SID值

set ORACLE_SID=mydb

7、连接到Oracle服务

 C:/Oracle/ora92/bin/sqlplus/nolog

SQL>connect SYS/caihuain as SYSDBA

SQL>connect SYS/caihuain as SYSDBA

8、启动数据库实例

SQL>STARTUP NOMOUNT

PFILE=”C:/oracle/admin/mydb/pfile/initmydb.ora”

9、创建数据库，执行mydb.sql脚本命令

SQL>@mydb.sql

mydb.sql脚本内容如下：

create database mydb

maxinstances 1

maxloghistory 1

maxlogfiles 5

maxlogmembers 5

maxdatafiles 100

DATAFILE 'e:/oracle/oradata/mydb/system01.dbf'

SIZE 325M REUSE

AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

EXTENT MANAGEMENT LOCAL

UNDO TABLESPACE UNDOTBS

DATAFILE 'e:/oracle/oradata/mydb/undotbs01.dbf'

SIZE 150M REUSE

AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

DEFAULT TEMPORARY TABLESPACE temps1

TEMPFILE 'E:/oracle/oradata/mydb/temptbs01.dbf'

SIZE 20M REUSE

EXTENT MANAGEMENT LOCAL

CHARACTER SET ZHS16GBK

NATIONAL CHARACTER SET AL16UTF16

LOGFILE GROUP 1 ('e:/oracle/oradata/mydb/redo01.log') SIZE 100M,

GROUP 2 ('e:/oracle/oradata/mydb/redo02.log') SIZE 100M,

GROUP 3 ('e:/oracle/oradata/mydb/redo03.log') SIZE 100M;

10、创建数据库数据文件，执行脚本createfiles.sql

SQL>@createfiles.sql

Createfiles.sql脚本内容如下：

             set echo on

             spool C:/oracle/admin/mydb/create/CreateDBFiles.log

      CREATE TABLE SPACE"INDX" LOGGING DATAFILE 'C:/oracle/oradata/mydb/indx01.dbf'

                            SIZE 25M REUSE

AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 

EXTENT MANAGEMENT LOCAL;

                     CREATE TEMPORARY TABLESPACE "TEMP" TEMPFILE 'C:/oracle/oradata/mydb/temp01.dbf' 

                            SIZE 40M REUSE

                            AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

            EXTENT MANAGEMENT LOCAL;

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";

CREATE TABLESPACE "TOOLS" LOGGING DATAFILE 'C:/oracle/oradata/mydb/tools01.dbf'

         SIZE 10M REUSE AUTOEXTEND ON NEXT  320K MAXSIZE UNLIMITED

         EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE "USERS" LOGGING DATAFILE 'C:/oracle/oradata/mydb/users01.dbf'

         SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED

         EXTENT MANAGEMENT LOCAL;

CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE 'C:/oracle/oradata/mydb/undotbs01.dbf'

         SIZE 200M REUSE AUTOEXTEND ON NEXT  5120K;

spool off

                  exit;

11、创建数据字典，运行createdbcatalog.sql脚本命令

SQL>@createdbcatalog.sql

Createdbcatalog.sql脚本内容如下：

@C:/oracle/ora92/rdbms/admin/catalog.sql;

@C:/oracle/ora92/rdbms/admin/catproc.sql;

12、创建用户

       CREATE USER username IDENTIFIED BY password

              DEFAULT TABLESPACE users

              QUOTA UNLIMITED ON users

              TEMPORARY TABLESPACE temp;

       SQL>GRANT CREATE SESSION,CREATE TABLE

                     To username;