Oracle DBA 必備查詢指令大全：鎖定、表空間、字符集、大表優化等實戰 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. 查詢正在執行的預存程序

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;

注意：sm$ts_* 為特定版本視圖，通用寫法請改用 DBA_FREE_SPACE + DBA_DATA_FILES。

鎖定與 Session 管理

7. 表鎖定語法

LOCK TABLE dept IN SHARE MODE;

8. 強制終止 Session

-- 步驟 1：查詢 SID, SERIAL#
SELECT sid, serial#, username, osuser FROM v$session;

-- 步驟 2：標記終止
ALTER SYSTEM KILL SESSION 'sid,serial#';
-- 例：ALTER SYSTEM KILL SESSION '131,33574';

9. OS 層級強制殺掉已標記 KILLED 的進程

SELECT 'kill -9 ' || a.spid "xxx"
FROM v$process a, v$session b
WHERE a.addr = b.paddr AND b.status = 'KILLED';

若 ALTER SYSTEM KILL SESSION 回傳 ORA-00031: session marked for kill，表示 Session 仍在清理中，需等待或使用 OS 指令強制結束。

結構變更與優化技巧

10. 新增表空間與資料檔

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

11. 修改使用者預設表空間

ALTER USER user_name 
DEFAULT TABLESPACE bgtablespace01;

12. 大表新增帶預設值欄位的效能優化

問題：ALTER TABLE tab_large ADD col_new NUMBER(9) DEFAULT 1; 會鎖表更新 3000 萬筆資料，耗時數小時。

解法：分兩步驟，避免全表更新：

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

第二步僅更新字典中繼資料，新增資料自動帶入預設值，耗時秒級。舊資料需更新時，另行排程 UPDATE 分批處理。

13. 建立使用者與授權

CREATE USER sjzq IDENTIFIED BY sjzq123
DEFAULT TABLESPACE bgtablespace01
TEMPORARY TABLESPACE temp;

GRANT CONNECT TO sjzq;
GRANT SELECT ON abc.tname TO sjzq;

總結

這組查詢涵蓋 DBA 日常 80% 的監控與維護場景。建議整理為個人腳本庫，搭配 SQL*Plus 或 SQLcl 格式化輸出，提升操作效率。