CREATE DATABASE "mydb" MAXINSTANCES 8 MAXLOGHISTORY 1 MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE '/u02/oradata/mydb/system01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE '/u02/oradata/mydb/sysaux01.dbf' SIZE 512M REUSE AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/mydb/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u02/oradata/mydb/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 ('/u03/oradata/mydb/redo01.log') SIZE 2048M, GROUP 2 ('/u03/oradata/mydb/redo02.log') SIZE 2048M, GROUP 3 ('/u03/oradata/mydb/redo03.log') SIZE 2048M USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword";
Note 1:
You can exchange "SMALLFILE" with "BIGFILE" for any tablespace, as long as they are locally managed with automatic segment space management.
There are three exceptions to this rule: locally managed undo tablespaces, temporary tablespaces, and the SYSTEM tablespace does not have to use automatic segment space management; they *must* use manual segment space management but can still be created as BIGFILE. In one of my databases, I have the following setup:
SELECT tablespace_name,bigfile,extent_management,segment_space_management FROM dba_tablespaces; TABLESPACE_NAME BIGFILE EXTENT_MANAGEMENT SEGMENT_SPACE_MANA ---------------------------------------- --------- ------------------------------ ------------------ SYSTEM NO LOCAL MANUAL SYSAUX NO LOCAL AUTO UNDOTBS1 YES LOCAL MANUAL TEMP YES LOCAL MANUAL USERS NO LOCAL AUTO
Note 2:
The NATIONAL CHARACTER SET clause specifies the "national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2. Valid values are AL16UTF16 and UTF8. The default is AL16UTF16."
No comments:
Post a Comment