Oracle 常用管理命令速查：表/日誌/解鎖/歸檔/權限

表對象查詢

-- 全庫所有表 (需 DBA 權限)
SELECT * FROM dba_tables;

-- 當前用戶表
SELECT * FROM user_tables;

日誌檔案查詢

-- 聞取日誌文件成員路徑
SELECT member FROM v$logfile;

用戶解鎖

ALTER USER username ACCOUNT UNLOCK;

歸檔日誌預設位置

D:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\ORCL\

實際路徑取決於 `DB_RECOVERY_FILE_DEST` 參數與 `DB_UNIQUE_NAME`。

擴展：更多實用管理查詢

表空間使用率

SELECT tablespace_name,
       ROUND(SUM(bytes)/1024/1024) AS total_mb,
       ROUND(SUM(bytes)/1024/1024 - NVL(SUM(free_bytes),0)/1024/1024) AS used_mb,
       ROUND(100 * (1 - NVL(SUM(free_bytes),0)/SUM(bytes))) AS pct_used
FROM (
  SELECT tablespace_name, bytes, 0 AS free_bytes FROM dba_data_files
  UNION ALL
  SELECT tablespace_name, 0, bytes FROM dba_free_space
)
GROUP BY tablespace_name
ORDER BY pct_used DESC;

長時間運行 SQL (Top SQL)

SELECT sql_id, elapsed_time/1e6 AS elapsed_sec, sql_text
FROM v$sql
WHERE elapsed_time > 10000000  -- >10 秒
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

鎖等待鏈

SELECT blocking_session, session_id, sql_id, event, seconds_in_wait
FROM v$session
WHERE blocking_session IS NOT NULL
ORDER BY seconds_in_wait DESC;

當前活躍會話

SELECT s.sid, s.serial#, p.spid, s.status, s.program, s.module, s.sql_id
FROM v$session s, v$process p
WHERE s.paddr = p.addr
  AND s.status = 'ACTIVE'
  AND s.type != 'BACKGROUND'
ORDER BY s.logon_time;

殺掉會話

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;