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

Oracle DBA 隱藏技能包：7 個實用腳本與進階技巧全解析

編輯：Oracle 資料庫相關知識

前言：資深 DBA 的「瑞士刀」工具箱

日常運維中，掌握幾個關鍵查詢腳本往往能化險為夷：隱含參數探查、元件清單、共享記憶體剖析、Session 追蹤、字符集確認、當前 SQL 監控、重複資料清理。本文深度解析 7 大實戰技巧，提供可直接生產部署的 SQL/Shell 腳本，並解析底層原理與擴展應用。

技巧一：查詢所有隱含參數 (Underscore Parameters)

隱含參數以 _ 開頭，不顯示於 SHOW PARAMETER，但影響核心行為 (如 _optimizer_use_feedback, _serial_direct_read)。

-- 1. 查詢所有隱含參數名稱
SELECT ksppinm AS parameter_name
FROM sys.x$ksppi
WHERE substr(ksppinm, 1, 1) = '_'
ORDER BY 1;

-- 2. 連接 x$ksppcv 獲取當前值 (需 SYS 權限)
SELECT a.ksppinm AS name, b.ksppstvl AS value, a.ksppdesc AS description
FROM sys.x$ksppi a, sys.x$ksppcv b
WHERE a.indx = b.indx
  AND substr(a.ksppinm, 1, 1) = '_'
ORDER BY a.ksppinm;

擴展：建立基線表定期對比，異動即告警。警示：修改隱含參數需 Oracle Support 批准，否則喪失支援資格。

技巧二：快速清點已安裝 Oracle 元件與版本

-- OS 層面 (適用所有平台)
cd $ORACLE_HOME/OPatch
./opatch lsinventory -detail

-- 或查詢資料字典 (資料庫啟動後)
SELECT comp_name, version, status
FROM dba_registry
ORDER BY comp_name;

opatch lsinventory 可見 Patch 級別、互斥關係；DBA_REGISTRY 適合腳本化巡檢。

技巧三：剖析 Oracle 佔用的共享記憶體段

Oracle SGA 在 OS 層面為一或多個共享記憶體段 (Shared Memory Segments)。

-- 1. OS 命令查看所有共享記憶體
ipcs -m

-- 2. 或用 oradebug 直接映射到 Oracle 實例
SQL> oradebug setmypid
SQL> oradebug ipc

輸出解讀：

-------------- Shared memory --------------
Seg Id  Address    Size
1153    0x7fe000   784           -- 微小段，通常為固定 SGA 區
1154    0x800000   419430400     -- 400MB，主 SGA (Buffer Cache + Shared Pool + Log Buffer)
1155    0x19800000 67108864      -- 64MB，可能為 Large Pool / Java Pool / Streams Pool

用途：驗證 SGA 目標大小是否真實生效、排查 OS 共享記憶體耗盡 (ORA-27123)、大頁 (HugePages) 配置驗證。

技巧四：精準定位當前 SQL*Plus Session 的 SID/SERIAL#

SELECT sid, serial#, status, username, machine, program, logon_time
FROM v$session
WHERE audsid = USERENV('SESSIONID');

優於 SELECT SID FROM V$MYSTAT (僅得 SID)，此法同時獲取 SERIAL#，可直接 ALTER SYSTEM KILL SESSION 或啟用 Trace。

技巧五：秒級確認資料庫/Session 字符集

-- 資料庫字符集
SELECT value FROM nls_database_parameters WHERE parameter = 'NLS_CHARACTERSET';
-- 或
SELECT * FROM v$nls_parameters WHERE parameter IN ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

-- 當前 Session 字符集 (受 NLS_LANG 影響)
SELECT userenv('language') FROM dual;
-- 輸出範例: AMERICAN_AMERICA.ZHS16GBK (語言_地區.字符集)

關鍵：資料庫字符集決定儲存編碼；Session 字符集決定客戶端轉換。兩者不一致導致亂碼。

技巧六：實時監控指定用戶/機器正在執行的 SQL 完整文本

SELECT s.sid, s.serial#, s.username, s.machine, s.sql_id, t.sql_text
FROM v$session s
JOIN v$sqltext t ON t.address = s.sql_address AND t.hash_value = s.sql_hash_value
WHERE s.type = 'USER'
  AND (s.machine = 'APP_SERVER_01' OR s.username = 'SCOTT')
  AND s.status = 'ACTIVE'
ORDER BY s.sid, t.piece;

進階：若 SQL 跨多個子游標 (V$SQL 多行)，用 DBMS_LOB.SUBSTR 組裝完整 CLOB：

SELECT sql_fulltext FROM v$sql WHERE sql_id = '&SQL_ID';

技巧七：高效刪除大表重複資料 (保留唯一行)

場景：無主鍵表、匯入髒資料、ETL 異常導致重複。核心邏輯：ROWID 分組保留最小值。

-- 標準寫法 (適用中小表)
DELETE FROM table_name a
WHERE rowid > (
    SELECT MIN(rowid)
    FROM table_name b
    WHERE b.pk_col1 = a.pk_col1
      AND b.pk_col2 = a.pk_col2
      -- 所有業務主鍵欄位
);

-- 大表優化版: CTAS 重建法 (最快、產生最少 Redo/Undo)
CREATE TABLE table_name_dedup NOLOGGING AS
SELECT /*+ PARALLEL(8) */ *
FROM (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY pk_col1, pk_col2 ORDER BY rowid) rn
    FROM table_name t
)
WHERE rn = 1;

-- 完成後: 索引/約束/權限/同義詞/觸發器 遷移 -> RENAME 切換

關鍵點：

業務主鍵 (PK_COL1, PK_COL2) 必須能唯一識別重複群組。
CTAS 法並行度視 CPU 核心數調整，NOLOGGING 大幅減少 Redo (需配合備份策略)。
刪除法產生大量 Undo/Redo，易導致 Undo 表空間暴漲、Long Running Transaction。

進階技巧：一鍵生成 Schema 級 DDL 遷移腳本

-- 利用 DBMS_METADATA 導出表、索引、約束、授權
SET LONG 1000000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
SPOOL schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('TABLE', table_name, owner) FROM dba_tables WHERE owner = 'SCOTT';
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) FROM dba_indexes WHERE owner = 'SCOTT';
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) FROM dba_constraints WHERE owner = 'SCOTT';
SPOOL OFF

適用於異構遷移、測試環境克隆、版本控管 Schema 變更。