Oracle DBA 必備 20 條實用監控 SQL：鎖、空間、字符集、殺會話全覆蓋

核心監控 SQL 收藏夾

1. 查詢被鎖對象與會話

SELECT uo.object_name, uo.object_id, lo.oracle_username, lo.locked_mode
FROM user_objects uo, v$locked_object lo
WHERE uo.object_id = lo.object_id;

2. 查看資料庫字符集

SELECT * FROM nls_database_parameters;

3. 查看表創建/修改時間

SELECT * FROM all_objects WHERE object_name = UPPER('tname');

4. 統計各表佔用空間（按大小降序）

SELECT segment_name, ROUND(SUM(bytes)/1024/1024, 0) "MBytes"
FROM user_extents
GROUP BY segment_name
ORDER BY "MBytes" DESC;

5. 查詢正在執行的存儲過程（DDL 鎖）

SELECT * FROM dba_ddl_locks WHERE owner = 'USERNAME';

6. 表空間使用率詳細報表

SELECT a.tablespace_name,
       ROUND(a.bytes/(1024*1024),2) "Total MB",
       ROUND(b.bytes/(1024*1024),2) "Used MB",
       ROUND(c.bytes/(1024*1024),2) "Free MB",
       ROUND((b.bytes*100)/a.bytes,2) || '%' "% Used",
       ROUND((c.bytes*100)/a.bytes,2) || '%' "% Free"
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
  AND a.tablespace_name = c.tablespace_name;

註：10g 以上建議使用 DBA_TABLESPACE_USAGE_METRICS 或 DBA_FREE_SPACE + DBA_DATA_FILES 組合查詢，兼容性更好。

7. 鎖表與解鎖殺會話完整流程

-- 加鎖
LOCK TABLE dept IN SHARE MODE;

-- 查找持有鎖的會話
SELECT sid, serial#, username, osuser FROM v$session WHERE sid IN (SELECT session_id FROM v$locked_object);

-- 正常殺會話
ALTER SYSTEM KILL SESSION 'sid,serial#';
-- 例: ALTER SYSTEM KILL SESSION '131,33574';

8. 處理「ORA-00031: session marked for kill」頑固會話

當 KILL SESSION 只標記不立即釋放資源時，在 OS 層面強制終止：

-- 查找被標記 KILLED 會話的 OS 進程號 (SPID)
SELECT 'kill -9 ' || a.spid "OS_KILL_CMD"
FROM v$process a, v$session b
WHERE a.addr = b.paddr
  AND b.status = 'KILLED';

在 Linux/Unix 執行輸出的命令；Windows 使用 ORAKILL SID SPID。

9. 增加表空間數據文件

ALTER TABLESPACE bgtablespace01
ADD DATAFILE '/opt/oracle/oradata/orcl/bgtablespace01/bgtablespace0190.dbf' SIZE 5120M;

10. 修改用戶預設表空間

ALTER USER user_name DEFAULT TABLESPACE bgtablespace01;

11. 大表新增帶默認值欄位的性能陷阱與優化

❌ 慢寫法（全表更新，耗時數小時）：

ALTER TABLE tab_large ADD col_new NUMBER(9) DEFAULT 1;

✅ 快寫法（元數據操作，秒級完成）：

ALTER TABLE tab_large ADD col_new NUMBER(9);
ALTER TABLE tab_large MODIFY col_new DEFAULT 1;

原理：11g 起優化，ADD COLUMN DEFAULT 僅修改字典，不更新現有行。若需回填歷史數據，事後分批 UPDATE 即可。

12. 建立用戶並授權

CREATE USER sjzq IDENTIFIED BY sjzq123
  DEFAULT TABLESPACE BGTABLESPACE01
  TEMPORARY TABLESPACE Temp;
GRANT CONNECT TO sjzq;
GRANT SELECT ON abc.tname TO sjzq;