您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 學習筆記精華：備份導入、常用函數、系統表、複合查詢實戰

編輯：Oracle 資料庫基礎

一、資料備份與導入 (EXP/IMP)

導出 (備份)：

exp userid/password@service_name owner=schema_name file=/path/backup.dmp log=/path/exp.log

導入 (恢復)：

imp userid/password@service_name full=y file=/path/backup.dmp log=/path/imp.log

現代替代：10g+ 強烈建議使用 Data Pump (expdp/impdp)：並行、可中斷續傳、網路模式、更細粒度物件過濾。

二、常用核心函數速查

DECODE - 條件分支 (Oracle 特有)

DECODE(表達式, 值1, 結果1, 值2, 結果2, ..., 預設結果)
-- 等同 CASE WHEN 但僅支援等值比較
SELECT DECODE(CJOB.SEX, 1, '男', 2, '女', 0, '不限', '未知') FROM COMPANYJOB CJOB;

TO_CHAR - 格式化轉換

TO_CHAR(日期/數值, '格式模板')
SELECT TO_CHAR(UPTIME, 'YYYY-MM-DD HH24:MI:SS') FROM LOGS;

三、複合查詢實戰：子查詢與多表聯結

SELECT 
  CJOB.ID, CJOB.MODULEID,
  (SELECT NICKNAME FROM USERS WHERE USERID = CJOB.USERID) AS USERNAME,
  (SELECT NAME FROM COMMCODE WHERE TYPE='C_JOB' AND VAL = CJOB.JOB) AS JOB_NAME,
  CJOB.DESCRIBE, CJOB.COUNT,
  DECODE(CJOB.SEX, 1, '男', 2, '女', 0, '不限') AS SEX,
  (SELECT NAME FROM COMMCODE WHERE TYPE='C_AGE' AND VAL = CJOB.AGE1) AS AGE1,
  (SELECT NAME FROM COMMCODE WHERE TYPE='C_AGE' AND VAL = CJOB.AGE2) AS AGE2,
  (SELECT NAME FROM COMMCODE WHERE TYPE='C_EDUCATION' AND VAL = CJOB.EDUCATION) AS EDUCATION,
  DECODE(CJOB.MARRY, 0, '不限', 1, '已婚', 2, '未婚') AS MARRY,
  (SELECT NAME FROM COMMCODE WHERE TYPE='C_EXPERIENCE' AND VAL = CJOB.EXPERIENCE) AS EXPERIENCE,
  CJOB.REQUIRE, CJOB.PLACE, CJOB.SALARY, CJOB.TREATMENT,
  CJOB.VALID, CJOB.ISVALID, CJOB.LEAVEDATE
FROM COMPANYJOB CJOB
WHERE CJOB.ID = 2;

優化建議

多次查詢同一碼表 → 改用 LEFT JOIN 或 WITH 子查詢因子化
碼表資料量小 → 考慮 結果緩存 (/*+ RESULT_CACHE */)
頻繁查詢 → 建立 物化視圖 預先聯結

四、核心系統表/視圖速查

查詢需求	系統視圖	範例
所有可存取表名	ALL_TABLES	SELECT * FROM ALL_TABLES WHERE OWNER='SCOTT'
表註解	ALL_TAB_COMMENTS	SELECT * FROM ALL_TAB_COMMENTS WHERE TABLE_NAME='EMP'
所有欄位結構	ALL_TAB_COLUMNS	SELECT COLUMN_NAME, DATA_TYPE FROM ALL_TAB_COLUMNS WHERE TABLE_NAME='EMP'
索引資訊	ALL_INDEXES, ALL_IND_COLUMNS	SELECT * FROM ALL_INDEXES WHERE TABLE_NAME='EMP'
約束條件	ALL_CONSTRAINTS, ALL_CONS_COLUMNS	SELECT * FROM ALL_CONSTRAINTS WHERE TABLE_NAME='EMP'
視圖定義	ALL_VIEWS	SELECT TEXT FROM ALL_VIEWS WHERE VIEW_NAME='EMP_VIEW'
序列	ALL_SEQUENCES	SELECT * FROM ALL_SEQUENCES WHERE SEQUENCE_NAME='EMP_SEQ'
同義詞	ALL_SYNONYMS	SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME='EMP'
PL/SQL 原始碼	ALL_SOURCE	SELECT TEXT FROM ALL_SOURCE WHERE NAME='MY_PKG' ORDER BY LINE
物件相依	ALL_DEPENDENCIES	SELECT * FROM ALL_DEPENDENCIES WHERE NAME='MY_PKG'

五、效能診斷入門查詢

-- 慢 SQL 定位
SELECT SQL_TEXT, EXECUTIONS, ELAPSED_TIME/EXECUTIONS AVG_MS
FROM V$SQL 
WHERE EXECUTIONS > 0 
ORDER BY ELAPSED_TIME/EXECUTIONS DESC;

-- 表空間使用率
SELECT TABLESPACE_NAME, 
       ROUND(SUM(BYTES)/1024/1024,2) TOTAL_MB,
       ROUND(SUM(BYTES)/1024/1024 - NVL(FREE_MB,0),2) USED_MB
FROM DBA_DATA_FILES
LEFT JOIN (SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 FREE_MB FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) USING (TABLESPACE_NAME)
GROUP BY TABLESPACE_NAME, FREE_MB;