Oracle 建立資料庫、表空間、用戶與資料表完整實戰教學

架構層級認知

Oracle 邏輯結構自上而下：資料庫 → 表空間 → 用戶 → 表/索引 → 段/區/塊。物理層面對應數據檔、控制檔、重做日誌檔。

一、建立表空間（儲存容器）

-- 一般表空間（本地管理、自動區塊分配、自動段空間管理）
CREATE TABLESPACE app_data
DATAFILE '/u01/oradata/orcl/app_data01.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 10G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

CREATE TABLESPACE app_idx
DATAFILE '/u01/oradata/orcl/app_idx01.dbf' SIZE 200M AUTOEXTEND ON NEXT 50M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

二、建立用戶並授權

CREATE USER dev_user IDENTIFIED BY "DevPass#2024"
DEFAULT TABLESPACE app_data
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON app_data
QUOTA 1G ON app_idx
PROFILE default
ACCOUNT UNLOCK;

-- 最小權限集（開發環境）
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW,
      CREATE PROCEDURE, CREATE TRIGGER, CREATE SYNONYM TO dev_user;

-- 生產環境嚴禁 GRANT DBA / RESOURCE (含 UNLIMITED TABLESPACE)
-- 按需授予系統權限：CREATE ANY TABLE, DROP ANY TABLE 等

三、連線用戶建立資料表

CONN dev_user/DevPass#2024@orcl

CREATE TABLE employees (
    emp_id       NUMBER(6) PRIMARY KEY,
    emp_name     VARCHAR2(50) NOT NULL,
    email        VARCHAR2(100) UNIQUE,
    hire_date    DATE DEFAULT SYSDATE NOT NULL,
    salary       NUMBER(8,2) CHECK (salary > 0),
    dept_id      NUMBER(4),
    manager_id   NUMBER(6),
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    CONSTRAINT fk_emp_mgr  FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
)
TABLESPACE app_data
STORAGE (INITIAL 64K NEXT 1M PCTINCREASE 0)
PCTFREE 10 PCTUSED 40
ENABLE ROW MOVEMENT;

-- 索引建立於索引表空間
CREATE INDEX idx_emp_dept ON employees(dept_id) TABLESPACE app_idx;
CREATE INDEX idx_emp_name ON employees(emp_name) TABLESPACE app_idx;

四、用戶維護常用操作

五、權限查詢速查

-- 系統權限
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'DEV_USER';
-- 角色權限
SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'DEV_USER';
-- 對象權限
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'DEV_USER';
-- 用戶擁有對象
SELECT OBJECT_TYPE, COUNT(*) FROM DBA_OBJECTS WHERE OWNER = 'DEV_USER' GROUP BY OBJECT_TYPE;

六、密碼文件與 SYSDBA（遠程管理）

# 建立密碼文件
orapwd file=$ORACLE_HOME/dbs/orapworcl password=SysPass#2024 entries=10

# 授予 SYSDBA（自動寫入密碼文件）
GRANT SYSDBA TO dba_admin;

# 遠程以 SYSDBA 登入
sqlplus dba_admin/SysPass#2024@orcl AS SYSDBA

七、初始化參數檔（SPFILE/PFILE）

• SPFILE：二進位，實例啟動讀取，ALTER SYSTEM SET ... SCOPE=SPFILE 持久化。
• PFILE：文本，啟動時可指定 STARTUP PFILE=/path/init.ora。
• 轉換：CREATE SPFILE FROM PFILE / CREATE PFILE FROM SPFILE。

總結

標準化流程：規劃表空間 → 建用戶配額 → 最小授權 → 連線建表 → 指定儲存屬性 → 索引分離 → 權限審計。將上述 SQL 封裝為參數化部署腳本，納入版本控管，實現基礎設施即代碼。