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

Oracle Schema 對象完全指南：索引、視圖、同義詞、數據庫鏈接、權限管理標準語法

編輯：Oracle 資料庫基礎

1. 索引 (INDEX)

-- 創建
CREATE INDEX idx_emp_dept ON emp(deptno);
CREATE INDEX idx_emp_composite ON emp(deptno, sal);  -- 組合索引
CREATE INDEX idx_emp_func ON emp(UPPER(ename));      -- 函數索引

-- 重建（碎片整理、統計信息更新）
ALTER INDEX idx_emp_dept REBUILD [ONLINE];  -- ONLINE 不鎖表 (EE)

-- 監控使用情況
ALTER INDEX idx_emp_dept MONITORING USAGE;
-- 查詢 v$object_usage 後關閉
ALTER INDEX idx_emp_dept NOMONITORING USAGE;

單表索引建議 ≤ 3 個（特殊大表除外），優先單列，配合 SQL 執行計劃評估組合/函數索引。
字符串索引長度限制：11g+ 3998 字節，8i/9i 758/1578 字節（視塊大小）。

2. 視圖 (VIEW)

-- 創建
CREATE VIEW v_emp_dept AS
SELECT e.empno, e.ename, e.sal, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- 編譯驗證（依賴對象變更後）
ALTER VIEW v_emp_dept COMPILE;

-- WITH CHECK OPTION：防止 DML 違反視圖定義條件
CREATE VIEW v_high_sal AS
SELECT * FROM emp WHERE sal > 5000
WITH CHECK OPTION CONSTRAINT v_high_sal_chk;

視圖 = 存儲的查詢，不佔用數據空間，簡化複雜連接、實現行/列級安全。
可更新視圖需滿足：單表、無聚合/分組/集合運算、無 ROWNUM/DISTINCT、所有 NOT NULL 列包含在內。

3. 同義詞 (SYNONYM)

-- 私有同義詞（當前用戶）
CREATE SYNONYM emp FOR scott.emp;

-- 公共同義詞（全庫可見，需 DBA 權限）
CREATE PUBLIC SYNONYM emp FOR scott.emp;

-- 跨數據庫鏈接
CREATE SYNONYM remote_emp FOR emp@dblink_prod;

解耦硬編碼 Schema 名，遷移/測試只需修改同義詞指向。

4. 數據庫鏈接 (DATABASE LINK)

-- 創建
CREATE DATABASE LINK dblink_prod
CONNECT TO app_user IDENTIFIED BY password
USING 'tns_alias_prod';  -- tnsnames.ora 中的別名

-- 查詢遠端表
SELECT * FROM emp@dblink_prod WHERE deptno = 10;

-- 全局名稱強制模式 (global_names=true)
-- 鏈接名必須 = 遠端數據庫全局名
SELECT * FROM global_name;  -- 查看當前庫全局名

USING 字符串可為 TNS 別名、Easy Connect 字符串（host:port/service）。
固定用戶鏈接（指定用戶/密碼）vs 當前用戶鏈接（CONNECT TO CURRENT_USER，需雙向信任）。

5. 權限管理 (DCL)

5.1 系統權限角色

-- 三大標準角色
GRANT CONNECT, RESOURCE TO app_user;  -- 基礎連接 + 創建對象權限
GRANT DBA TO admin_user;              -- 完全管理權限（慎授）

-- 細粒度系統權限
GRANT CREATE TABLE, CREATE VIEW, CREATE PROCEDURE TO dev_user;
GRANT UNLIMITED TABLESPACE TO data_user;

5.2 對象權限

-- 五大核心對象權限
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON emp TO hr_user;

-- 全部權限
GRANT ALL ON emp TO hr_user;

-- 授予角色（角色可包含多個權限）
CREATE ROLE r_emp_rw;
GRANT SELECT, INSERT, UPDATE ON emp TO r_emp_rw;
GRANT r_emp_rw TO user1, user2;

5.3 回收權限

REVOKE SELECT ON emp FROM hr_user;
REVOKE CONNECT, RESOURCE FROM app_user;
REVOKE SELECT, INSERT, UPDATE ON emp FROM user1, user2;

6. 實用業務查詢示例（原文保留）

-- 查詢錯誤代碼 63 詳情
SELECT orgaddr, destaddr FROM sm_histable0116 WHERE error_code = '63';

-- 查詢用戶最大提交/下發
SELECT msisdn, tcos, ocos FROM ms_usertable;

-- 錯誤代碼統計
SELECT error_code, COUNT(*) FROM sm_histable0513 GROUP BY error_code ORDER BY error_code;

-- 話單統計
SELECT SUM(successcount) FROM tbl_middlemt0411 WHERE servicetype2 = 111;
SELECT SUM(successcount), servicetype FROM tbl_middlemt0411 GROUP BY servicetype;