Oracle 常用管理命令大全：日誌、表空間、表、索引、約束、備份恢復、用戶權限、角色稽核速查手冊

第 1 章：Redo Log 管理

-- 強制切換日誌
ALTER SYSTEM SWITCH LOGFILE;
-- 強制檢查點
ALTER SYSTEM CHECKPOINT;
-- 新增日誌組
ALTER DATABASE ADD LOGFILE GROUP 4 ('/DISK3/LOG4A.RDO','/DISK4/LOG4B.RDO') SIZE 100M;
-- 新增日誌成員
ALTER DATABASE ADD LOGFILE MEMBER '/DISK3/LOG1B.RDO' TO GROUP 1;
-- 重命名日誌檔
ALTER DATABASE RENAME FILE '/OLD/REDO01.LOG' TO '/NEW/REDO01.LOG';
-- 刪除日誌組/成員
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE MEMBER '/OLD/REDO01.LOG';
-- 清除未歸檔日誌
ALTER DATABASE CLEAR UNARCHIVED LOGFILE '/PATH/REDO01.LOG';

第 2 章：表空間管理

-- 建立永久表空間
CREATE TABLESPACE USER_DATA
  DATAFILE '/U01/ORADATA/USER_DATA01.DBF' SIZE 500M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

-- 建立臨時表空間
CREATE TEMPORARY TABLESPACE TEMP
  TEMPFILE '/U01/ORADATA/TEMP01.DBF' SIZE 500M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10M;

-- 離線/上線
ALTER TABLESPACE APP_DATA OFFLINE;
ALTER TABLESPACE APP_DATA ONLINE;

-- 唯讀/讀寫
ALTER TABLESPACE APP_DATA READ ONLY;

-- 自動擴展
ALTER TABLESPACE APP_DATA ADD DATAFILE '/U01/ORADATA/APP_DATA02.DBF'
  SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 500M;

-- 手動調整大小
ALTER DATABASE DATAFILE '/U01/ORADATA/APP_DATA01.DBF' RESIZE 300M;

-- 移動資料檔 (資料庫關閉)
ALTER DATABASE RENAME FILE '/OLD/APP_DATA01.DBF' TO '/NEW/APP_DATA01.DBF';

-- 刪除表空間
DROP TABLESPACE APP_DATA INCLUDING CONTENTS AND DATAFILES;

第 3 章：表操作

-- 建表含儲存參數
CREATE TABLE T (
  ID NUMBER PRIMARY KEY,
  DATA VARCHAR2(100)
) TABLESPACE USER_DATA
PCTFREE 20 PCTUSED 60
STORAGE (INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED);

-- 依查詢建表 (CTAS)
CREATE TABLE T_COPY AS SELECT * FROM T;

-- 全域暫存表
CREATE GLOBAL TEMPORARY TABLE T_TEMP ON COMMIT PRESERVE ROWS AS SELECT * FROM T;

-- 移動表空間 (重組、降低 HWM)
ALTER TABLE T MOVE TABLESPACE USER_DATA;

-- 釋放未用空間
ALTER TABLE T DEALLOCATE UNUSED KEEP 10M;

-- 截斷/刪除
TRUNCATE TABLE T;
DROP TABLE T CASCADE CONSTRAINTS;

-- 刪除/標記欄位
ALTER TABLE T DROP COLUMN COL1 CASCADE CONSTRAINTS;
ALTER TABLE T SET UNUSED COLUMN COL1;
ALTER TABLE T DROP UNUSED COLUMNS;

第 4 章：索引操作

-- B-tree 索引
CREATE INDEX IDX_T_DATA ON T(DATA) TABLESPACE INDX PCTFREE 20;

-- 反向鍵索引 (緩解右邊增長熱塊)
CREATE UNIQUE INDEX IDX_T_ID ON T(ID) REVERSE PCTFREE 20;

-- 位圖索引 (低基數、DW 環境)
CREATE BITMAP INDEX IDX_T_TYPE ON T(TYPE) PCTFREE 20;

-- 函數索引
CREATE INDEX IDX_T_UPPER ON T(UPPER(DATA));

-- 調整儲存參數
ALTER INDEX IDX_T_DATA STORAGE (NEXT 2M MAXEXTENTS UNLIMITED);

-- 重建/整理
ALTER INDEX IDX_T_DATA REBUILD ONLINE NOLOGGING PARALLEL 4;
ALTER INDEX IDX_T_DATA COALESCE;

第 5 章：約束管理

-- 建表時定義可延遲約束
CREATE TABLE T (
  ID NUMBER CONSTRAINT PK_T PRIMARY KEY DEFERRABLE INITIALLY DEFERRED
    USING INDEX TABLESPACE INDX
);

-- 會話層級設定
ALTER SESSION SET CONSTRAINTS = DEFERRED;
SET CONSTRAINTS ALL IMMEDIATE;

-- 啟用/停用
ALTER TABLE T ENABLE NOVALIDATE CONSTRAINT PK_T;  -- 不驗證現有資料
ALTER TABLE T ENABLE VALIDATE CONSTRAINT PK_T;    -- 驗證現有資料
ALTER TABLE T DISABLE CONSTRAINT PK_T;

第 6 章：資料載入

-- 直接路徑插入 (最小 REDO)
INSERT /*+ APPEND */ INTO T SELECT * FROM T_OLD;

-- 並行直接路徑
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(T,4) APPEND */ INTO T SELECT * FROM T_OLD;

-- SQL*Loader 直接路徑
SQLLDR USER/PASS CONTROL=LOAD.CTL DIRECT=TRUE

第 7 章：重組與遷移

-- EXP/IMP 邏輯備份
EXP USER/PASS TABLES=T FILE=T.DMP LOG=EXP.LOG COMPRESS=N DIRECT=Y
IMP USER/PASS TABLES=T FILE=T.DMP LOG=IMP.LOG IGNORE=Y

-- 表空間傳輸 (TTS)
ALTER TABLESPACE SALES_TS READ ONLY;
EXP USER/PASS FILE=SALES.DMP TRANSPORT_TABLESPACE=Y TABLESPACES=SALES_TS
-- 複製資料檔至目標
IMP USER/PASS FILE=SALES.DMP TRANSPORT_TABLESPACE=Y DATAFILES=('/NEW/SALES01.DBF')
ALTER TABLESPACE SALES_TS READ WRITE;

第 8 章：密碼與資源 Profile

CREATE PROFILE SEC_PROF LIMIT
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LIFE_TIME 60
  PASSWORD_GRACE_TIME 10
  PASSWORD_VERIFY_FUNCTION VERIFY_FN;

CREATE PROFILE DEV_PROF LIMIT
  SESSIONS_PER_USER 2
  CPU_PER_SESSION 10000
  IDLE_TIME 60
  CONNECT_TIME 480;

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

第 9-11 章：用戶、權限、角色

CREATE USER APP IDENTIFIED BY PWD
  DEFAULT TABLESPACE USER_DATA
  TEMPORARY TABLESPACE TEMP
  QUOTA 100M ON USER_DATA
  PROFILE SEC_PROF;

GRANT CREATE SESSION, CREATE TABLE TO APP;
GRANT SELECT ON SCOTT.EMP TO APP WITH GRANT OPTION;

CREATE ROLE APP_READ;
GRANT SELECT ON SCOTT.EMP TO APP_READ;
GRANT APP_READ TO APP;

ALTER USER APP DEFAULT ROLE ALL;

第 12 章：備份恢復核心語法

-- RMAN 完整備份
RUN {
  ALLOCATE CHANNEL C1 DEVICE TYPE DISK;
  BACKUP DATABASE PLUS ARCHIVELOG;
  RELEASE CHANNEL C1;
}

-- 完全恢復 (Mount 狀態)
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;

-- 表空間恢復 (Open 狀態)
RESTORE TABLESPACE USER_DATA;
RECOVER TABLESPACE USER_DATA;
ALTER TABLESPACE USER_DATA ONLINE;

-- 檔案級恢復
RESTORE DATAFILE 5;
RECOVER DATAFILE 5;

常用動態視圖速查