整理 Oracle 核心管理與開發知識點，涵蓋：用戶/角色/權限體系、表空間操作、常用函數、PL/SQL 結構、EXP/IMP 備份還原、數據字典查詢，適合 DBA 與開發快速查閱。
1. 內建用戶與角色
| 用戶 | 角色/權限 | 用途 |
|---|
| SYS | DBA | 最高權限，擁有數據字典基表 |
| SYSTEM | DBA, OPER | 系統管理員，日常維護 |
| SCOTT | CONNECT, RESOURCE | 教學測試用戶 |
2. 常用 SQL*Plus 命令
SHOW USER -- 顯示當前用戶
CONN user/pass -- 切換用戶
EDIT file.sql -- 編輯腳本
SPOOL file.log ... SPOOL OFF -- 記錄輸出
3. 用戶與權限管理
-- 建立用戶
CREATE USER app_user IDENTIFIED BY pwd;
-- 授權角色
GRANT CONNECT, RESOURCE TO app_user;
-- 授權系統權限
GRANT CREATE TABLE, CREATE VIEW TO app_user;
-- 授權對象權限
GRANT SELECT, INSERT ON scott.emp TO app_user;
-- 授權可傳遞
GRANT SELECT ON scott.emp TO app_user WITH GRANT OPTION;
-- 系統權限可傳遞
GRANT DBA TO app_user WITH ADMIN OPTION;
-- 解鎖
ALTER USER app_user ACCOUNT UNLOCK;
-- 刪除用戶（含物件）
DROP USER app_user CASCADE;
4. 表空間管理
-- 建立本地管理表空間
CREATE TABLESPACE app_data
DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' SIZE 500M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
-- 狀態控制
ALTER TABLESPACE app_data OFFLINE; -- 脫機
ALTER TABLESPACE app_data ONLINE; -- 聯機
ALTER TABLESPACE app_data READ ONLY;
ALTER TABLESPACE app_data READ WRITE;
-- 擴容
ALTER TABLESPACE app_data ADD DATAFILE '/u01/.../app_data02.dbf' SIZE 500M;
-- 刪除（含資料檔）
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES;
-- 指定表建立於表空間
CREATE TABLE t (...) TABLESPACE app_data;
5. 常用函數速查
| 類別 | 函數 | 範例 |
|---|
| 字串 | UPPER, LOWER, SUBSTR, LENGTH, REPLACE | SUBSTR('abcde',2,3) → 'bcd' |
| 數字 | ROUND, TRUNC, MOD, POWER | ROUND(45.926,2) → 45.93 |
| 日期 | SYSDATE, ADD_MONTHS, LAST_DAY, MONTHS_BETWEEN | ADD_MONTHS(SYSDATE,3) |
| 轉換 | TO_CHAR, TO_DATE, TO_NUMBER | TO_CHAR(SYSDATE,'YYYY-MM-DD') |
| 空值 | NVL, NVL2, COALESCE | NVL(comm,0) |
| 系統 | SYS_CONTEXT('USERENV','DB_NAME') | 取得資料庫名稱 |
6. PL/SQL 結構範例
DECLARE
v_sal emp.sal%TYPE;
v_ename emp.ename%TYPE;
BEGIN
FOR rec IN (SELECT sal, ename FROM emp) LOOP
IF rec.sal < 2500 THEN
DBMS_OUTPUT.PUT_LINE(rec.ename || ' 普通員工');
ELSE
DBMS_OUTPUT.PUT_LINE(rec.ename || ' 高級員工');
END IF;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('無資料');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('多筆資料');
END;
/
-- 遊標 FOR 迴圈隱式開啟/關閉，最簡潔
7. EXP/IMP 備份還原（傳統模式）
-- 匯出表
EXP userid=scott/tiger@orcl TABLES=(emp,dept) FILE=exp_emp.dmp
-- 僅匯出結構
EXP userid=scott/tiger@orcl TABLES=emp FILE=exp_emp.dmp ROWS=N
-- 直接模式（快）
EXP userid=scott/tiger@orcl TABLES=emp FILE=exp_emp.dmp DIRECT=Y
-- 匯出用戶所有物件
EXP userid=scott/tiger@orcl OWNER=scott FILE=exp_scott.dmp
-- 全庫匯出
EXP userid=system/admin@orcl FULL=Y FILE=full.dmp
-- 匯入
IMP userid=scott/tiger@orcl FILE=exp_emp.dmp TABLES=emp
IMP userid=system/admin@orcl FILE=full.dmp FULL=Y
現代替代：Data Pump (EXPDP/IMPDP) 效能更強、支援並行、可壓縮/加密。
8. 數據字典常用視圖
| 前綴 | 範圍 | 範例 |
|---|
| USER_ | 當前用戶 | USER_TABLES, USER_TAB_COLS |
| ALL_ | 可存取物件 | ALL_TABLES, ALL_VIEWS |
| DBA_ | 全庫（需 DBA 權限） | DBA_USERS, DBA_SEGMENTS |
| V$ | 動態效能視圖 | V$SESSION, V$SQL |
9. 關鍵 SQL 技巧
ROWNUM：結果集產生後分配，從 1 開始，不可直接 WHERE ROWNUM > 5。- 分頁標準寫法（12c 前）：
SELECT * FROM (SELECT a.*, ROWNUM rn FROM (...) a WHERE ROWNUM <= 20) WHERE rn > 10。
TRUNCATE：DDL、不產生 Redo、不可回滾、釋放空間、觸發隱式提交。DELETE：DML、產生 Redo、可回滾、不釋放高水位。START WITH ... CONNECT BY PRIOR：遞迴查詢（組織架構、BOM 展開）。
10. 同義詞
-- 私有
CREATE SYNONYM emp FOR scott.emp;
-- 公有
CREATE PUBLIC SYNONYM emp FOR scott.emp;
