您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫相關知識

Oracle SQL 核心命令速查手冊：DML/DDL/DCL、索引、視圖、序列、分頁、ROWID 實戰語法大全

編輯：Oracle 資料庫相關知識

DML 核心：鎖機制與大表刪除

操作	語法	鎖類型	空間釋放	可回滾	適用場景
DELETE	DELETE FROM t WHERE ...	行級排它鎖	否（高水位不變）	是	小批量、需觸發器
TRUNCATE	TRUNCATE TABLE t	DDL 級排它鎖	是（重置高水位）	否	清空大表、重置自增
DROP	DROP TABLE t	DDL 級排它鎖	是（含結構）	否	永久刪除

大表分批刪除模板（避免 UNDO 爆滿、鎖升級）：


BEGIN
    LOOP
        DELETE FROM big_table WHERE create_dt < SYSDATE-365 AND ROWNUM <= 10000;
        EXIT WHEN SQL%ROWCOUNT = 0;
        COMMIT;
    END LOOP;
END;
/

DDL 表結構變更：約束、欄位、重命名


-- 約束
ALTER TABLE t ADD CONSTRAINT pk_t PRIMARY KEY (id);
ALTER TABLE t DROP PRIMARY KEY CASCADE;        -- 連帶刪除子表外鍵
ALTER TABLE t DISABLE CONSTRAINT pk_t;         -- 暫停檢查
ALTER TABLE t ENABLE CONSTRAINT pk_t;          -- 重新驗證現有數據

-- 欄位
ALTER TABLE t ADD (col1 VARCHAR2(10), col2 NUMBER);
ALTER TABLE t MODIFY (col1 VARCHAR2(20));
ALTER TABLE t DROP COLUMN col1;                -- 12c+ 支援 ONLINE
ALTER TABLE t RENAME COLUMN old_c TO new_c;    -- 12c+

-- 表重命名
RENAME old_t TO new_t;

序列：主鍵生成器


CREATE SEQUENCE seq_t
    START WITH 1 INCREMENT BY 1
    MAXVALUE 9999999999 NOCYCLE
    CACHE 20;          -- 緩存 20 個，提升效能；NOCACHE 保證連續但較慢

SELECT seq_t.NEXTVAL FROM dual;   -- 取下一值（自動 +1）
SELECT seq_t.CURRVAL FROM dual;   -- 當前會話最後取值

ALTER SEQUENCE seq_t INCREMENT BY 5;  -- 修改步長
DROP SEQUENCE seq_t;

視圖：邏輯簡化與權限控制


-- 簡單視圖（可 DML）
CREATE VIEW v_emp10 AS SELECT * FROM emp WHERE deptno = 10 WITH CHECK OPTION;

-- 複雜視圖（多表/聚合/ISTINCT → 唯讀）
CREATE VIEW v_dept_sum AS SELECT deptno, AVG(sal) FROM emp GROUP BY deptno;

-- 物化視圖（11g+，預計算加速查詢）
CREATE MATERIALIZED VIEW mv_sales_sum
BUILD IMMEDIATE REFRESH FAST ON COMMIT
AS SELECT prod_id, SUM(amt) FROM sales GROUP BY prod_id;

同義詞：透明訪問異 Schema 對象


CREATE SYNONYM emp FOR scott.emp;           -- 私有
CREATE PUBLIC SYNONYM pub_emp FOR scott.emp; -- 公共，需權限
DROP SYNONYM emp;

索引：加速查詢的核心


-- 基本
CREATE INDEX idx_emp_dept ON emp(deptno);
CREATE INDEX idx_emp_name ON emp(ename) TABLESPACE idx_tbs;

-- 組合索引（最左前綴原則）
CREATE INDEX idx_emp_dept_job ON emp(deptno, job);

-- 函數索引（11g+）
CREATE INDEX idx_emp_upper_name ON emp(UPPER(ename));
SELECT /*+ INDEX(e idx_emp_upper_name) */ * FROM emp e WHERE UPPER(ename) = 'SMITH';

-- 位圖索引（低基數列、數據倉庫）
CREATE BITMAP INDEX idx_emp_sex ON emp(sex);

-- 維護
ALTER INDEX idx_emp_dept REBUILD ONLINE;     -- 重建不鎖表
ALTER INDEX idx_emp_dept COALESCE;           -- 合併碎片

ROWID 物理定位與分頁


-- 查看 ROWID 組成
SELECT ROWID, DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID) AS file#,
       DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) AS block#,
       DBMS_ROWID.ROWID_ROW_NUMBER(ROWID) AS row#
FROM emp;

-- 12c+ 標準分頁
SELECT * FROM emp ORDER BY empno
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

-- 11g 經典分頁（確定性排序必加 ORDER BY）
SELECT * FROM (
    SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp ORDER BY empno) a
    WHERE ROWNUM <= 30
) WHERE rn > 20;

同義詞與權限速查

操作	系統權限	對象權限
建表	CREATE TABLE / CREATE ANY TABLE	—
查詢任意表	SELECT ANY TABLE	GRANT SELECT ON t TO u
建視圖	CREATE VIEW / CREATE ANY VIEW	—
建索引	CREATE ANY INDEX	—
授權角色	GRANT role TO user	—

常用系統表/視圖


USER_TABLES / ALL_TABLES / DBA_TABLES
USER_TAB_COLUMNS / DBA_TAB_COLUMNS
USER_INDEXES / DBA_INDEXES
USER_CONSTRAINTS / DBA_CONSTRAINTS
USER_SEQUENCES / DBA_SEQUENCES
USER_VIEWS / DBA_VIEWS
DBA_FREE_SPACE / DBA_DATA_FILES / DBA_TEMP_FILES
V$SESSION / V$PROCESS / V$SQL / V$PARAMETER

將本手冊作為日常開發運維的「語法外掛」，遇到語法細節「Ctrl+F 即得」，編碼效率提升 50% 以上。