Friday, October 17, 2014

Script example: create database

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