Oracle 實用查詢語句大全：分頁、字典查詢、系統參數、實例資訊、字符集、時間等常用 SQL 收錄

Oracle DBA/開發者必備查詢語句速查手冊

整理實務高頻查詢：分頁模板、資料字典瀏覽、系統參數檢視、實例/資料庫資訊、字符集/時間/用戶/權限等，附帶 iBATIS/PreparedStatement 參數化分頁寫法。

1. 通用分頁查詢模板

-- 標準三層 ROWNUM (10g/11g)
SELECT * FROM (
    SELECT A.*, ROWNUM RN FROM (
        SELECT * FROM YOUR_TABLE WHERE CONDITIONS ORDER BY SORT_COL
    ) A WHERE ROWNUM <= :END_ROW
) WHERE RN > :START_ROW;

-- iBATIS 參數化範例
String sql = "SELECT * FROM " + tableName;
String pageSql = "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (" + sql + ") A WHERE ROWNUM <= ?) WHERE RN > ?";
PreparedStatement ps = conn.prepareStatement(pageSql);
ps.setInt(1, pageIndex * pageSize);   // endRow
ps.setInt(2, (pageIndex - 1) * pageSize); // startRow

2. 資料字典與物件瀏覽

3. 系統參數與環境資訊

-- 顯示參數 (支援模糊)
SHOW PARAMETER DB;
SHOW PARAMETER DOMAIN;
SHOW PARAMETER SERVICE_NAME;

-- SQL 查詢參數
SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME LIKE '%DB%';

-- 域名
SELECT VALUE FROM V$PARAMETER WHERE NAME = 'DB_DOMAIN';

-- 服務名
SHOW PARAMETER SERVICE_NAME;

-- 當前時間
SELECT SYSDATE FROM DUAL;

-- 字符集相關參數
SELECT * FROM V$NLS_PARAMETERS;
-- 或
SELECT * FROM NLS_DATABASE_PARAMETERS;      -- 資料庫層級
SELECT * FROM NLS_INSTANCE_PARAMETERS;      -- 實例層級
SELECT * FROM NLS_SESSION_PARAMETERS;       -- 會話層級

4. 直接以 SYSDBA 登入

SQLPLUS "/ AS SYSDBA"
-- 或
SQLPLUS SYS/PASSWORD AS SYSDBA

5. 表空間與資料檔狀態

-- 表空間使用率
SELECT TABLESPACE_NAME, 
       ROUND(SUM(BYTES)/1024/1024,2) TOTAL_MB,
       ROUND(SUM(DECODE(AUTOEXTENSIBLE,'YES',MAXBYTES,BYTES))/1024/1024,2) MAX_MB
FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;

-- 空間剩餘
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 FREE_MB
FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

6. 鎖與阻塞查詢

-- 被阻塞會話
SELECT S.SID, S.SERIAL#, S.USERNAME, S.MACHINE, S.PROGRAM,
       BLOCKING_SESSION, BLOCKING_SESSION_STATUS
FROM V$SESSION S WHERE BLOCKING_SESSION IS NOT NULL;

-- 鎖定物件
SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, L.LMODE, L.REQUEST
FROM V$LOCKED_OBJECT O
JOIN V$SESSION S ON O.SESSION_ID = S.SID
JOIN V$LOCK L ON S.SID = L.SID;

7. 長時間執行 SQL (Top SQL)

SELECT SQL_ID, ELAPSED_TIME/1E6 ELAPSED_SEC, EXECUTIONS, 
       ELAPSED_TIME/1E6/EXECUTIONS AVG_SEC, SQL_TEXT
FROM V$SQL
WHERE EXECUTIONS > 0
ORDER BY ELAPSED_TIME/EXECUTIONS DESC
FETCH FIRST 20 ROWS ONLY;

使用提醒

• V$ 視圖需 SELECT_CATALOG_ROLE 或 DBA 權限
• ALL_ 視圖顯示可存取物件，USER_ 僅當前用戶，DBA_ 全庫 (需權限)
• 生產環境查詢 V$SQL/V$SESSION 等動態視圖時建議加 WHERE 條件限制範圍
• 12c+ 可用 FETCH FIRST N ROWS ONLY 替代 ROWNUM