Oracle 日常維護 21 條核心 SQL 語句速查手冊

空間與存儲

1. 表空間名稱與大小

   SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES/1024/1024),0) TS_SIZE
   FROM DBA_TABLESPACES T, DBA_DATA_FILES D
   WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
   GROUP BY T.TABLESPACE_NAME;

2. 數據文件明細

   SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROUND(BYTES/1024/1024,0) TOTAL_SPACE
   FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

3. 回滾段/Undo 段

   SELECT SEGMENT_NAME, TABLESPACE_NAME, R.STATUS,
          INITIAL_EXTENT/1024 INITIAL_EXTENT, NEXT_EXTENT/1024 NEXT_EXTENT,
          MAX_EXTENTS, V.CUREXT CUR_EXT
   FROM DBA_ROLLBACK_SEGS R, V$ROLLSTAT V
   WHERE R.SEGMENT_ID = V.USN(+)
   ORDER BY SEGMENT_NAME;

4. 控制文件 / 日志文件

   SELECT NAME FROM V$CONTROLFILE;
   SELECT MEMBER FROM V$LOGFILE;

5. 表空間使用率 (兩種寫法)

   -- 簡易版
   SELECT SUM(BYTES)/1024/1024 FREE_SPACE, TABLESPACE_NAME
   FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;
   
   -- 詳細版 (含使用率%)
   SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES FREE,
          (B.BYTES*100)/A.BYTES "% USED", (C.BYTES*100)/A.BYTES "% 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;

對象與版本

6. 數據庫對象統計

   SELECT OWNER, OBJECT_TYPE, STATUS, COUNT(*)
   FROM ALL_OBJECTS GROUP BY OWNER, OBJECT_TYPE, STATUS;

7. 資料庫版本

   SELECT VERSION FROM PRODUCT_COMPONENT_VERSION
   WHERE SUBSTR(PRODUCT,1,6)='Oracle';

8. 創建時間與歸檔模式

   SELECT CREATED, LOG_MODE FROM V$DATABASE;

9. 當前用戶對象列表

   SELECT * FROM TAB;

常用 DDL 技巧

10. 複製表結構 (無數據)

    CREATE TABLE B AS SELECT * FROM A WHERE 1=2;
    CREATE TABLE B(B1,B2,B3) AS SELECT A1,A2,A3 FROM A WHERE 1=2;

11. 空間使用詳情 (含文件級)

    COL TABLESPACE FORMAT A20
    SELECT B.FILE_ID, B.TABLESPACE_NAME, B.FILE_NAME, B.BYTES,
           (B.BYTES-SUM(NVL(A.BYTES,0))) USED,
           SUM(NVL(A.BYTES,0)) FREE,
           SUM(NVL(A.BYTES,0))/B.BYTES*100 FREE_PCT
    FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
    WHERE A.FILE_ID(+)=B.FILE_ID
    GROUP BY B.TABLESPACE_NAME,B.FILE_NAME,B.FILE_ID,B.BYTES
    ORDER BY B.TABLESPACE_NAME;

回滾段與數據文件路徑

12. 回滾段狀態

    COL SEGMENT FORMAT A30
    SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, FILE_ID, STATUS
    FROM DBA_ROLLBACK_SEGS;

13. 數據文件路徑

    COL FILE_NAME FORMAT A50
    SELECT TABLESPACE_NAME, FILE_ID, BYTES/1024/1024 MB, FILE_NAME
    FROM DBA_DATA_FILES ORDER BY FILE_ID;

實用小技巧

14. 顯示當前用戶：SHOW USER
15. SQL*Plus 當計算器：SELECT 100*20 FROM DUAL;
16. 字符串拼接：COL1 || COL2 或 CONCAT(COL1,COL2)
17. 格式化當前時間：SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;

跨用戶複製表

18. COPY FROM USER1 TO USER2 CREATE TABLE2 USING SELECT * FROM TABLE1;

視圖排序繞過

19. 視圖不可用 ORDER BY，用 GROUP BY 達到同序效果：

    CREATE VIEW A AS SELECT B1,B2 FROM B GROUP BY B1,B2;

一鍵建用戶授權

20. GRANT CONNECT,RESOURCE TO TEST IDENTIFIED BY TEST; CONN TEST/TEST