oracle 学习总结篇一: 数据库的创建

在安装完oracle软件后,我们所做的第一件事情就是创建数据库,一般有两种方法来创建
1.用图形化工具dbca 全称即 Database Configure Assistant
2.用脚本
对于初学者来说,建议用第一种,图形化创建时有个良好的向导,可以由浅入深,下面就两种方法来介绍了
首先介绍第一种: dbca 图形化工具
在windows下 点开始 运行 输入dbca 命令, 就出现图形化界面了可以按照向导操作了,这个用的比较多
在unix/linux下也只需要启动xmanager ,然后输入dbca命令 也一样完成操作
在创建数据库时有四个选项,主要看哪个比较适合你
[table=98%]
[font=Arial, Helvetica, sans-serif]Environment[font=Arial, Helvetica, sans-serif]Description of Environment
DSS (Data Warehousing)Users perform. numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues. These queries (typically read-only) range from a simple fetch of a few records to numerous complex queries that sort thousands of records from many different tables.
OLTP (Online Transaction Processing)Many concurrent users performing numerous transactions requiring rapid access to data. Availability, speed, concurrence, and recoverability are key issues. Transactions consist of reading ([font=新宋体]SELECT statements), writing ([font=新宋体]INSERT and [font=新宋体]UPDATE statements), and deleting ([font=新宋体]DELETE statements) data in database tables.
General PurposeThis template creates a database designed for general purpose usage. It combines features of both the DSS and OLTP database templates.
New DatabaseThis template allows you maximum flexibility in defining a database.

第二种方法:手工写脚本 有助于理解数据库的创建过程 以windows为例子:
目录下有这些文件
2008-03-06 10:11 955 CreateDB.sql
2008-03-06 10:11 631 CreateDBCatalog.sql
2008-03-06 10:11 722 CreateDBFiles.sql
2008-03-06 10:11 814 dbca.bat
2008-03-06 10:11 2,689 init.ora
2008-03-06 10:11 414 postDBCreation.sql
打开 dbca.bat 文件内容 里面内容如下:
mkdir D:\oracle\admin\dbca\bdump
mkdir D:\oracle\admin\dbca\cdump
mkdir D:\oracle\admin\dbca\create
mkdir D:\oracle\admin\dbca\pfile
mkdir D:\oracle\admin\dbca\udump
mkdir D:\oracle\ora92\database
mkdir D:\oracle\oradata\dbca --红色部分创建相关存放trace日志文件的目录
[color=#00ff40]set ORACLE_SID=dbca [color=#00ff40]--绿色部分设置oracle 实例名为 为dbca
D:\oracle\ora92\bin\oradim.exe -new -sid DBCA -startmode m
D:\oracle\ora92\bin\oradim.exe -edit -sid DBCA -startmode a --蓝色部分用oracle自带的 oradim
创建一个实例名为dbca 并且有手动启动方式改为自动启动
[color=#ff00ff]D:\oracle\ora92\bin\orapwd.exe file=D:\oracle\ora92\database\PWDdbca.ora password=change_on_install
[color=#ff00ff]--粉红色部分为用oracle自带的orapwd 为sys用户创建一个默认的密码为 change_on_install
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\dbca\scripts\CreateDB.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\dbca\scripts\CreateDBFiles.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\dbca\scripts\CreateDBCatalog.sql
D:\oracle\ora92\bin\sqlplus /nolog @D:\oracle\admin\dbca\scripts\postDBCreation.sql
--黑体部分就是创建数据库要调用的脚本
第一 CreateDB.sql
connect SYS/change_on_install as SYSDBA --这是刚刚我们设置的密码以sysdba身份连接到数据库
set echo on
spool D:\oracle\ora92\assistants\dbca\logs\CreateDB.log 写日志
startup nomount pfile="D:\oracle\admin\dbca\scripts\init.ora"; 调用参数文件 启动数据库到只装载实例阶段
CREATE DATABASE dbca
MAXINSTANCES 1
MAXLOGHISTORY 1
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100 --控制文件记录的相关最大日志数,日志组,最大数据文件数等限制
DATAFILE 'D:\oracle\oradata\dbca\system01.dbf' SIZE 250M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\oracle\oradata\dbca\temp01.dbf' SIZE 40M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE 'D:\oracle\oradata\dbca\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED --创建系统,临时,回滚 表空间
[color=#00ff40]CHARACTER SET ZHS16GBK
NATIONAL CHARACTER SET AL16UTF16 --字符集
[color=#ff00ff]LOGFILE GROUP 1 ('D:\oracle\oradata\dbca\redo01.log') SIZE 102400K,
GROUP 2 ('D:\oracle\oradata\dbca\redo02.log') SIZE 102400K,
GROUP 3 ('D:\oracle\oradata\dbca\redo03.log') SIZE 102400K; --创建日志文件租和成员
spool off
exit;

第二.CreateDBFiles.sql
connect SYS/change_on_install as SYSDBA
set echo on
spool D:\oracle\ora92\assistants\dbca\logs\CreateDBFiles.log
CREATE TABLESPACE "INDX" LOGGING DATAFILE 'D:\oracle\oradata\dbca\indx01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE "TOOLS" LOGGING DATAFILE 'D:\oracle\oradata\dbca\tools01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;
CREATE TABLESPACE "USERS" LOGGING DATAFILE 'D:\oracle\oradata\dbca\users01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ; --创建相关用户要用到的表空间
spool off
exit;
第三:CreateDBCatalog.sql --创建system数据字典,存放到system表空间,相关表,视图等
connect SYS/change_on_install as SYSDBA
set echo on
spool D:\oracle\ora92\assistants\dbca\logs\CreateDBCatalog.log
@D:\oracle\ora92\rdbms\admin\catalog.sql;
@D:\oracle\ora92\rdbms\admin\catexp7.sql;
@D:\oracle\ora92\rdbms\admin\catblock.sql;
@D:\oracle\ora92\rdbms\admin\catproc.sql;
@D:\oracle\ora92\rdbms\admin\catoctk.sql;
@D:\oracle\ora92\rdbms\admin\owminst.plb;
connect SYSTEM/manager
@D:\oracle\ora92\sqlplus\admin\pupbld.sql;
connect SYSTEM/manager
set echo on
spool D:\oracle\ora92\assistants\dbca\logs\sqlPlusHelp.log
@D:\oracle\ora92\sqlplus\admin\help\hlpbld.sql helpus.sql;
spool off
spool off
exit;
第四: postDBCreation.sql
connect SYS/change_on_install as SYSDBA
set echo on
spool D:\oracle\ora92\assistants\dbca\logs\postDBCreation.log
@D:\oracle\ora92\rdbms\admin\utlrp.sql; --编译相关视图,包对象等
shutdown ; --关闭数据库
connect SYS/change_on_install as SYSDBA
set echo on
spool D:\oracle\ora92\assistants\dbca\logs\postDBCreation.log
create spfile='D:\oracle\ora92\database\spfiledbca.ora' FROM pfile='D:\oracle\admin\dbca\scripts\init.ora';
--创建服务器参数文件代替文件初始化参数文件,方便有时在不重启数据库的情况下可以使参数生效
从上面可以看到spfile文件的存放位置
startup ; --启动数据库 ,创建数据库完成
exit;

从上面可以看出,创建数据库有以下10个步骤
[color=#ff00ff]Step 1:[color=#ff00ff] 创建相关trace目录文件夹[color=#ff00ff]Step 2: [color=#ff00ff]创建实例,密码 启动方式[color=#ff00ff]Step 3: [color=#ff00ff]创建初始化参数文件 init.ora[color=#ff00ff]Step 4: 连接到实例[color=#ff00ff]Step 5: 启动实例到nomount状态[color=#ff00ff]Step 6: [color=#ff00ff]创建数据库[color=#ff00ff]Step 7: [color=#ff00ff]创建表空间[color=#ff00ff]Step 8: 运行脚本创建数据字典[color=#ff00ff]Step 9: 创建服务器参数文件(这步不是必须的,但oracle建议做这步) 好处会在以后的总结中列出下面也贴出linux下脚本,和windows下几乎差不多#!/bin/shmkdir /oradata/ora9i
mkdir /oradata/ora9i/controlfile
mkdir /oradata/ora9i/redofile
mkdir /orasys/oracle/admin
mkdir /orasys/oracle/admin/ora9i
mkdir /orasys/oracle/admin/ora9i/bdump
mkdir /orasys/oracle/admin/ora9i/cdump
mkdir /orasys/oracle/admin/ora9i/create
mkdir /orasys/oracle/admin/ora9i/udump
mkdir /orasys/oracle/admin/ora9i/pfile
cp init.ora /orasys/oracle/product/9.2.0/dbs/.export ORACLE_BASE=/orasys/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0
export ORACLE_SID=ora9i
export ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/lib
export LD_LIBRARY_PATH
export PATH=$PATH:$ORACLE_HOME/bin
echo Add this entry in the oratab: ora9i:/orasys/oracle/product/9.2.0:Y
/orasys/oracle/product/9.2.0/bin/orapwd file=/orasys/oracle/product/9.2.0/dbs/orapwora9i password=change_on_install
/orasys/oracle/product/9.2.0/bin/sqlplus /nolog @/home/oracle/create_script/CreateDB.sql
/orasys/oracle/product/9.2.0/bin/sqlplus /nolog @/home/oracle/create_script/CreateDBFiles.sql
/orasys/oracle/product/9.2.0/bin/sqlplus /nolog @/home/oracle/create_script/CreateDBCatalog.sql
/orasys/oracle/product/9.2.0/bin/sqlplus /nolog @/home/oracle/create_script/postDBCreation.sql