-- 所有資料庫使用者
SELECT username FROM all_users;
-- DBA 權限使用者
SELECT username FROM dba_users WHERE username IN (
SELECT grantee FROM dba_sys_privs WHERE privilege = 'DBA'
);
-- 當前使用者擁有的表
SELECT table_name FROM user_tables;-- 1. 建立表空間 (含資料檔、大小、區段管理)
CREATE TABLESPACE blueskydb
DATAFILE 'D:\Oracle\oradata\bluesky\blueskydb.dbf'
SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
-- 2. 建立使用者
CREATE USER bluesky IDENTIFIED BY bluesky
DEFAULT TABLESPACE blueskydb
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON blueskydb;
-- 3. 授予核心權限
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE,
CREATE VIEW, CREATE PROCEDURE, CREATE TRIGGER
TO bluesky;
-- 4. 視需求授予資源角色 (12c 建議精細授權)
GRANT RESOURCE TO bluesky; -- 含 CREATE TABLE 等
-- 5. 可選：恢復目錄擁有者權限
GRANT RECOVERY_CATALOG_OWNER TO bluesky;| 操作 | SQL |
|---|---|
| 檢視表空間使用率 | SELECT tablespace_name, ROUND(SUM(bytes)/1024/1024,2) total_mb, ROUND(SUM(bytes)-NVL(SUM(free_bytes),0))/1024/1024,2) used_mb, ROUND(NVL(SUM(free_bytes),0)/1024/1024,2) free_mb FROM dba_data_files LEFT JOIN (SELECT tablespace_name, SUM(bytes) free_bytes FROM dba_free_space GROUP BY tablespace_name) USING (tablespace_name) GROUP BY tablespace_name; |
| 新增資料檔 | ALTER TABLESPACE tbs ADD DATAFILE 'path' SIZE 100M AUTOEXTEND ON; |
| 調整檔案大小 | ALTER DATABASE DATAFILE 'path' RESIZE 500M; |
| 設定自動擴展 | ALTER DATABASE DATAFILE 'path' AUTOEXTEND ON NEXT 50M MAXSIZE 2G; |
| 離線/上線表空間 | ALTER TABLESPACE tbs OFFLINE [NORMAL|TEMPORARY|IMMEDIATE]; ALTER TABLESPACE tbs ONLINE; |
| 唯讀/讀寫切換 | ALTER TABLESPACE tbs READ ONLY; ALTER TABLESPACE tbs READ WRITE; |
| 刪除表空間 (含資料檔) | DROP TABLESPACE tbs INCLUDING CONTENTS AND DATAFILES; |
-- 修改使用者表空間配額
ALTER USER bluesky QUOTA 100M ON blueskydb;
ALTER USER bluesky QUOTA UNLIMITED ON blueskydb;
-- 鎖定/解鎖帳號
ALTER USER bluesky ACCOUNT LOCK;
ALTER USER bluesky ACCOUNT UNLOCK;
-- 修改密碼
ALTER USER bluesky IDENTIFIED BY new_password;
