常用 SQL*Plus 指令速查
| 指令 | 說明 |
|---|
DESC table_name | 顯示資料表結構 |
SET LINESIZE 200 | 設定顯示行寬 |
SET PAGESIZE 50 | 設定每頁行數 |
COL col_name FORMAT A20 | 設定欄位顯示格式 |
BREAK ON dept_no | 分組斷行顯示 |
TTITLE CENTER 'Report' | 設定報表標題 |
SPOOL file.txt | 將輸出導向檔案 |
EDIT | 啟動編輯器修改緩衝區 SQL |
@script.sql | 執行腳本檔 |
資料庫啟動/關閉模式
STARTUP NOMOUNT -- 僅啟動例項 (建立控制檔時使用)
STARTUP MOUNT -- 掛載資料庫 (維護、歸檔模式切換)
STARTUP -- 正常開啟 (MOUNT -> OPEN)
SHUTDOWN NORMAL -- 等待所有使用者斷線
SHUTDOWN IMMEDIATE -- 回滾未提交交易、斷開連線、關閉 (最常用)
SHUTDOWN ABORT -- 直接中斷，下次啟動需執行例項復原
使用者與權限管理
-- 建立使用者
CREATE USER app_user IDENTIFIED BY password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;
-- 解鎖帳號
ALTER USER scott ACCOUNT UNLOCK;
-- 角色授權
GRANT CONNECT, RESOURCE TO app_user;
GRANT CREATE VIEW, CREATE SEQUENCE TO app_user;
-- 物件權限
GRANT SELECT, INSERT ON scott.emp TO app_user WITH GRANT OPTION;
表空間與儲存物件
-- 永久表空間
CREATE TABLESPACE app_data
DATAFILE '/u01/app/oracle/oradata/app_data01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
-- 臨時表空間
CREATE TEMPORARY TABLESPACE app_temp
TEMPFILE '/u01/app/oracle/oradata/app_temp01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE 5G;
-- 指定表空間建表
CREATE TABLE orders (
order_id NUMBER PRIMARY KEY USING INDEX TABLESPACE app_idx,
cust_id NUMBER,
amt NUMBER(10,2)
) TABLESPACE app_data;
序列與觸發器實現自動編號
CREATE SEQUENCE seq_order_id
START WITH 1 INCREMENT BY 1
NOCACHE NOCYCLE;
CREATE OR REPLACE TRIGGER trg_order_bir
BEFORE INSERT ON orders
FOR EACH ROW
WHEN (NEW.order_id IS NULL)
BEGIN
SELECT seq_order_id.NEXTVAL INTO :NEW.order_id FROM dual;
END;
/
-- 測試
INSERT INTO orders (cust_id, amt) VALUES (1001, 123.45);
視圖、同義詞、資料庫連結
-- 視圖
CREATE OR REPLACE VIEW v_high_value_orders AS
SELECT * FROM orders WHERE amt > 1000 WITH CHECK OPTION;
-- 同義詞
CREATE PUBLIC SYNONYM emp FOR scott.emp;
-- DB Link
CREATE DATABASE LINK remote_db
CONNECT TO remote_user IDENTIFIED BY pwd
USING 'tns_alias';
SELECT * FROM emp@remote_db;
PL/SQL 區塊結構與常用功能
DECLARE
v_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO v_cnt FROM orders;
IF v_cnt = 0 THEN
DBMS_OUTPUT.PUT_LINE('No orders');
ELSE
DBMS_OUTPUT.PUT_LINE('Orders: ' || v_cnt);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Query returned no rows');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
-- 遊標
CURSOR c_orders IS SELECT * FROM orders WHERE amt > 500;
FOR r IN c_orders LOOP
DBMS_OUTPUT.PUT_LINE(r.order_id || ' ' || r.amt);
END LOOP;
備份/還原與匯出匯入 (exp/imp) 關鍵參數
FULL=Y：完整資料庫匯出OWNER=schema：指定結構匯出TABLES=tab1,tab2：指定資料表匯出FILE=exp.dmp：輸出檔案COMPRESS=N：大表避免 extent 合併導致匯入失敗DIRECT=Y：直接路徑匯出 (效能較佳)- 匯入時
IGNORE=Y 忽略物件已存在錯誤，FROMUSER/TOUSER 重映射結構。
效能調校切入點
- SQL 語句優化：執行計畫、索引、綁定變數、避免全表掃描。
- 記憶體參數：
SGA_TARGET, PGA_AGGREGATE_TARGET, DB_CACHE_SIZE, SHARED_POOL_SIZE。
- I/O 分散：資料檔、重做日誌、控制檔、臨時表空間置於不同磁碟/ASM 磁碟群。
- 鎖/爭用：監控
V$SESSION_WAIT, V$LOCK，調整 INITRANS/MAXTRANS、分區表。
- 統計資訊：定期
DBMS_STATS.GATHER_SCHEMA_STATS，啟用自動統計收集。
