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

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

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;

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~[@more@]