Oracle 常用管理 SQL 快速查詢手冊

整理 DBA 日常巡檢、開發除錯最常用的 4 類查詢：用戶/權限、表/段、表空間建立授權流程，語法標準化，可直接複製執行。

1. 查看所有資料庫用戶

SELECT username FROM all_users ORDER BY username;

2. 查看具 DBA 權限用戶

SELECT username FROM dba_users WHERE 'DBA' IN (SELECT granted_role FROM dba_role_privs WHERE grantee = username);

或更簡潔：

SELECT grantee FROM dba_role_privs WHERE granted_role = 'DBA';

3. 查看當前用戶擁有的表

SELECT table_name FROM user_tables ORDER BY table_name;

4. 新建表空間、用戶、授權標準流程

CREATE TABLESPACE blueskydb
DATAFILE 'D:\oracle\product\10.2.0\oradata\bluesky\blueskydb.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE 5G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


CREATE USER bluesky IDENTIFIED BY bluesky
DEFAULT TABLESPACE blueskydb
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON blueskydb
ACCOUNT UNLOCK;


GRANT CONNECT, RESOURCE TO bluesky;
GRANT CREATE TABLE TO bluesky;
-- 若需完整 DBA 權限（謹慎）
-- GRANT DBA TO bluesky;


ALTER USER bluesky DEFAULT TABLESPACE blueskydb;

5. 進階：一鍵建立應用帳號範本（含角色解耦）

DEFINE tbs_name = APP_DATA
DEFINE user_name = APP_USER
DEFINE user_pwd = "Str0ngP@ssw0rd"

CREATE TABLESPACE &tbs_name DATAFILE '+DATA' SIZE 2G AUTOEXTEND ON NEXT 200M MAXSIZE 20G
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER &user_name IDENTIFIED BY &user_pwd
DEFAULT TABLESPACE &tbs_name TEMPORARY TABLESPACE TEMP
QUOTA UNLIMITED ON &tbs_name ACCOUNT UNLOCK PASSWORD EXPIRE;

CREATE ROLE &user_name._RW;
CREATE ROLE &user_name._RO;

GRANT CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE TO &user_name._RW;
GRANT CREATE SESSION TO &user_name._RO;

GRANT &user_name._RW TO &user_name;
-- GRANT &user_name._RO TO REPORT_USER;

建議將上述流程封裝為參數化 Shell/Python 腳本，納入 CI/CD 流程，實現資料庫變更自動化、可追溯、可回滾。