本文彙整 Oracle DBA 日常運維與開發最常用的指令、語法範本與最佳實踐，涵蓋資料庫建立、用戶權限、核心 SQL、PL/SQL 程式結構、匯出入、表空間管理、存儲過程/函數/包開發模板，適合列印備查或離線速查。
CREATE TABLE dept(deptno NUMBER(4), dname VARCHAR2(14), loc VARCHAR2(13)); CREATE TABLE emp(empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(4)); CREATE TABLE salgrade(grade NUMBER, losal NUMBER, hisal NUMBER); -- INSERT 資料略...
| 操作 | 指令範例 |
|---|---|
| 建立用戶 | CREATE USER db_user IDENTIFIED BY pwd; |
| 刪除用戶(含物件) | DROP USER db_user CASCADE; |
| 修改密碼(自己) | PASSWORD; |
| 修改密碼(DBA) | ALTER USER db_user IDENTIFIED BY new_pwd; |
| 授予 DBA 權限 | GRANT DBA TO db_user; |
| 授予連線/資源角色 | GRANT CONNECT, RESOURCE TO db_user; |
| 授予建立 Session | GRANT CREATE SESSION TO db_user; |
| 查看所有用戶 | SELECT username FROM dba_users; |
| 查看系統權限 | SELECT * FROM dba_sys_privs WHERE grantee='ROLE_NAME'; |
| 查看物件權限 | SELECT * FROM dba_tab_privs WHERE grantee='USER_NAME'; |
| 查看角色權限 | SELECT * FROM dba_role_privs WHERE grantee='USER_NAME'; |
| 所有系統權限清單 | SELECT * FROM system_privilege_map ORDER BY name; |
| 所有角色清單 | SELECT * FROM dba_roles; |
-- 表操作
DROP TABLE t;
RENAME t TO t1;
ALTER TABLE t RENAME COLUMN c1 TO c2;
ALTER TABLE t ADD (c3 DATE);
ALTER TABLE t MODIFY (c3 VARCHAR2(50));
ALTER TABLE t DROP COLUMN c3;
-- 約束
ALTER TABLE t MODIFY c1 NOT NULL;
ALTER TABLE t ADD CONSTRAINT uk_id UNIQUE (id);
ALTER TABLE t ADD CONSTRAINT chk_city CHECK (city IN ('北京','上海'));
ALTER TABLE t DROP CONSTRAINT uk_id;
ALTER TABLE t DROP PRIMARY KEY CASCADE;
-- 交易控制
SAVEPOINT a;
ROLLBACK TO a;
ROLLBACK;
COMMIT; -- 提交會刪除所有存檔點並釋放鎖
-- 分頁查詢 (經典寫法)
SELECT * FROM (
SELECT a.*, ROWNUM rn FROM (SELECT * FROM emp) a WHERE ROWNUM <= 10
) WHERE rn >= 6;
-- 集合運算
UNION -- 合併去重
UNION ALL -- 合併不去重
INTERSECT -- 交集
MINUS -- 差集 (前減後)
-- 日期格式化
TO_DATE('1998-09-09 12:10:10','YYYY-MM-DD HH24:MI:SS')
SYSDATE
TO_CHAR(hiredate, 'YYYY-MM-DD HH24:MI:SS')
TO_CHAR(sal, 'L99,999.99') -- 本地貨幣格式
-- 日期運算
ADD_MONTHS(hiredate, 6) -- 加 6 個月
LAST_DAY(hiredate) -- 本月最後一天
TRUNC(SYSDATE - hiredate) -- 入職天數
MONTHS_BETWEEN(SYSDATE, hiredate) -- 入職月數
-- 字串處理
UPPER/LOWER/INITCAP
LENGTH, SUBSTR(str, pos, len)
INSTR(str, substr, start_pos)
LTRIM/RTRIM/TRIM
REPLACE(str, from, to)
-- 導出表結構不含資料 exp userid=user/pwd@sid tables=emp file=emp.dmp rows=n -- 導出表 (直接路徑加速) exp userid=user/pwd@sid tables=emp file=emp.dmp direct=y -- 導出 Schema exp userid=user/pwd@sid owner=user file=schema.dmp -- 導出全庫 exp userid=sys/pwd@sid full=y inctype=complete file=full.dmp -- 導入僅結構 imp userid=user/pwd@sid tables=emp file=emp.dmp rows=n -- 導入資料(表已存在忽略錯誤) imp userid=user/pwd@sid tables=emp file=emp.dmp ignore=y -- 導入到其他用戶 imp userid=sys/pwd@sid tables=emp file=emp.dmp touser=other_user
-- 建立表空間 CREATE TABLESPACE data01 DATAFILE 'E:\data01.dbf' SIZE 20M UNIFORM SIZE 128K; -- 增加資料檔 ALTER TABLESPACE data01 ADD DATAFILE 'E:\data02.dbf' SIZE 20M; -- 調整檔案大小 ALTER TABLESPACE data01 DATAFILE 'E:\data01.dbf' RESIZE 30M; -- 自動擴展 ALTER TABLESPACE data01 DATAFILE 'E:\data01.dbf' AUTOEXTEND ON NEXT 10M MAXSIZE 500M; -- 磁碟損壞時邏輯移動資料檔 ALTER TABLESPACE data01 OFFLINE; -- OS 層面移動檔案 ALTER TABLESPACE data01 RENAME DATAFILE 'E:\data01.dbf' TO 'D:\data01.dbf'; ALTER TABLESPACE data01 ONLINE; -- 使用表空間建表 CREATE TABLE t(...) TABLESPACE data01; -- 查詢表空間資訊 SELECT tablespace_name, table_name FROM user_tables WHERE table_name='EMP'; SELECT * FROM all_tables WHERE tablespace_name='DATA01'; -- 刪除表空間(含內容與檔案) DROP TABLESPACE data01 INCLUDING CONTENTS AND DATAFILES;
SET SERVEROUTPUT ON;
DECLARE
v_ename VARCHAR2(20);
v_sal NUMBER(7,2);
BEGIN
SELECT ename, sal INTO v_ename, v_sal FROM emp WHERE empno = &no;
DBMS_OUTPUT.PUT_LINE('員工:'||v_ename||' 薪資:'||v_sal);
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('查無此人');
WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('錯誤:'||SQLERRM);
END;
/
CREATE OR REPLACE PROCEDURE proc_name(
p_in_param IN VARCHAR2,
p_out_param OUT NUMBER
) AS
v_err_msg VARCHAR2(4000);
CURSOR c_data IS SELECT ...;
BEGIN
-- 邏輯處理
FOR rec IN c_data LOOP
UPDATE ...;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
v_err_msg := 'ERROR IN proc_name:' || SUBSTR(SQLERRM,1,500);
ROLLBACK;
INSERT INTO log_table(proc_name, error_info, op_date) VALUES ('proc_name', v_err_msg, SYSDATE);
COMMIT;
RETURN;
END;
/
-- 呼叫
EXEC proc_name('value', :out_var);
-- 或
CALL proc_name('value', :out_var);
CREATE OR REPLACE FUNCTION fn_annual_income(p_ename VARCHAR2) RETURN NUMBER AS
v_income NUMBER;
BEGIN
SELECT sal*12 + NVL(comm,0)*12 INTO v_income FROM emp WHERE ename = p_ename;
RETURN v_income;
END;
/
-- 呼叫
VAR v_result NUMBER;
CALL fn_annual_income('SCOTT') INTO :v_result;
PRINT v_result;
-- 規範
CREATE OR REPLACE PACKAGE pkg_emp AS
PROCEDURE update_sal(p_ename VARCHAR2, p_sal NUMBER);
FUNCTION annual_income(p_ename VARCHAR2) RETURN NUMBER;
END pkg_emp;
/
-- 主體
CREATE OR REPLACE PACKAGE BODY pkg_emp AS
PROCEDURE update_sal(p_ename VARCHAR2, p_sal NUMBER) AS
BEGIN UPDATE emp SET sal = p_sal WHERE ename = p_ename; END;
FUNCTION annual_income(p_ename VARCHAR2) RETURN NUMBER AS
v_income NUMBER;
BEGIN
SELECT sal*12+NVL(comm,0)*12 INTO v_income FROM emp WHERE ename = p_ename;
RETURN v_income;
END;
END pkg_emp;
/
-- 呼叫
EXEC pkg_emp.update_sal('SCOTT', 3000);
| 技巧 | 語法要點 |
|---|---|
| 記錄型別 | TYPE rec_t IS RECORD (name VARCHAR2(20), sal NUMBER); v_rec rec_t; |
| 關聯陣列 | TYPE arr_t IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER; v_arr arr_t; |
| 游標變數 (SYS_REFCURSOR) | OPEN cur FOR SELECT ...; FETCH cur INTO ...; CLOSE cur; |
| 動態 SQL | EXECUTE IMMEDIATE 'DROP TABLE ' || v_tbl; |
| 集合管線函數 | CREATE FUNCTION ... RETURN arr_t PIPELINED IS ... PIPE ROW(...); |
-- RMAN 完整備份
RUN {
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
BACKUP DATABASE PLUS ARCHIVELOG;
RELEASE CHANNEL c1;
}
-- RMAN 還原至時間點
RUN {
SET UNTIL TIME "TO_DATE('2024-01-15 10:00:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS;
}
-- Data Pump 邏輯備份 (參見 Data Pump 專文)
這份手冊涵蓋 DBA 從安裝、建庫、權限、日常 SQL、PL/SQL 開發、表空間、備份恢復的核心知識點。建議將常用段落存為程式碼片段，配合 IDE (SQL Developer, VS Code, PL/SQL Developer) 片段功能，實現秒級調用。
