Oracle 9i DBA 實戰筆記精華：從建庫、表空間、UNDO 到物件管理完整語法集

資料庫建立與啟動關機

CREATE DATABASE db01
  MAXLOGFILES 10 MAXDATAFILES 1024 MAXINSTANCES 2
  LOGFILE GROUP 1 ('/u01/oradata/db01/log_01.rdo') SIZE 15M,
          GROUP 2 ('/u01/oradata/db01/log_02.rdo') SIZE 15M,
          GROUP 3 ('/u01/oradata/db01/log_03.rdo') SIZE 15M
  DATAFILE '/u01/oradata/db01/system_01.dbf' SIZE 100M
  UNDO TABLESPACE UNDO DATAFILE '/u01/oradata/db01/undo_01.dbf' SIZE 40M
  DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/db01/temp_01.dbf' SIZE 20M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
  CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16;

-- 啟動階段
STARTUP NOMOUNT;  -- 僅 Instance
ALTER DATABASE MOUNT;  -- 掛載控制檔
ALTER DATABASE OPEN;   -- 開啟資料檔

核心資料字典查詢

• 使用者/權限：DBA_USERS, USER_TABLES, ALL_TABLES, DBA_TABLES, DBA_TAB_PRIVS
• 實例/記憶體：V$INSTANCE, V$SGA, V$PARAMETER
• 表空間/檔案：V$TABLESPACE, V$DATAFILE, V$TEMPFILE, DBA_DATA_FILES
• 控制檔/日誌：V$CONTROLFILE, V$LOG, V$LOGFILE, V$ARCHIVED_LOG
• UNDO/回滾：DBA_ROLLBACK_SEGS, V$UNDOSTAT, V$TRANSACTION
• 段/區塊/空間：DBA_SEGMENTS, DBA_EXTENTS, DBA_FREE_SPACE

控制檔與重做日誌管理

-- 多路徑控制檔
ALTER SYSTEM SET CONTROL_FILES='/path/ctrl01.ctl','/path/ctrl02.ctl' SCOPE=SPFILE;
-- 備份控制檔 (二進位/文字)
ALTER DATABASE BACKUP CONTROLFILE TO '/path/control.bak';
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- 日誌組管理
ALTER DATABASE ADD LOGFILE GROUP 3 ('/path/redo03.rdo') SIZE 50M;
ALTER DATABASE ADD LOGFILE MEMBER '/path/redo03b.rdo' TO GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3;  -- 清除未歸檔需加 UNARCHIVED

-- 歸檔模式切換 (需 MOUNT 狀態)
ALTER DATABASE ARCHIVELOG / NOARCHIVELOG;
ARCHIVE LOG START;  -- 自動歸檔

表空間全方位操作

-- 建立 (LOCAL 管理 + AUTOALLOCATE/UNIFORM)
CREATE TABLESPACE userdata DATAFILE '/path/userdata01.dbf' SIZE 100M
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

-- UNDO 表空間
CREATE UNDO TABLESPACE undo1 DATAFILE '/path/undo01.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL;

-- 暫存表空間
CREATE TEMPORARY TABLESPACE temp TEMPFILE '/path/temp01.dbf' SIZE 100M EXTENT MANAGEMENT LOCAL;

-- 修改/擴充/重命名/離線/唯讀/刪除
ALTER TABLESPACE userdata ADD DATAFILE '/path/userdata02.dbf' SIZE 50M;
ALTER DATABASE DATAFILE '/path/userdata01.dbf' RESIZE 200M;
ALTER DATABASE DATAFILE '/path/userdata01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
ALTER TABLESPACE userdata RENAME DATAFILE '/old/path.dbf' TO '/new/path.dbf';
ALTER TABLESPACE userdata OFFLINE NORMAL / ONLINE;
ALTER TABLESPACE userdata READ ONLY;
DROP TABLESPACE userdata INCLUDING CONTENTS AND DATAFILES;

-- OMF 簡化管理
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/u01/oradata';
CREATE TABLESPACE userdata;  -- 自動命名/管理

UNDO 管理與快照回溯

-- 參數
UNDO_MANAGEMENT=AUTO, UNDO_TABLESPACE=UNDO, UNDO_RETENTION=900

-- 空間估算公式
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)
  UR=UNDO_RETENTION(秒), UPS=每秒回滾區塊數, DBS=DB_BLOCK_SIZE

-- 手動模式 (舊版相容)
CREATE ROLLBACK SEGMENT rbs1 TABLESPACE rbs;
ALTER ROLLBACK SEGMENT rbs1 ONLINE;
-- init.ora: UNDO_MANAGEMENT=MANUAL, ROLLBACK_SEGMENTS=(rbs1,rbs2...)

-- 閃回查詢 (9i 新功能)
EXEC DBMS_FLASHBACK.ENABLE_AT_TIME('26-JAN-2004 12:17:00');
-- 查詢舊資料...
EXEC DBMS_FLASHBACK.DISABLE;

表與索引核心 DDL

-- 建表 (儲存參數/約束/表空間)
CREATE TABLE test (id NUMBER PRIMARY KEY, name VARCHAR2(20) NOT NULL)
  TABLESPACE userdata PCTFREE 20 PCTUSED 40 STORAGE (INITIAL 100K NEXT 100K);

-- 分區表移動分區
ALTER TABLE sales MOVE PARTITION p2023 TABLESPACE userdata;

-- 索引類型
CREATE INDEX idx_name ON tab(col) TABLESPACE userdata;           -- B-tree
CREATE BITMAP INDEX idx_bmp ON tab(col) TABLESPACE userdata;     -- 低基數
CREATE INDEX idx_func ON tab(UPPER(name)) TABLESPACE userdata;   -- 函數索引
CREATE INDEX idx_rev ON tab(col) REVERSE;                        -- 反轉索引

-- 索引維護
ALTER INDEX idx_name REBUILD [ONLINE] [TABLESPACE userdata] [REVERSE/NOREVERSE];
ALTER INDEX idx_name COALESCE;  -- 整理碎片
ANALYZE INDEX idx_name VALIDATE STRUCTURE;  -- 結構驗證/統計
-- 監控使用
ALTER INDEX idx_name MONITORING USAGE;
SELECT * FROM V$OBJECT_USAGE WHERE INDEX_NAME='IDX_NAME';

完整性約束管理

ALTER TABLE tab ADD CONSTRAINT pk_tab PRIMARY KEY (col1,col2);
ALTER TABLE tab ADD CONSTRAINT fk_tab FOREIGN KEY (col1) REFERENCES parent(col1);
ALTER TABLE tab ADD CONSTRAINT ck_tab CHECK (status IN ('A','I')) ENABLE VALIDATE;
-- 延遲驗證 (Commit 時檢查)
ALTER TABLE tab MODIFY CONSTRAINT ck_tab INITIALLY DEFERRED;
-- 停用/啟用/驗證/例外表
ALTER TABLE tab DISABLE CONSTRAINT ck_tab;
ALTER TABLE tab ENABLE NOVALIDATE CONSTRAINT ck_tab;
ALTER TABLE tab ADD CONSTRAINT ck_tab CHECK (col>15) EXCEPTIONS INTO exceptions;
@?/rdbms/admin/utlexcpt.sql  -- 建立 EXCEPTIONS 表

使用者/權限/角色/設定檔

CREATE USER app IDENTIFIED BY pwd
  DEFAULT TABLESPACE userdata TEMPORARY TABLESPACE temp QUOTA 100M ON userdata;

-- 系統/物件權限
GRANT CREATE SESSION, CREATE TABLE TO app;
GRANT SELECT, INSERT ON tab TO app WITH GRANT OPTION;
GRANT UPDATE(col1,col2) ON tab TO role;  -- 欄位級

-- 角色
CREATE ROLE app_read IDENTIFIED BY pwd;
GRANT SELECT ON tab TO app_read;
GRANT app_read TO app;
ALTER USER app DEFAULT ROLE app_read;

-- 密碼/資源設定檔
CREATE PROFILE sec_prof LIMIT
  FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 1/24
  PASSWORD_LIFE_TIME 30 PASSWORD_GRACE_TIME 5
  PASSWORD_REUSE_TIME 30 PASSWORD_REUSE_MAX 3
  SESSIONS_PER_USER 2 CPU_PER_SESSION 10000 IDLE_TIME 30 CONNECT_TIME 480;
ALTER USER app PROFILE sec_prof;
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

進階 SQL 技巧

• 合併查詢：UNION (去重排序) / UNION ALL (保留重複) / INTERSECT / MINUS
• JOIN 語法 (ANSI 99)：INNER JOIN, LEFT/RIGHT/FULL OUTER JOIN, CROSS JOIN, NATURAL JOIN, JOIN ... USING
• MERGE：單一陳述式完成 UPSERT
• TOP-N：SELECT * FROM (SELECT ... ORDER BY col DESC) WHERE ROWNUM <= N
• ROLLUP/CUBE/GROUPING SETS：多維小計/總計
• SEQUENCE/SYNONYM/DB LINK：自動編號/別名/遠端存取