Oracle 常用運維 SQL 速查手冊（二）：存儲過程、分區、並行、壓縮、批量執行實戰語法

針對 Oracle DBA/開發高頻操作場景，彙整存儲過程查詢/編譯、分區管理、並行 DDL、表壓縮、批量執行腳本等實戰語法，可直接複製到工具箱或自動化腳本中使用。

一、存儲過程查詢與編譯

-- 查看存儲過程原始碼
SELECT text FROM user_source WHERE name = UPPER('sp_kr_pd_markplan_info') ORDER BY line;

-- 查看是否被調用（假設有審計表）
SELECT * FROM dgdm_dw.tb_dw_sys_log WHERE table_name = 'TB_DW_MD_WORKF_MARKPLAN_CUR';

-- 編譯存儲過程（解決 INVALID 狀態）
ALTER PROCEDURE sp_kr_pd_markplan_sta_QQt COMPILE;
ALTER FUNCTION  fn_xxx COMPILE;
ALTER PACKAGE   pkg_xxx COMPILE BODY;  -- 或 COMPILE SPECIFICATION

-- 搜尋存儲過程中引用特定表
SELECT * FROM user_source WHERE UPPER(text) LIKE '%TB_KR_KPI_NUSER_DAY%';

二、分區管理完整語法

-- 查看分區資訊
SELECT table_name, partition_name, tablespace_name, num_rows, blocks
FROM   user_tab_partitions
WHERE  table_name = 'TB_MK_LS_USER_CELL_DTAL_MID'
ORDER BY partition_position;

-- 刪除單一分區
ALTER TABLE table_name DROP PARTITION partition_name UPDATE GLOBAL INDEXES;

-- 批次產生刪除所有分區腳本
SELECT 'ALTER TABLE '||owner||'.'||segment_name||' DROP PARTITION '||partition_name||' UPDATE GLOBAL INDEXES;'
FROM   dba_segments
WHERE  segment_name = 'TB_MK_LS_USER_CELL_DTAL_MID'
ORDER BY partition_name;

-- 新增分區（範圍分區）
ALTER TABLE dgdm_mk.TB_MK_SC_CALL_FEE_DAY
  ADD PARTITION call_fee_day_20070930
  VALUES LESS THAN (TO_DATE('20070930','YYYYMMDD'))
  TABLESPACE tbs_mk_list_data_3;

-- 截斷分區（極快，保留結構）
ALTER TABLE table_name TRUNCATE PARTITION partition_name;

-- 移動分區（換表空間/壓縮/重建索引）
ALTER TABLE table_name MOVE PARTITION partition_name TABLESPACE new_ts COMPRESS UPDATE INDEXES;

三、並行 DDL / DML 與壓縮

-- 平行建立索引
CREATE INDEX idx_x ON t(col) PARALLEL 8 TABLESPACE idx_ts;

-- 平行重建索引
ALTER INDEX idx_x REBUILD PARALLEL 8;

-- 平行 CTAS
CREATE TABLE t_new NOLOGGING PARALLEL 8 AS SELECT * FROM t_old;

-- 啟用表壓縮（OLTP / BASIC / QUERY / ARCHIVE）
ALTER TABLE t_move MOVE COMPRESS FOR OLTP;           -- 11g+ OLTP 壓縮
ALTER TABLE t_move MOVE COMPRESS FOR QUERY HIGH;     -- 12c+ Hybrid Columnar
ALTER TABLE t_move MOVE COMPRESS FOR ARCHIVE HIGH;   -- 歸檔壓縮

-- 分區級壓縮
EXECUTE IMMEDIATE 'ALTER TABLE tb_mk_user_opp_last_call_day MOVE PARTITION opp_last_call_day_'||v_date||' COMPRESS FOR OLTP';

四、批量執行存儲過程腳本範例

run.sql 檔案內容：

VAR hhh NUMBER;
EXEC dgdm_mk.SP_DWMK_OPP_USER_CELL_MON('20070815', :hhh);
/
EXEC dgdm_mk.SP_DWMK_OPP_USER_INFO('20070815', :hhh);
/
EXEC dgdm_mk.SP_DWMK_OPP_USER_INFO_WEEK('20070815', :hhh);
/
-- 依此類推...

執行方式：

sqlplus dgdm_mk/password @run.sql

進階：帶邏輯控制的 PL/SQL 批次塊

BEGIN
  FOR dt IN (SELECT TO_CHAR(dt,'YYYYMMDD') d FROM date_dim WHERE dt BETWEEN DATE '2007-08-01' AND DATE '2007-08-31')
  LOOP
    dgdm_mk.SP_DWMK_OPP_USER_CELL_MON(dt.d, :hhh);
    COMMIT;  -- 或每 N 次提交一次
  END LOOP;
END;
/

五、查看表結構（含索引、約束、分區）

-- DBMS_METADATA 完整 DDL
SELECT DBMS_METADATA.GET_DDL('TABLE','TB_KR_SC_VIP_MON','DGDM_KR') FROM DUAL;

-- 快速描述
DESC tb_kr_sc_vip_mon;
-- 或
SELECT column_name, data_type, data_length, nullable, data_default
FROM   user_tab_columns WHERE table_name = 'TB_KR_SC_VIP_MON';

六、實用小技巧

將上述語法整理為個人 cheatsheet.sql，配合編輯器片段功能，讓日常運維「指尖即得」，減少翻閱文件時間，專注於核心問題解決。