Oracle DBA 常用 SQL 腳本庫：建立表空間、用戶、授權與權限查詢一站式參考

1. 建立暫存表空間

CREATE TEMPORARY TABLESPACE bbs_temp
TEMPFILE 'E:\Oracledata\bbs.dbf'
SIZE 32M
AUTOEXTEND ON NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

關鍵：TEMPFILE 必須指定、LOCAL 區管理、UNIFORM SIZE 固定區大小提升效能。

2. 建立永久資料表空間

CREATE TABLESPACE bbs_table
LOGGING
DATAFILE 'E:\Oracledata\bbs_true.dbf'
SIZE 32M
AUTOEXTEND ON NEXT 32M MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

關鍵：DATAFILE 必須指定、AUTOALLOCATE 適合混合負載、ASSM (AUTO) 取代 freelists 減少爭用。

3. 刪除用戶 (含級聯物件)

DROP USER bbs CASCADE;

CASCADE 強制刪除用戶擁有的所有物件 (表、索引、序列等)，否則有物件時會報錯無法刪除。

4. 建立用戶並指定預設/暫存表空間

CREATE USER bbs IDENTIFIED BY password
DEFAULT TABLESPACE bbs_table
TEMPORARY TABLESPACE bbs_temp
QUOTA UNLIMITED ON bbs_table
PASSWORD EXPIRE ACCOUNT UNLOCK;

建議明確指定 QUOTA 避免佔滿 SYSTEM 表空間，PASSWORD EXPIRE 強制首次登入修改密碼。

5. 授予核心系統權限與角色

-- 基礎連線與建表權限 (傳統角色)
GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO bbs;

-- 或授予預定義角色 (含上述權限)
GRANT CONNECT, RESOURCE TO bbs;

-- 進階 DBA 權限 (謹慎授予)
GRANT CREATE ANY TABLE, DROP ANY TABLE, ALTER ANY TABLE, SELECT ANY TABLE TO bbs;

6. 角色管理

-- 建立角色
CREATE ROLE app_read_only;
GRANT SELECT ON schema.table TO app_read_only;

-- 授予角色給用戶
GRANT app_read_only TO bbs;

-- 查詢用戶擁有的角色
SELECT * FROM USER_ROLE_PRIVS;
SELECT * FROM DBA_ROLE_PRIVS WHERE grantee = 'BBS';

7. 權限查詢速查表

8. 實用進階腳本

-- 查詢用戶表空間配額使用情況
SELECT tablespace_name, bytes/1024/1024 used_mb, max_bytes/1024/1024 quota_mb
FROM dba_ts_quotas WHERE username = 'BBS';

-- 查詢角色包含的系統權限
SELECT * FROM ROLE_SYS_PRIVS WHERE role = 'RESOURCE';

-- 查詢角色包含的物件權限
SELECT * FROM ROLE_TAB_PRIVS WHERE role = 'APP_READ_ONLY';