Oracle 中手工创建数据库的语法

Oracle — 作者 seafolk @ 15:59

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oracle 8i：

(还不支持 9i 的 AUM 特性，需要 DBA 手工管理 Undo 表空间大小和回滚段数量)

CREATE DATABASE "test"
maxdatafiles 254
maxinstances 8
maxlogfiles 32
character set US7ASCII
national character set UTF8
DATAFILE '/opt/oracle/oradata/test/system01.dbf' SIZE 260M AUTOEXTEND ON NEXT 10240K
logfile 
'/opt/oracle/oradata/test/redo01.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo02.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo03.log' SIZE 4M;

然后再创建 Undo, Temp 表空间，Rollback Segments.....运行创建数据字典脚本 catalog.sql, catproc.sql .....

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
...............
@?/rdbms/admin/utlrp.sql

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oracle 9i：

(引入 AUM 特性，相应的初始化参数是 undo_management， undo_retention 和 undo_tablespace；

保留手工管理 undo 支持；支持表空间本地管理特性(Extent Management Local)，9.2 支持 SYSTEM 表空间)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

Oracle 10g:

(引入 SYSAUX 表空间特性，在手工创建数据库的时候必须包含创建 SYSAUX 表空间的语句)

当禁用 AUM 时，10g 中依然是默认设置

undo_management='MANUAL'

create database test
maxdatafiles 1024
maxlogfiles 9
maxlogmembers 3
maxloghistory 1
maxinstances 1
character set al32utf8
national character set al16utf16
datafile '/opt/oracle/oradata/test/system01.dbf' size 50M autoextend on 
sysaux datafile '/opt/oracle/oradata/test/sysaux01.dbf' size 300M 
logfile 
'/opt/oracle/oradata/test/redo01.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo02.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo03.log' SIZE 4M;

创建 sysaux 表空间的语句必须存在， 否则会报 (ORA-13504: No SYSAUX datafile clause specified) 错误；

如果脚本中包含创建 undo 表空间的命令，则会报 (ORA-30014: operation only supported in Automatic Undo Management mode) 错误。

上面这段脚本等效于前面 8i 中的命令，再简洁不过了。

当禁用 AUM，并且system 表空间使用本地管理特性时 (extent management local)

undo_management='MANUAL'

create database test
maxdatafiles 1024
maxlogfiles 9
maxlogmembers 3
maxloghistory 1
maxinstances 1
character set al32utf8
national character set al16utf16
datafile '/opt/oracle/oradata/test/system01.dbf' size 50M autoextend on extent management local
sysaux datafile '/opt/oracle/oradata/test/sysaux01.dbf' size 300M
default temporary tablespace temp
tempfile '/opt/oracle/oradata/test/temp01.dbf' size 20M 
logfile 
'/opt/oracle/oradata/test/redo01.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo02.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo03.log' SIZE 4M;

创建默认临时表空间的语句必须存在，否则会报 (ORA-12900: must specify a default temporary tablespace for a locally managed
database) 错误，这是 10g 中要求的。

 

当启用 AUM 时

undo_management='AUTO'
undo_tablespace='UNDOTBS'


create database test
maxdatafiles 1024
maxlogfiles 9
maxlogmembers 3
maxloghistory 1
maxinstances 1
character set al32utf8
national character set al16utf16
datafile '/opt/oracle/oradata/test/system01.dbf' size 50M AUTOEXTEND ON
sysaux datafile '/opt/oracle/oradata/test/sysaux01.dbf' size 300M
undo tablespace undotbs 
datafile '/opt/oracle/oradata/test/undotbs01.dbf' size 10M autoextend on maxsize unlimited
logfile 
'/opt/oracle/oradata/test/redo01.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo02.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo03.log' SIZE 4M;

 

如果脚本中不包含创建 undo 表空间的命令 (undo tablespace xxx......)，则会报 (ORA-30045: No undo tablespace name specified) 错误；

如果参数和脚本中的表空间名称不一致则会报常见的 (ORA-01092: ORACLE instance terminated. Disconnection forced) 错误。

当启用 AUM 时，并且 system 表空间使用本地管理特性时 (extent management local)

undo_management='AUTO'
undo_tablespace='UNDOTBS'

create database test
maxdatafiles 1024
maxlogfiles 9
maxlogmembers 3
maxloghistory 1
maxinstances 1
character set al32utf8
national character set al16utf16
datafile '/opt/oracle/oradata/test/system01.dbf' size 50M autoextend on extent management local
sysaux datafile '/opt/oracle/oradata/test/sysaux01.dbf' size 300M
default temporary tablespace temp
tempfile '/opt/oracle/oradata/test/temp01.dbf' size 20M 
undo tablespace undotbs 
datafile '/opt/oracle/oradata/test/undotbs01.dbf' size 10M autoextend on maxsize unlimited
logfile 
'/opt/oracle/oradata/test/redo01.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo02.log' SIZE 4M, 
'/opt/oracle/oradata/test/redo03.log' SIZE 4M;

四个必要的表空间一个不能少！

当启用 OMF 时，AUM 必须启用

db_create_file_dest='/opt/oracle/oradata'
db_create_online_log_dest_1='/opt/oracle/oradata'
undo_management='AUTO'

create database test
user sys identified by oracle
user system identified by oracle
undo tablespace undotbs
default temporary tablespace temp;

我们可以使它更简洁如下:

create database test;

此时临时表空间需要手工创建如下：

create temporary tablespace temp;

 

 

当启用 OMF & ASM 时，AUM 必须启用

db_create_file_dest='+DATA1'
db_create_online_log_dest_1='+DATA1'
undo_management='AUTO'

create database test;

 

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~