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

Oracle 用戶與表空間全生命週期管理：從建立到刪除的標準化操作手冊

編輯：Oracle 資料庫相關知識

核心概念速覽

概念	說明
資料庫	物理檔案集合（控制檔、日誌檔、數據檔），實例掛載後對外服務
表空間	邏輯儲存容器，由一或多個數據檔組成，對應業務/功能分離
用戶/Schema	擁有對象的命名空間，建立用戶即建立同名 Schema
數據檔	作業系統層面的實體檔案，表空間的物理載體

標準化建立流程

1. 建立表空間（分離數據與索引）

-- 數據表空間
CREATE TABLESPACE data_ts
DATAFILE '/u01/oradata/orcl/data_ts01.dbf' SIZE 2G AUTOEXTEND ON NEXT 100M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

-- 索引表空間
CREATE TABLESPACE idx_ts
DATAFILE '/u01/oradata/orcl/idx_ts01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

-- 臨時表空間
CREATE TEMPORARY TABLESPACE temp_ts
TEMPFILE '/u01/oradata/orcl/temp_ts01.dbf' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

2. 建立用戶並指定預設/臨時表空間

CREATE USER app_user IDENTIFIED BY "StrongPass#2024"
DEFAULT TABLESPACE data_ts
TEMPORARY TABLESPACE temp_ts
PROFILE default_profile
ACCOUNT UNLOCK;

3. 授權最小權限原則

-- 基礎連接與建表權限
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE PROCEDURE TO app_user;

-- 表空間配額（避免無限制佔用）
ALTER USER app_user QUOTA UNLIMITED ON data_ts;
ALTER USER app_user QUOTA 500M ON idx_ts;

-- 角色授權（生產環境避免 DBA）
-- GRANT resource TO app_user;  -- 12c+ RESOURCE 不含 UNLIMITED TABLESPACE
-- GRANT connect TO app_user;   -- 僅 CREATE SESSION

常用維護操作

操作	SQL 範例
修改密碼	ALTER USER app_user IDENTIFIED BY "NewPass#2024";
解鎖帳號	ALTER USER app_user ACCOUNT UNLOCK;
鎖定帳號	ALTER USER app_user ACCOUNT LOCK;
修改預設表空間	ALTER USER app_user DEFAULT TABLESPACE new_data_ts;
修改臨時表空間	ALTER USER app_user TEMPORARY TABLESPACE new_temp_ts;
刪除用戶（含對象）	DROP USER app_user CASCADE;
查看用戶權限	SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='APP_USER';
查看用戶角色	SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='APP_USER';
查看用戶對象	SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OWNER='APP_USER';
查看表空間使用率	SELECT * FROM DBA_TABLESPACE_USAGE_METRICS;

角色管理最佳實踐

-- 建立應用角色
CREATE ROLE app_read_only;
GRANT SELECT ON schema1.table1 TO app_read_only;
GRANT SELECT ON schema1.table2 TO app_read_only;

CREATE ROLE app_read_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON schema1.table1 TO app_read_write;

-- 用戶授予角色
GRANT app_read_write TO app_user;

-- 密碼文件與 SYSDBA（僅極少數運維帳號）
ORAPWD FILE=$ORACLE_HOME/dbs/orapworcl PASSWORD=sys_pass ENTRIES=10
GRANT SYSDBA TO dba_user;  -- 自動加入密碼文件

建表範例：完整儲存子句

CREATE TABLE app_user.orders (
    order_id     NUMBER(12) PRIMARY KEY,
    cust_id      NUMBER(8) NOT NULL,
    order_date   DATE DEFAULT SYSDATE NOT NULL,
    total_amt    NUMBER(12,2),
    status       VARCHAR2(10) CHECK (status IN ('NEW','PAID','SHIPPED','CANCELLED'))
)
TABLESPACE data_ts
STORAGE (INITIAL 64K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
PCTFREE 10 PCTUSED 40
ENABLE ROW MOVEMENT;  -- 分區表維護必需