Oracle 表空間與用戶管理完整腳本庫：創建、授權、查詢、清理標準化實踐

表空間管理標準化腳本 (19c/21c/23c 通用)

1. 創建臨時表空間 (本地管理、自動擴展、上限可控)

-- 標準模板：適配 ASM / 文件系統
CREATE TEMPORARY TABLESPACE app_temp
    TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 32G  -- ASM
    -- TEMPFILE '/u01/app/oracle/oradata/ORCL/app_temp01.dbf' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 32G  -- 文件系統
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;  -- 統一區塊 1M，減少臨時段爭用

-- 多文件分散 I/O (大型系統)
ALTER TABLESPACE app_temp ADD TEMPFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;
ALTER TABLESPACE app_temp ADD TEMPFILE '+RECO' SIZE 1G AUTOEXTEND ON NEXT 512M MAXSIZE 32G;

2. 創建永久表空間 (本地管理、ASSM、日志、加密、壓縮)

-- 業務數據表空間 (含表、索引、LOB)
CREATE TABLESPACE app_data
    DATAFILE '+DATA' SIZE 10G AUTOEXTEND ON NEXT 2G MAXSIZE 500G  -- ASM
    -- DATAFILE '/u01/app/oracle/oradata/ORCL/app_data01.dbf' SIZE 10G AUTOEXTEND ON NEXT 2G MAXSIZE 500G
    LOGGING
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE  -- 自動區塊分配 (64K/1M/8M/64M)
    SEGMENT SPACE MANAGEMENT AUTO         -- ASSM (自動段空間管理)，必開
    ENCRYPTION USING 'AES256'             -- TDE 表空間加密 (需 Advanced Security 選項)
    DEFAULT COMPRESS FOR OLTP;            -- 默認 OLTP 壓縮 (需 Advanced Compression 選項)

-- 索引獨立表空間 (可選，便於備份/恢復/監控分離)
CREATE TABLESPACE app_index
    DATAFILE '+DATA' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 200G
    LOGGING
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO
    DEFAULT COMPRESS ADVANCED LOW;        -- 索引默認高級壓縮低級別

-- 大對象 (LOB) 專用表空間 (SecureFile 優化)
CREATE TABLESPACE app_lob
    DATAFILE '+DATA' SIZE 5G AUTOEXTEND ON NEXT 1G MAXSIZE 200G
    LOGGING
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO
    ENCRYPTION USING 'AES256';
-- 表創建時指定: LOB (col) STORE AS SECUREFILE (TABLESPACE app_lob ...)

3. 刪除表空間 (含物理文件、級聯對象)

-- 標準刪除：包含內容 + 數據文件 + 級聯約束
DROP TABLESPACE app_data INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

-- 臨時表空間刪除 (無對象，無需 CASCADE)
DROP TEMPORARY TABLESPACE app_temp INCLUDING CONTENTS AND DATAFILES;

用戶創建與授權標準化流程

1. 創建應用用戶 (綁定表空間、配額、Profile、鎖定狀態)

CREATE USER app_user IDENTIFIED BY "Str0ngP@ssw0rd!"
    DEFAULT TABLESPACE app_data
    TEMPORARY TABLESPACE app_temp
    QUOTA UNLIMITED ON app_data
    QUOTA 10G ON app_index
    QUOTA 5G ON app_lob
    PROFILE app_profile          -- 密碼/資源策略
    ACCOUNT UNLOCK
    CONTAINER = CURRENT;         -- 12c+ 非 CDB 忽略，PDB 指定

-- 僅讀/報表用戶 (無創建對象權限)
CREATE USER app_report IDENTIFIED BY "ReportP@ss!"
    DEFAULT TABLESPACE app_data
    TEMPORARY TABLESPACE app_temp
    QUOTA 0 ON app_data           -- 無配額，不可創建對象
    PROFILE app_report_profile
    ACCOUNT UNLOCK;

2. 精細授權腳本 (拒絕 RESOURCE/DBA，構建最小權限集)

-- 基礎會話權限 (所有用戶必需)
GRANT CREATE SESSION TO app_user;

-- 對象操作權限 (按業務模式/表授予)
GRANT SELECT, INSERT, UPDATE, DELETE ON schema_core.customers TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema_core.orders TO app_user;
GRANT SELECT ON schema_core.products TO app_user;
GRANT EXECUTE ON schema_core.pkg_order_mgmt TO app_user;
GRANT EXECUTE ON schema_core.pkg_customer_mgmt TO app_user;

-- 開發/測試用戶額外權限 (受控)
GRANT CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE TRIGGER, CREATE SYNONYM TO app_dev;
GRANT SELECT_CATALOG_ROLE TO app_dev; -- 查詢數據字典

-- 管理類權限 (極度謹慎，僅 DBA/自動化運維)
-- GRANT CREATE ANY TABLE, ALTER ANY TABLE, DROP ANY TABLE TO schema_admin;
-- GRANT ALTER SYSTEM, ALTER SESSION TO ops_monitor;

3. 角色封裝授權 (推薦生產模式)

-- 創建業務角色
CREATE ROLE r_app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema_core.customers TO r_app_rw;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema_core.orders TO r_app_rw;
GRANT EXECUTE ON schema_core.pkg_order_mgmt TO r_app_rw;

-- 用戶授予角色
GRANT r_app_rw TO app_user;
ALTER USER app_user DEFAULT ROLE r_app_rw; -- 登錄自動啟用

權限與對象查詢標準視圖腳本庫

用戶與配額查詢

-- 所有用戶狀態
SELECT username, account_status, default_tablespace, temporary_tablespace, profile, created
FROM dba_users ORDER BY username;

-- 用戶表空間配額
SELECT username, tablespace_name, bytes/1024/1024 used_mb, max_bytes/1024/1024 max_mb
FROM dba_ts_quotas ORDER BY username, tablespace_name;

-- 用戶擁有對象統計
SELECT owner, object_type, COUNT(*) cnt
FROM dba_objects WHERE owner IN ('APP_USER','APP_DEV','SCHEMA_CORE')
GROUP BY owner, object_type ORDER BY owner, object_type;

系統/對象/角色權限全景掃描

-- 用戶直接系統權限
SELECT grantee, privilege, admin_option FROM dba_sys_privs WHERE grantee LIKE 'APP_%' ORDER BY grantee, privilege;

-- 用戶被授予角色 (含 ADMIN/DEFAULT 選項)
SELECT grantee, granted_role, admin_option, default_role FROM dba_role_privs WHERE grantee LIKE 'APP_%' ORDER BY grantee;

-- 用戶/角色對象權限 (含 GRANTABLE/層級)
SELECT grantor, grantee, owner, table_name, privilege, grantable, hierarchy
FROM dba_tab_privs WHERE grantee LIKE 'APP_%' OR grantor LIKE 'APP_%' ORDER BY grantee, owner, table_name;

-- 角色擁有的系統/對象/角色權限
SELECT * FROM role_sys_privs WHERE role LIKE 'R_%';
SELECT * FROM role_tab_privs WHERE role LIKE 'R_%';
SELECT * FROM role_role_privs WHERE role LIKE 'R_%';

-- 當前會話生效權限/角色 (含動態角色)
SELECT * FROM session_privs;
SELECT * FROM session_roles;

清理腳本：刪除表空間下所有用戶對象 (保留用戶/表空間)

-- 針對指定表空間中屬於特定用戶/模式的對象
-- 警告：生產環境極度謹慎，必須先導出 DDL 備份 (DBMS_METADATA)
DECLARE
    v_sql VARCHAR2(4000);
    CURSOR c_obj IS
        SELECT object_type, object_name, owner
        FROM dba_objects
        WHERE owner = UPPER('&schema_name')  -- 目標模式
          AND object_type IN ('TABLE','VIEW','INDEX','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','TYPE','TYPE BODY','SYNONYM','MATERIALIZED VIEW','DATABASE LINK')
          AND status = 'VALID'
        ORDER BY 
            CASE object_type 
                WHEN 'TABLE' THEN 1 
                WHEN 'MATERIALIZED VIEW' THEN 2
                WHEN 'VIEW' THEN 3
                WHEN 'SYNONYM' THEN 4
                WHEN 'DATABASE LINK' THEN 5
                WHEN 'PACKAGE BODY' THEN 6
                WHEN 'PACKAGE' THEN 7
                WHEN 'TRIGGER' THEN 8
                WHEN 'FUNCTION' THEN 9
                WHEN 'PROCEDURE' THEN 10
                WHEN 'TYPE BODY' THEN 11
                WHEN 'TYPE' THEN 12
                WHEN 'SEQUENCE' THEN 13
                WHEN 'INDEX' THEN 14
                ELSE 99 END;
BEGIN
    FOR r IN c_obj LOOP
        BEGIN
            IF r.object_type = 'TABLE' THEN
                v_sql := 'DROP TABLE ' || r.owner || '.' || r.object_name || ' CASCADE CONSTRAINTS PURGE';
            ELSIF r.object_type IN ('VIEW','SEQUENCE','PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','TRIGGER','TYPE','TYPE BODY','SYNONYM','MATERIALIZED VIEW','DATABASE LINK','INDEX') THEN
                v_sql := 'DROP ' || r.object_type || ' ' || r.owner || '.' || r.object_name;
                IF r.object_type = 'TYPE' THEN v_sql := v_sql || ' FORCE'; END IF;
            END IF;
            EXECUTE IMMEDIATE v_sql;
            DBMS_OUTPUT.PUT_LINE('Dropped: ' || v_sql);
        EXCEPTION WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('FAILED: ' || v_sql || ' - ' || SQLERRM);
        END;
    END LOOP;
END;
/

進階：自動化巡檢腳本 (定時任務部署)

-- 表空間使用率告警 (>85%)
SELECT tablespace_name, 
       ROUND(used_space*block_size/1024/1024/1024,2) used_gb,
       ROUND(tablespace_size*block_size/1024/1024/1024,2) total_gb,
       ROUND(used_percent,2) pct_used
FROM dba_tablespace_usage_metrics
WHERE used_percent > 85
ORDER BY used_percent DESC;

-- 用戶配額即將耗盡 (>90%)
SELECT username, tablespace_name,
       ROUND(bytes/1024/1024,2) used_mb,
       ROUND(max_bytes/1024/1024,2) max_mb,
       ROUND(bytes/max_bytes*100,2) pct_used
FROM dba_ts_quotas
WHERE max_bytes > 0 AND bytes/max_bytes > 0.9
ORDER BY pct_used DESC;

-- 失效對象掃描
SELECT owner, object_name, object_type, status
FROM dba_objects WHERE status != 'VALID' AND owner NOT IN ('SYS','SYSTEM','PUBLIC')
ORDER BY owner, object_type;

-- 過期/鎖定用戶
SELECT username, account_status, lock_date, expiry_date
FROM dba_users WHERE account_status != 'OPEN' ORDER BY username;

最佳實踐總結清單

1. **表空間分離**：數據 / 索引 / LOB / 臨時 / Undo 獨立表空間，便於備份恢復、I/O 隔離、存儲分級。
2. **本地管理 + ASSM + 自動擴展 + 上限** 標準化創建，**啟用 TDE 加密 + 壓縮** (有授權前提)。
3. **用戶綁定專用表空間 + 精確 QUOTA**，**系統表空間 0 配額**，禁止 UNLIMITED ON SYSTEM/SYSAUX。
4. **拒絕 RESOURCE/DBA 直接授予應用賬號**，構建 **角色體系 (基礎功能→業務應用→管理審計)**，用戶授予角色，角色嵌套繼承。
5. **Profile 強化密碼/資源策略**，開啟 **統一審計 (12c+)** 記錄所有 DDL/權限變更/登錄失敗。
6. **標準化查詢腳本庫** 納入版本控，巡檢/故障排查/審計合規一鍵執行。
7. **清理操作必備份 DDL (DBMS_METADATA)**，按依賴順序刪除 (表→MV→視圖→同義詞→DB Link→包體→包→觸發器→函數/過程→類型體→類型→序列→索引)。