Oracle DBA 必備命令速查手冊：用戶管理、表空間操作、權限控制、鎖處理、空間監控一站式匯總

用戶與權限管理

創建用戶標準流程

-- 1. 創建表空間（建議分離數據/索引/臨時）
CREATE TABLESPACE app_data 
DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

CREATE TEMPORARY TABLESPACE app_temp 
TEMPFILE '/u01/app/oracle/oradata/app_temp01.dbf' SIZE 50M AUTOEXTEND ON NEXT 50M MAXSIZE 5G;

-- 2. 創建用戶並指定默認表空間
CREATE USER app_user IDENTIFIED BY "ComplexPass#2024"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE app_temp
QUOTA UNLIMITED ON app_data;

-- 3. 授予最小權限集
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE TO app_user;
-- 生產環境嚴禁 GRANT DBA TO app_user;

常用用戶操作

表空間空間監控核心查詢

表空間總容量

SELECT tablespace_name "表空間", 
       ROUND(SUM(bytes)/1024/1024, 2) "總容量_MB"
FROM dba_data_files
GROUP BY tablespace_name
ORDER BY 2 DESC;

表空間使用率詳細版（含自動擴展考量）

SELECT 
    a.tablespace_name "表空間",
    ROUND(a.max_bytes/1024/1024, 2) "最大容量_MB",
    ROUND(a.bytes/1024/1024, 2) "當前容量_MB",
    ROUND((a.bytes - b.free_bytes)/1024/1024, 2) "已用_MB",
    ROUND(b.free_bytes/1024/1024, 2) "可用_MB",
    ROUND((a.bytes - b.free_bytes) / a.max_bytes * 100, 2) "使用率_%"
FROM (
    SELECT tablespace_name, 
           SUM(bytes) bytes, 
           SUM(DECODE(autoextensible, 'YES', maxbytes, bytes)) max_bytes
    FROM dba_data_files
    GROUP BY tablespace_name
) a,
(
    SELECT tablespace_name, SUM(bytes) free_bytes
    FROM dba_free_space
    GROUP BY tablespace_name
) b
WHERE a.tablespace_name = b.tablespace_name(+)
ORDER BY 6 DESC;

臨時表空間使用情況

SELECT tablespace_name, 
       ROUND(tablespace_size/1024/1024, 2) "總量_MB",
       ROUND(allocated_space/1024/1024, 2) "已分配_MB",
       ROUND(free_space/1024/1024, 2) "空閒_MB"
FROM dba_temp_free_space;

鎖衝突處理標準流程

查詢阻塞鏈

-- 1. 找到阻塞會話 (BLOCKER)
SELECT s.sid, s.serial#, s.username, s.machine, s.program, s.logon_time,
       s.sql_id, sq.sql_text
FROM v$session s, v$sql sq
WHERE s.sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL)
AND s.sql_id = sq.sql_id(+);

-- 2. 找到被阻塞會話 (WAITER)
SELECT w.sid, w.serial#, w.username, w.event, w.seconds_in_wait,
       w.sql_id, sq.sql_text
FROM v$session w, v$sql sq
WHERE w.blocking_session IS NOT NULL
AND w.sql_id = sq.sql_id(+);

生成 Kill 腳本

SELECT 'ALTER SYSTEM KILL SESSION ''' || sid || ',' || serial# || ''' IMMEDIATE;'
FROM v$session
WHERE sid IN (SELECT blocking_session FROM v$session WHERE blocking_session IS NOT NULL);

執行 Kill 並驗證

-- 執行生成的語句
ALTER SYSTEM KILL SESSION '136,18257' IMMEDIATE;

-- 驗證鎖釋放
SELECT * FROM v$lock WHERE block = 1;

常見維護操作集錦

表空間擴容

-- 數據文件擴容
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' RESIZE 500M;

-- 添加數據文件
ALTER TABLESPACE app_data ADD DATAFILE 
'/u01/app/oracle/oradata/app_data02.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

表空間離線/刪除

-- 離線 (需先確保無活躍事務)
ALTER TABLESPACE app_data OFFLINE NORMAL;

-- 刪除 (含數據文件)
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

參數文件操作

-- SPFILE 創建/備份
CREATE SPFILE FROM PFILE;
CREATE PFILE='/tmp/init_backup.ora' FROM SPFILE;

-- 參數修改 (SCOPE=SPFILE 需重啟生效，MEMORY 即時生效)
ALTER SYSTEM SET db_cache_size = 2G SCOPE=SPFILE;
ALTER SYSTEM SET shared_pool_size = 1G SCOPE=BOTH;

日志切換與歸檔

-- 強制日志切換
ALTER SYSTEM SWITCH LOGFILE;

-- 檢查歸檔模式
ARCHIVE LOG LIST;

-- 手動歸檔當前日志
ALTER SYSTEM ARCHIVE LOG CURRENT;

性能診斷快速入口

腳本化巡檢建議

將上述核心查詢封裝為 daily_check.sql，配合 crontab 定時執行，結果輸出 HTML/CSV 郵件推送，關鍵指標（表空間使用率>80%、鎖等待>5min、緩存命中率<90%）觸發告警。