Oracle 中手工建立資料庫的語法

前幾天在網上看到了此文章，對建庫指令碼有了另一種收穫，覺得很好。為了連線的丟失，決定在此轉載。源文件 <http://blog.itpub.net/category/42901/67182>

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

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

[@more@]

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;

源文件 <http://blog.itpub.net/category/42901/67182>