權限與角色查詢
對象權限
SELECT owner, table_name, grantor, privilege, grantable, hierarchy, 'TABLE' object_type
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 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';
-- 修改限制
ALTER PROFILE "DEFAULT" LIMIT CONNECT_TIME 30; -- 最大連接 30 分鐘
ALTER PROFILE "DEFAULT" LIMIT FAILED_LOGIN_ATTEMPTS 3; -- 最大登錄失敗 3 次
RMAN 核心配置與備份命令
控制文件自動備份
SHOW ALL;
CONFIGURE CONTROLFILE AUTOBACKUP CLEAR; -- 恢復默認
CONFIGURE CONTROLFILE AUTOBACKUP ON; -- 啟用自動備份
保留策略
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
常用備份命令
BACKUP ARCHIVELOG ALL;
BACKUP TABLESPACE users;
BACKUP DATAFILE 3;
BACKUP INCREMENTAL LEVEL 0 DATABASE;
BACKUP INCREMENTAL LEVEL 1 DATABASE;
BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE FORMAT '/oradata/bak/dblevel1.bak';
BACKUP AS COMPRESSED BACKUPSET DATAFILE 4;
歸檔空間滿緊急處理
-- 1. OS 層面刪除部分 .arc 文件釋放空間
RM *.arc
-- 2. RMAN 標記失效並刪除
RMAN> CHANGE ARCHIVELOG ALL VALIDATE;
RMAN> DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
執行 RMAN 腳本
RUN { EXECUTE SCRIPT b_whole_10; }
實用系統命令
df -h -- 磁盤使用率
SHOW PARAMETER log_archive_format -- 歸檔命名格式