Oracle 維護必備：權限查詢、概要文件、RMAN 參數與歸檔清理實用腳本

權限與角色快速偵測

-- 對象權限
SELECT OWNER, TABLE_NAME, GRANTOR, PRIVILEGE, GRANTABLE, HIERARCHY
FROM DBA_TAB_PRIVS WHERE GRANTEE = 'HR' ORDER BY OWNER, TABLE_NAME;

-- 角色授予
SELECT GRANTED_ROLE, ADMIN_OPTION, DEFAULT_ROLE
FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'HR' ORDER BY GRANTED_ROLE;

-- 系統權限
SELECT PRIVILEGE, ADMIN_OPTION
FROM DBA_SYS_PRIVS WHERE GRANTEE = 'HR' ORDER BY PRIVILEGE;

-- 表空間配額
SELECT * FROM DBA_TS_QUOTAS WHERE USERNAME = 'SYSMAN' ORDER BY TABLESPACE_NAME;

-- 角色擁有的系統權限
SELECT * FROM ROLE_SYS_PRIVS WHERE ROLE = 'RESOURCE';

-- 所有含 SELECT 的系統權限名
SELECT DISTINCT NAME FROM SYSTEM_PRIVILEGE_MAP WHERE NAME LIKE '%SELECT%';

概要文件管理

-- 查用戶概要文件
SELECT USERNAME, PROFILE FROM DBA_USERS WHERE USERNAME = 'SCOTT';

-- 查概要文件參數
SELECT RESOURCE_NAME, LIMIT FROM DBA_PROFILES WHERE PROFILE = 'DEFAULT';

-- 修改：最長連線 30 分鐘、登入失敗 3 次鎖定
ALTER PROFILE "DEFAULT" LIMIT CONNECT_TIME 30;
ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS 3;

RMAN 核心配置與備份指令

RMAN TARGET /
SHOW ALL;  -- 顯示所有配置

-- 自動備份控制檔
CONFIGURE CONTROLFILE AUTOBACKUP ON;
-- 保留策略：30 天恢復窗口
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

-- 常用備份
BACKUP ARCHIVELOG ALL;
BACKUP TABLESPACE USERS;
BACKUP DATAFILE 3;
BACKUP INCREMENTAL LEVEL 0 DATABASE;       -- L0 完整
BACKUP INCREMENTAL LEVEL 1 DATABASE;       -- L1 差異
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE FORMAT '/oradata/bak/dblevel1.bak'; -- L1 累計
BACKUP AS COMPRESSED BACKUPSET DATAFILE 4; -- 壓縮備份

歸檔目的地滿載急救流程

-- 1. OS 層刪除部分舊歸檔（謹慎！確認已備份）
RM *.ARC

-- 2. RMAN 同步控制檔與 Repository
RMAN TARGET /
CHANGE ARCHIVELOG ALL VALIDATE;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

執行儲存腳本

RUN { EXECUTE SCRIPT B_WHOLE_10; }

磁碟空間快速檢查

DF -H

歸檔格式確認

SHOW PARAMETER LOG_ARCHIVE_FORMAT

將上述常用查詢與指令整理為個人「DBA 口袋書」，關鍵時刻秒級調用，避免現場翻文檔。