附錄C Oracle SQL的常用命令語法
在操作數據庫時，經常需要聯機為SYSDBA，其命令語法為CONNECT SYS/password AS SYSDBA，下麵列舉出Oracle SQL的常用命令語法，以供讀者參考。
C-1 建立數據庫的命令語法
CREATE DATABASE name
CONTROLFILE REUSE
LOGFILE GROUP n (filespec)
MAXLOGFILES n
MAXLOGMEMBERS n
MAXLOGHISTORY n
MAXDATAFILES n
MAXINSTANCES n
ARCHIVELOG|NOARCHIVELOG
CHARACTER_SET charset
NATIONAL_CHARACTER_SET charset
DATAFILE (filespec) autoextend_clause
Extent_managemnet_clause
Default_temp_tablespace_clasue
UNDO_tablespace_clause
SET STANDBY DATABASE clause
Set_time_zone_clause
autoextend_clause;
AUTOEXTEND ON|OFF NEXT n K|M MAXSIZE n|UNLIMITED K|M
Filespec:
'full path file name|logical|system link name' SIZE n K|M REUSE
extent_management_clause:
EXTENT MANAGEMENT
DIRECTORY
LOCAL
AUTOALLOCATE
UNIFORM
SIZE n [K|M]
Default_temp_tablespace_clause:
DEFAULT TEMPORARY TABLESPACE tablespace [TEMPFILE]
Temp_tablespace_extent_clause
UNDO_tablespace_cluae:
UNDO TABLESPACE tablespace [DATAFILE file_space_clauses]
範例：
CREATE DATABASE myhrdb
USER SYS IDENTIFIED BY hr6588
USER SYSTEM IDENTIFIED BY hr0001
LOGFILE GROUP 1 ('/u01/oracle/oradata/myhrdb/redo01.log') SIZE 100M,
GROUP 2 ('/u01/oracle/oradata/myhrdb/redo02.log') SIZE 100M,
GROUP 3 ('/u01/oracle/oradata/myhrdb/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/u01/oracle/oradata/myhrdb/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/oracle/oradata/myhrdb/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TABLESPACE tbs_1
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/myhrdb/temp01.dbf'
SIZE 20M REUSE
UNDO TABLESPACE undotbs
DATAFILE '/u01/oracle/oradata/myhrdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
C-2 啟動數據庫的命令語法
STARTUP [RESTRICTED] [FORCE] [PFILE=filename] [SPFILE=filename]
[EXCLUSIVE or PARALLEL]
[MOUNT or OPEN] dbname
[NOMOUNT]
[RECOVER]
C-3 關閉數據庫的命令語法
SHUTDOWN [NORMAL] [IMMEDIATE] [ABORT] [TRANSACTINAL]
C-4 刪除數據庫的命令語法
DROP DATABASE;
C-5 建立表格空間的命令語法
CREATE [UNDO|TEMPORARY] TABLESPACE tablespace
DATAFILE|TEMPFILE 'file_space'
[MINIMUM EXTENT n [K|M]]
[AUTOEXTEND
OFF
ON NEXT n K|M MAXSIZE UNLIMITED|n [K|M]]
[LOGGING|NOLOGGING]
[ONLINE|OFFLINE]
[DEFAULT (storage_clause)] (N/A If TEMPORARY above)
[PERMANENT|TEMPORARY] (N/A If TEMPORARY above)
[EXTENT MANAGEMENT
DICTIONARY (Must be LOCAL for TEMPFILE)
LOCAL
AUTOALLOCATE
UNIFORM [SIZE n [K|M]]
[SEGMENT SPACE MANAGEMENT MAUNAL|AUTO]
範例：
CREATE TABLESPACE users LOGGING
DATAFILE '/u01/oracle/oradata/myhrdb/users01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING
DATAFILE '/u01/oracle/oradata/myhrdb/indx01.dbf'
SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL;
C-6 更改表格空間的命令語法
ALTER TABLESPACE tablespace
[LOGGING|NOLOGGING]
[ADD DATAFILE|TEMPFILE file_space [autoextendclause]]
[RENAME DATAFILE 'from_file_space' TO 'to_file_spec']
[COALESCE]
[DEFAULT STORAGE storage_cluase]
[MINIMUM EXTENT n K|M]