达梦数据库日常运维500条
一、实例与数据库基础（1–20）
- 查看实例身份、版本、启动时间与 DSC 角色：
SELECT NAME, INSTANCE_NAME, INSTANCE_NUMBER, HOST_NAME, DB_VERSION, START_TIME, STATUS$, MODE$, DSC_ROLE FROM V$INSTANCE; - 查看数据库角色、状态、归档模式与容量：
SELECT NAME, ROLE$, STATUS$, ARCH_MODE, DSC_NODES, TOTAL_SIZE, MAX_SIZE, CREATE_TIME, LAST_STARTUP_TIME FROM V$DATABASE; - 查看实例名、主机、版本与编译信息：
SELECT INSTANCE_NAME, HOST_NAME, DB_VERSION, BUILD_VERSION, BUILD_TIME FROM V$INSTANCE; - 计算实例已运行天数：
SELECT NAME, START_TIME, ROUND((SYSDATE - START_TIME), 2) AS RUN_DAYS FROM V$INSTANCE; - 查看打开次数、启动次数与最后检查点：
SELECT NAME, OPEN_COUNT, STARTUP_COUNT, LAST_CKPT_TIME FROM V$DATABASE; - 查看主机 CPU 数与负载均值：
SELECT N_CPU, CPU_USER_RATE, CPU_SYSTEM_RATE, CPU_IDLE_RATE, LOAD_ONE_AVERAGE, LOAD_FIVE_AVERAGE, LOAD_FIFTEEN_AVERAGE FROM V$SYSTEMINFO; - 查看主机物理/虚拟内存总量与剩余：
SELECT TOTAL_PHY_SIZE, USED_PHY_SIZE, FREE_PHY_SIZE, TOTAL_VIR_SIZE, FREE_VIR_SIZE FROM V$SYSTEMINFO; - 查看磁盘总量、剩余与挂载设备：
SELECT TOTAL_DISK_SIZE, FREE_DISK_SIZE, DRIVER_NAME, DRIVER_TOTAL_SIZE, DRIVER_FREE_SIZE FROM V$SYSTEMINFO; - 查看实例网络累计收发与实时速率：
SELECT RECEIVE_BYTES_TOTAL, SEND_BYTES_TOTAL, RECEIVE_BYTES_PER_SECOND, SEND_BYTES_PER_SECOND FROM V$SYSTEMINFO; - 查看实例网络收发字节原值：
SELECT RECV_BYTES, SEND_BYTES FROM V$NETINFO; - 核对授权到期日与连接、CPU、核心数上限：
SELECT EXPIRED_DATE, PRODUCT_TYPE, SERVER_TYPE, MAX_CPU_NUM, MAX_CORE_NUM, AUTHORIZED_USER_NUMBER, CONCURRENCY_USER_NUMBER FROM V$LICENSE; - 核对授权空间与表记录数上限：
SELECT EXPIRED_DATE, TOTAL_SPACE_LIMIT, TABLE_RECORD_NUMBER_LIMIT FROM V$LICENSE; - 查看授权客户、项目、硬件与操作系统类型：
SELECT AUTHORIZED_CUSTOMER, PROJECT_NAME, HARDWARE_TYPE, OS_TYPE, DATE_GEN FROM V$LICENSE; - 查看实例打开模式与主备身份历史：
SELECT SYS_MODE, OPEN_TIME, CUR_INST_NAME, PRIMARY_INST_NAME, N_EP FROM SYSOPENHISTORY ORDER BY OPEN_TIME DESC; - 查看最近的重要管理操作与操作人：
SELECT OPUSER, OPTIME, OPERATION FROM V$DANGER_EVENT ORDER BY OPTIME DESC; - 查看参数运行值、系统值、文件值与默认值：
SELECT NAME, VALUE, SYS_VALUE, FILE_VALUE, DEFAULT_VALUE, ISDEFAULT FROM V$PARAMETER; - 对比参数会话值、运行值与文件值：
SELECT PARA_NAME, PARA_VALUE, SESS_VALUE, FILE_VALUE, DEFAULT_VALUE, PARA_TYPE FROM V$DM_INI; - 筛选内存相关参数及其上下限：
SELECT PARA_NAME, PARA_VALUE, DEFAULT_VALUE, MIN_VALUE, MAX_VALUE FROM V$DM_INI WHERE PARA_NAME LIKE '%MEM%'; - 筛选时间类参数当前值：
SELECT PARA_NAME, PARA_VALUE, DEFAULT_VALUE FROM V$DM_INI WHERE PARA_NAME LIKE '%TIME%'; - 筛选归档相关参数当前值：
SELECT PARA_NAME, PARA_VALUE, DEFAULT_VALUE FROM V$DM_INI WHERE PARA_NAME LIKE '%ARCH%';
二、会话管理（21–65）
- 列出全部会话及来源主机、应用：
SELECT SESS_ID, USER_NAME, CLNT_IP, CLNT_HOST, APPNAME, STATE, CREATE_TIME FROM V$SESSIONS ORDER BY CREATE_TIME; - 定位当前活动会话：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE, SQL_ID FROM V$SESSIONS WHERE STATE = 'ACTIVE'; - 查看非空闲会话及其事务与 SQL：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE, TRX_ID, SQL_TEXT FROM V$SESSIONS WHERE STATE <> 'IDLE'; - 按状态统计会话数：
SELECT STATE, COUNT(*) AS SESS_CNT FROM V$SESSIONS GROUP BY STATE ORDER BY SESS_CNT DESC; - 按用户统计会话数：
SELECT USER_NAME, COUNT(*) AS SESS_CNT FROM V$SESSIONS GROUP BY USER_NAME ORDER BY SESS_CNT DESC; - 按客户端 IP 统计连接数：
SELECT CLNT_IP, COUNT(*) AS SESS_CNT FROM V$SESSIONS GROUP BY CLNT_IP ORDER BY SESS_CNT DESC; - 按应用名统计连接数：
SELECT APPNAME, COUNT(*) AS SESS_CNT FROM V$SESSIONS GROUP BY APPNAME ORDER BY SESS_CNT DESC; - 按客户端类型统计连接数：
SELECT CLNT_TYPE, COUNT(*) AS SESS_CNT FROM V$SESSIONS GROUP BY CLNT_TYPE ORDER BY SESS_CNT DESC; - 计算会话已连接小时数：
SELECT SESS_ID, USER_NAME, CLNT_IP, CREATE_TIME, ROUND((SYSDATE - CREATE_TIME) * 24, 2) AS CONN_HOURS FROM V$SESSIONS ORDER BY CREATE_TIME; - 按最后接收时间排查挂起会话：
SELECT SESS_ID, USER_NAME, CLNT_IP, LAST_RECV_TIME FROM V$SESSIONS ORDER BY LAST_RECV_TIME; - 按最后发送时间排查挂起会话：
SELECT SESS_ID, USER_NAME, CLNT_IP, LAST_SEND_TIME FROM V$SESSIONS ORDER BY LAST_SEND_TIME; - 查看会话已用语句槽数量：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE, N_USED_STMT FROM V$SESSIONS ORDER BY N_USED_STMT DESC; - 查看会话临时段占用：
SELECT SESS_ID, USER_NAME, CLNT_IP, TMP_USED_EXTENT_NUM, TMP_USED_TYPE FROM V$SESSIONS WHERE TMP_USED_EXTENT_NUM > 0 ORDER BY TMP_USED_EXTENT_NUM DESC; - 查看会话当前模式与执行语句：
SELECT SESS_ID, USER_NAME, CURR_SCH, STATE, SQL_TEXT FROM V$SESSIONS WHERE CURR_SCH IS NOT NULL; - 查看自动提交、隔离级别与连接状态：
SELECT SESS_ID, USER_NAME, AUTO_CMT, ISO_LEVEL, CONNECTED FROM V$SESSIONS; - 查看客户端操作系统与驱动版本：
SELECT SESS_ID, USER_NAME, OSNAME, CLNT_VER, CLNT_TYPE FROM V$SESSIONS; - 查看会话模块、动作与客户端标识：
SELECT SESS_ID, USER_NAME, MODULE, ACTION, CLIENT_INFO FROM V$SESSIONS WHERE MODULE IS NOT NULL; - 查看会话线程号、序列号与事务号：
SELECT SESS_ID, USER_NAME, THRD_ID, SESS_SEQ, TRX_ID FROM V$SESSIONS; - 查看会话正在执行的 SQL_ID：
SELECT SESS_ID, USER_NAME, SQL_ID, SQL_TEXT FROM V$SESSIONS WHERE SQL_ID IS NOT NULL; - 查看存储过程内当前 SQL 片段：
SELECT SESS_ID, USER_NAME, CLNT_IP, CUR_SQLSTR FROM V$SESSIONS WHERE CUR_SQLSTR IS NOT NULL; - 查看存储过程当前方法名与行号：
SELECT SESS_ID, USER_NAME, CUR_MTDNAME, CUR_LINENO FROM V$SESSIONS WHERE CUR_MTDNAME IS NOT NULL; - 查看会话时区设置：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE, TIME_ZONE FROM V$SESSIONS; - 查看会话运行状态标记：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE, RUN_STATUS FROM V$SESSIONS; - 查看会话查询只读属性：
SELECT SESS_ID, USER_NAME, CLNT_IP, RS_FOR_QRY, RDONLY FROM V$SESSIONS; - 查看 DCP 登录与端口类型：
SELECT SESS_ID, USER_NAME, CLNT_IP, DCP_FLAG, PORT_TYPE FROM V$SESSIONS; - 查看会话心跳间隔与超时设置：
SELECT SESS_ID, USER_NAME, CLNT_IP, HEART_BEAT_INTERVAL, HEART_BEAT_TIMEOUT FROM V$SESSIONS; - 查看会话消息收发状态：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE, MSG_STATUS FROM V$SESSIONS; - 查看会话全局 ID 与 MAL 站点：
SELECT SESS_ID, USER_NAME, CLNT_IP, EID, MAL_ID, SRC_SITE FROM V$SESSIONS; - 查看会话约束、自增列与网络检查开关：
SELECT SESS_ID, USER_NAME, CLNT_IP, CHK_CONS, CHK_IDENT, CHK_NET FROM V$SESSIONS; - 查看空值插入、编译与 DDL 自动提交开关：
SELECT SESS_ID, USER_NAME, CLNT_IP, INS_NULL, COMPILE_FLAG, DDL_AUTOCMT FROM V$SESSIONS; - 查看限流占用与语句容量：
SELECT SESS_ID, USER_NAME, CLNT_IP, CONCURRENT_FLAG, N_STMT FROM V$SESSIONS; - 查看应用设置的客户端标识信息：
SELECT SESS_ID, USER_NAME, CLNT_IP, CLIENT_IDENTIFIER, CLIENT_INFO FROM V$SESSIONS; - 查看会话序号与私有内存池地址：
SELECT SESS_ID, USER_NAME, CLNT_IP, SEQ_NO, VPOOLADDR FROM V$SESSIONS; - 查找空闲超过一天的接收挂起会话：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE FROM V$SESSIONS WHERE STATE = 'IDLE' AND LAST_RECV_TIME < (SYSDATE - 1); - 查找空闲超过一天的发送挂起会话：
SELECT SESS_ID, USER_NAME, CLNT_IP, STATE FROM V$SESSIONS WHERE STATE = 'IDLE' AND LAST_SEND_TIME < (SYSDATE - 1); - 按用户与状态交叉统计会话：
SELECT USER_NAME, STATE, COUNT(*) AS CNT FROM V$SESSIONS GROUP BY USER_NAME, STATE ORDER BY USER_NAME, STATE; - 按客户端主机与 IP 统计连接：
SELECT CLNT_HOST, CLNT_IP, COUNT(*) AS CNT FROM V$SESSIONS GROUP BY CLNT_HOST, CLNT_IP ORDER BY CNT DESC; - 按应用与用户统计连接分布：
SELECT APPNAME, USER_NAME, COUNT(*) AS CNT FROM V$SESSIONS GROUP BY APPNAME, USER_NAME ORDER BY CNT DESC; - DSC 各节点按状态统计会话：
SELECT INSTANCE_NAME, STATE, COUNT(*) AS CNT FROM GV$SESSIONS GROUP BY INSTANCE_NAME, STATE ORDER BY INSTANCE_NAME, STATE; - DSC 各节点按用户统计会话：
SELECT INSTANCE_NAME, USER_NAME, COUNT(*) AS CNT FROM GV$SESSIONS GROUP BY INSTANCE_NAME, USER_NAME ORDER BY CNT DESC; - DSC 查看各节点活动会话明细：
SELECT INSTANCE_NAME, SESS_ID, USER_NAME, CLNT_IP, STATE FROM GV$SESSIONS WHERE STATE = 'ACTIVE'; - DSC 各节点按应用统计连接：
SELECT INSTANCE_NAME, APPNAME, COUNT(*) AS CNT FROM GV$SESSIONS GROUP BY INSTANCE_NAME, APPNAME ORDER BY CNT DESC; - DSC 各节点按客户端 IP 统计连接：
SELECT INSTANCE_NAME, CLNT_IP, COUNT(*) AS CNT FROM GV$SESSIONS GROUP BY INSTANCE_NAME, CLNT_IP ORDER BY CNT DESC; - 汇总总会话、活动与空闲会话数：
SELECT COUNT(*) AS TOTAL_SESS, SUM(CASE WHEN STATE = 'ACTIVE' THEN 1 ELSE 0 END) AS ACTIVE_SESS, SUM(CASE WHEN STATE = 'IDLE' THEN 1 ELSE 0 END) AS IDLE_SESS FROM V$SESSIONS; - 找出连接数超过 50 的用户：
SELECT USER_NAME, COUNT(*) AS CNT FROM V$SESSIONS GROUP BY USER_NAME HAVING COUNT(*) > 50 ORDER BY CNT DESC;
三、锁、阻塞与死锁（66–105）
- 查看全部锁记录及被阻塞标志：
SELECT TRX_ID, TABLE_ID, LMODE, BLOCKED, TID, ROW_IDX FROM V$LOCK ORDER BY TRX_ID; - 定位被阻塞的锁记录：
SELECT TRX_ID, TABLE_ID, LMODE, BLOCKED FROM V$LOCK WHERE BLOCKED = 1; - 按锁类型与模式统计锁数量：
SELECT LTYPE, LMODE, COUNT(*) AS CNT FROM V$LOCK GROUP BY LTYPE, LMODE ORDER BY CNT DESC; - 按事务统计持锁数量：
SELECT TRX_ID, COUNT(*) AS LOCK_CNT FROM V$LOCK GROUP BY TRX_ID ORDER BY LOCK_CNT DESC; - 按对象统计锁竞争次数：
SELECT TABLE_ID, COUNT(*) AS CNT FROM V$LOCK GROUP BY TABLE_ID ORDER BY CNT DESC; - 查看排他级及以上锁：
SELECT ADDR, TABLE_ID, TID, LMODE, BLOCKED FROM V$LOCK WHERE LMODE >= 5; - 查看行级锁及行索引：
SELECT TRX_ID, TABLE_ID, ROW_IDX, LMODE FROM V$LOCK WHERE ROW_IDX IS NOT NULL; - 查看锁对应的槽号与对象：
SELECT TRX_ID, TABLE_ID, SPT_NO, LMODE FROM V$LOCK; - 查看锁地址与忽略标志：
SELECT TRX_ID, TABLE_ID, HLCK_EP, IGN_FLAG FROM V$LOCK; - 按等待时长排序事务等待关系：
SELECT ID, WAIT_FOR_ID, WAIT_TIME FROM V$TRXWAIT ORDER BY WAIT_TIME DESC; - 查看事务等待的线程与被等待事务：
SELECT ID, THRD_ID, WAIT_FOR_ID, WAIT_TIME FROM V$TRXWAIT; - 统计当前事务等待总数：
SELECT COUNT(*) AS WAIT_CNT FROM V$TRXWAIT; - 筛选等待超过 10 秒的事务：
SELECT ID, WAIT_FOR_ID, WAIT_TIME FROM V$TRXWAIT WHERE WAIT_TIME > 10; - 查看死锁历史及涉及 SQL：
SELECT HAPPEN_TIME, SESS_ID, TRX_ID, DEADLOCK_CYCLE, SQL_TEXT FROM V$DEADLOCK_HISTORY ORDER BY HAPPEN_TIME DESC; - 查看死锁历史与检测次数：
SELECT HAPPEN_TIME, SESS_ID, TRX_ID, TOO_MANY_CHECKS FROM V$DEADLOCK_HISTORY; - 按会话统计死锁发生次数：
SELECT SESS_ID, COUNT(*) AS CNT FROM V$DEADLOCK_HISTORY GROUP BY SESS_ID ORDER BY CNT DESC; - 查看近一天死锁事件：
SELECT HAPPEN_TIME, TRX_ID, SQL_TEXT FROM V$DEADLOCK_HISTORY WHERE HAPPEN_TIME > (SYSDATE - 1); - 定位持锁较多的事务：
SELECT ID, SESS_ID, LOCK_CNT, WAITING FROM V$TRX WHERE LOCK_CNT > 0 ORDER BY LOCK_CNT DESC; - 定位正在等待且持锁的事务：
SELECT ID, SESS_ID, WAITING, LOCK_CNT FROM V$TRX WHERE WAITING = 1; - 查看长事务 ID 列表：
SELECT TRX_ID FROM V$LONG_TRX; - 查看长事务所在站点：
SELECT EP_SEQNO, TRX_ID FROM V$LONG_TRX; - 统计长事务数量：
SELECT COUNT(*) AS LONG_TRX_CNT FROM V$LONG_TRX; - 查看被阻塞锁涉及的事务与对象：
SELECT L.TRX_ID, L.TABLE_ID, L.LMODE, L.BLOCKED FROM V$LOCK L WHERE L.BLOCKED = 1; - 筛选等待超过 30 秒的事务：
SELECT W.ID, W.WAIT_FOR_ID, W.WAIT_TIME FROM V$TRXWAIT W WHERE W.WAIT_TIME > 30; - 定位持锁超过 10 个的事务：
SELECT T.ID, T.SESS_ID, T.STATUS, T.LOCK_CNT FROM V$TRX T WHERE T.LOCK_CNT > 10; - 回溯被阻塞事务对应的会话：
SELECT S.SESS_ID, S.USER_NAME, S.CLNT_IP, S.STATE FROM V$SESSIONS S WHERE S.TRX_ID IN (SELECT L.TRX_ID FROM V$LOCK L WHERE L.BLOCKED = 1); - 找出持锁超过 20 个的事务：
SELECT L.TRX_ID, COUNT(*) AS CNT FROM V$LOCK L GROUP BY L.TRX_ID HAVING COUNT(*) > 20; - 定位既等待又持锁的事务：
SELECT T.ID, T.SESS_ID, T.WAITING FROM V$TRX T WHERE T.WAITING = 1 AND T.LOCK_CNT > 0; - 查看近七天死锁事件：
SELECT D.HAPPEN_TIME, D.SESS_ID, D.TRX_ID, D.SQL_TEXT FROM V$DEADLOCK_HISTORY D WHERE D.HAPPEN_TIME > (SYSDATE - 7); - 按事务查看最高锁模式：
SELECT TRX_ID, MAX(LMODE) AS MAX_LMODE FROM V$LOCK GROUP BY TRX_ID ORDER BY MAX_LMODE DESC; - 按对象列出被阻塞锁：
SELECT TABLE_ID, TRX_ID, LMODE, BLOCKED FROM V$LOCK WHERE BLOCKED = 1 ORDER BY TABLE_ID; - 统计当前被阻塞锁总数：
SELECT COUNT(*) AS BLOCKED_CNT FROM V$LOCK WHERE BLOCKED = 1; - 按锁类型统计分布：
SELECT LTYPE, COUNT(*) AS CNT FROM V$LOCK GROUP BY LTYPE; - 按锁模式统计分布：
SELECT LMODE, COUNT(*) AS CNT FROM V$LOCK GROUP BY LMODE; - 找出等待最久的事务对：
SELECT W.ID, W.WAIT_FOR_ID FROM V$TRXWAIT W WHERE W.WAIT_TIME = (SELECT MAX(X.WAIT_TIME) FROM V$TRXWAIT X); - 查看长事务对应会话与 SQL：
SELECT S.SESS_ID, S.USER_NAME, S.SQL_TEXT FROM V$SESSIONS S WHERE S.TRX_ID IN (SELECT X.TRX_ID FROM V$LONG_TRX X); - 查看回滚项序号非零的事务：
SELECT T.ID, T.SESS_ID, T.UREC_SEQNO FROM V$TRX T WHERE T.UREC_SEQNO > 0; - 按最老活动事务排序观察快照边界：
SELECT T.ID, T.SESS_ID, T.MIN_ACTIVE_ID FROM V$TRX T ORDER BY T.MIN_ACTIVE_ID; - 查看存在分支或保存点的事务：
SELECT T.ID, T.SESS_ID, T.N_BRANCHES, T.SAVEPOINT_CNT FROM V$TRX T WHERE T.N_BRANCHES > 1; - 查看只读事务：
SELECT T.ID, T.SESS_ID, T.STATUS, T.READ_ONLY FROM V$TRX T WHERE T.READ_ONLY = 1;
四、事务与 UNDO（106–135）
- 查看事务状态、持锁与等待概况：
SELECT ID, STATUS, SESS_ID, LOCK_CNT, WAITING, READ_ONLY FROM V$TRX; - 按更新次数排序事务写操作：
SELECT ID, SESS_ID, UPD_CNT, INS_CNT, DEL_CNT FROM V$TRX ORDER BY UPD_CNT DESC; - 按写页数排序事务日志压力：
SELECT ID, SESS_ID, N_WRITES, START_LSN FROM V$TRX ORDER BY N_WRITES DESC; - 查看事务 XID 与隔离级别：
SELECT ID, SESS_ID, XID, ISOLATION FROM V$TRX; - 查看事务线程号与会话序列：
SELECT ID, SESS_ID, SESS_SEQ, THRD_ID, SRC_SITE FROM V$TRX; - 按状态统计事务数：
SELECT STATUS, COUNT(*) AS CNT FROM V$TRX GROUP BY STATUS; - 按隔离级别统计事务数：
SELECT ISOLATION, COUNT(*) AS CNT FROM V$TRX GROUP BY ISOLATION; - 找出同一会话多事务并发：
SELECT SESS_ID, COUNT(*) AS CNT FROM V$TRX GROUP BY SESS_ID HAVING COUNT(*) > 1; - 按忽略锁次数排序事务：
SELECT ID, SESS_ID, UPD_INS_CNT, IGN_LOCK_CNT FROM V$TRX ORDER BY IGN_LOCK_CNT DESC; - 查看使用保存点的事务：
SELECT ID, SESS_ID, SAVEPOINT_CNT, N_BRANCHES FROM V$TRX WHERE SAVEPOINT_CNT > 0; - 查看正在回滚的事务：
SELECT ID, SESS_ID, ROLLBACK_FLAG, READ_ONLY FROM V$TRX WHERE ROLLBACK_FLAG = 1; - 按下一事务号观察事务推进：
SELECT ID, SESS_ID, NEXTID FROM V$TRX ORDER BY NEXTID; - 按最老活动事务观察 UNDO 保留：
SELECT ID, SESS_ID, MIN_ACTIVE_ID, UREC_SEQNO FROM V$TRX ORDER BY MIN_ACTIVE_ID; - 查看回滚项事务与提交状态：
SELECT NTH, N_COMMIT_TRX, N_PURGING_TRX, N_PAGES FROM V$PSEG_ITEMS; - 查看回滚项页使用与清理情况：
SELECT NTH, N_PAGES, N_USED_PAGES, N_PURGED_PAGES FROM V$PSEG_ITEMS; - 查看回滚项空闲节点与队列：
SELECT NTH, N_FREE_NODE, N_QUEUE_ITEM, N_EXTEND FROM V$PSEG_ITEMS; - 查看回滚项保留时间与最早提交：
SELECT NTH, RESERVE_TIME, MAX_COMMIT_TRX, FIRST_COMMIT_TIME FROM V$PSEG_ITEMS; - 查看回滚项字节与栈使用：
SELECT NTH, N_UREC_BYTES, STACK_SIZE, STACK_SP FROM V$PSEG_ITEMS; - 查看回滚系统页分配与回收：
SELECT ALLOC_PAGES, RECLAIM_PAGES, EXTENT_SIZE, EXTEND_NUM FROM V$PSEG_SYS; - 查看回滚系统对象与哈希表规模：
SELECT OBJ_COUNT, N_ITEM, TAB_ITEMS, TAB_HASH_SIZE FROM V$PSEG_SYS; - 查看回滚系统对象哈希占用：
SELECT OBJ_HASH_SIZE, N_ITEM, OBJ_COUNT FROM V$PSEG_SYS; - 查看启动恢复回滚事务信息：
SELECT TRX_ID, BEGIN_LSN, BEGIN_TIME, N_UPAGES, N_URECS FROM V$RECV_ROLLBACK_TRX; - 按回滚记录数排序恢复事务：
SELECT TRX_ID, N_UPAGES, N_URECS FROM V$RECV_ROLLBACK_TRX ORDER BY N_URECS DESC; - 统计启动恢复回滚事务数：
SELECT COUNT(*) AS CNT FROM V$RECV_ROLLBACK_TRX; - 按已用页排序回滚项：
SELECT NTH, N_USED_PAGES FROM V$PSEG_ITEMS ORDER BY N_USED_PAGES DESC; - 按已提交事务数排序回滚项：
SELECT NTH, N_COMMIT_TRX FROM V$PSEG_ITEMS ORDER BY N_COMMIT_TRX DESC; - 找出正在清理的回滚项：
SELECT NTH, N_PURGING_TRX FROM V$PSEG_ITEMS WHERE N_PURGING_TRX > 0; - 按保留时间排序回滚项：
SELECT NTH, RESERVE_TIME FROM V$PSEG_ITEMS ORDER BY RESERVE_TIME DESC; - 汇总回滚项已用页与总页：
SELECT SUM(N_USED_PAGES) AS TOTAL_USED_PAGES, SUM(N_PAGES) AS TOTAL_PAGES FROM V$PSEG_ITEMS; - 汇总回滚系统分配与回收页：
SELECT SUM(ALLOC_PAGES) AS ALLOC_PAGES, SUM(RECLAIM_PAGES) AS RECLAIM_PAGES FROM V$PSEG_SYS;
五、等待事件与 ASH（136–175）
- 按累计等待时长排序系统等待事件：
SELECT EVENT, WAIT_CLASS, TOTAL_WAITS, TIME_WAITED_MICRO, AVERAGE_WAIT_MICRO FROM V$SYSTEM_EVENT ORDER BY TIME_WAITED_MICRO DESC; - 排除空闲类查看非空闲等待：
SELECT EVENT, WAIT_CLASS, TOTAL_WAITS, TIME_WAITED, TIME_WAITED_MICRO FROM V$SYSTEM_EVENT WHERE WAIT_CLASS <> 'Idle' ORDER BY TIME_WAITED_MICRO DESC; - 按等待类别汇总等待次数与时长：
SELECT WAIT_CLASS, SUM(TOTAL_WAITS) AS CNT, SUM(TIME_WAITED_MICRO) AS MICRO FROM V$SYSTEM_EVENT GROUP BY WAIT_CLASS ORDER BY MICRO DESC; - 按最大单次等待排序事件：
SELECT EVENT, SMAX_TIME, SMIN_TIME, AVERAGE_WAIT_MICRO FROM V$SYSTEM_EVENT ORDER BY SMAX_TIME DESC; - 查看等待事件编号与类别编号：
SELECT EVENT#, EVENT, WAIT_CLASS# FROM V$SYSTEM_EVENT; - 按会话累计等待时长排序：
SELECT SESSADDR, EVENT, WAIT_CLASS, TOTAL_WAITS, TIME_WAITED_MICRO FROM V$SESSION_EVENT WHERE TOTAL_WAITS > 0 ORDER BY TIME_WAITED_MICRO DESC; - 按会话平均等待排序事件：
SELECT SESSION#, EVENT, TOTAL_WAITS, AVERAGE_WAIT_MICRO FROM V$SESSION_EVENT ORDER BY AVERAGE_WAIT_MICRO DESC; - 按会话地址汇总等待时长：
SELECT SESSADDR, SUM(TIME_WAITED_MICRO) AS MICRO FROM V$SESSION_EVENT GROUP BY SESSADDR ORDER BY MICRO DESC; - 按事件统计会话侧发生次数：
SELECT EVENT, COUNT(*) AS CNT FROM V$SESSION_EVENT GROUP BY EVENT ORDER BY CNT DESC; - 查看会话非空闲等待明细：
SELECT SESSADDR, EVENT, WAIT_CLASS FROM V$SESSION_EVENT WHERE WAIT_CLASS <> 'Idle'; - 查看等待历史中的 SQL_ID 线索：
SELECT SESSADDR, EVENT, SQL_ID, WAIT_CLASS, TIME_WAITED_MICRO FROM V$SESSION_WAIT_HISTORY ORDER BY TIME_WAITED_MICRO DESC; - 按事件与类别统计等待历史：
SELECT EVENT, WAIT_CLASS, COUNT(*) AS CNT FROM V$SESSION_WAIT_HISTORY GROUP BY EVENT, WAIT_CLASS ORDER BY CNT DESC; - 按 SQL_ID 统计等待历史出现次数：
SELECT SQL_ID, COUNT(*) AS CNT FROM V$SESSION_WAIT_HISTORY WHERE SQL_ID IS NOT NULL GROUP BY SQL_ID ORDER BY CNT DESC; - 查看等待历史参数描述：
SELECT SESSADDR, EVENT, P1TEXT, P2TEXT, P3TEXT FROM V$SESSION_WAIT_HISTORY; - 查看实时 ASH 采样明细：
SELECT SESSION_ID, SQL_ID, EVENT, WAIT_CLASS, SESSION_STATE, SAMPLE_TIME FROM V$ACTIVE_SESSION_HISTORY; - 定位 ASH 中 ON CPU 样本：
SELECT SESSION_ID, SQL_ID, EVENT, SESSION_STATE, TIME_WAITED FROM V$ACTIVE_SESSION_HISTORY WHERE SESSION_STATE = 'ON CPU'; - 按事件统计 ASH 样本分布：
SELECT EVENT, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY EVENT ORDER BY CNT DESC; - 按等待类别统计 ASH 样本：
SELECT WAIT_CLASS, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY WAIT_CLASS ORDER BY CNT DESC; - 按会话状态统计 ASH 样本：
SELECT SESSION_STATE, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY SESSION_STATE; - 按 SQL_ID 统计 ASH 活跃样本：
SELECT SQL_ID, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY WHERE SQL_ID IS NOT NULL GROUP BY SQL_ID ORDER BY CNT DESC; - 按用户统计 ASH 活跃样本：
SELECT USER_ID, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY USER_ID ORDER BY CNT DESC; - 按程序统计 ASH 活跃样本：
SELECT PROGRAM, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY PROGRAM ORDER BY CNT DESC; - 按机器统计 ASH 活跃样本：
SELECT MACHINE, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY MACHINE ORDER BY CNT DESC; - 按模块统计 ASH 活跃样本：
SELECT MODULE, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY MODULE ORDER BY CNT DESC; - 查看 ASH 中 SQL 操作类型：
SELECT SESSION_ID, SQL_ID, SQL_OPNAME, EVENT FROM V$ACTIVE_SESSION_HISTORY WHERE SQL_OPNAME IS NOT NULL; - 查看 ASH 中事务相关样本：
SELECT SESSION_ID, TRX_ID, EVENT, WAIT_CLASS FROM V$ACTIVE_SESSION_HISTORY WHERE TRX_ID IS NOT NULL; - 查看 ASH 等待参数描述：
SELECT SESSION_ID, EVENT, P1TEXT, P2TEXT, P3TEXT FROM V$ACTIVE_SESSION_HISTORY; - 按采样时间倒序查看 ASH：
SELECT SAMPLE_ID, SAMPLE_TIME, SESSION_ID, EVENT FROM V$ACTIVE_SESSION_HISTORY ORDER BY SAMPLE_TIME DESC; - 按会话类型统计 ASH 样本：
SELECT SESSION_TYPE, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY SESSION_TYPE; - 区分 AWR 采样与实时样本：
SELECT IS_AWR_SAMPLE, COUNT(*) AS CNT FROM V$ACTIVE_SESSION_HISTORY GROUP BY IS_AWR_SAMPLE; - 按等待时长排序 ASH 样本：
SELECT SESSION_ID, SQL_ID, TIME_WAITED FROM V$ACTIVE_SESSION_HISTORY ORDER BY TIME_WAITED DESC; - 查找从未发生的等待事件：
SELECT EVENT, WAIT_CLASS, TOTAL_WAITS FROM V$SYSTEM_EVENT WHERE TOTAL_WAITS = 0; - 筛选平均等待超过 1 秒的事件：
SELECT EVENT, AVERAGE_WAIT_MICRO FROM V$SYSTEM_EVENT WHERE TOTAL_WAITS > 0 AND AVERAGE_WAIT_MICRO > 1000000; - 汇总非空闲等待总时长：
SELECT SUM(TIME_WAITED_MICRO) AS TOTAL_MICRO FROM V$SYSTEM_EVENT WHERE WAIT_CLASS <> 'Idle'; - 查看应用类等待事件：
SELECT EVENT, TIME_WAITED_MICRO FROM V$SYSTEM_EVENT WHERE WAIT_CLASS = 'Application'; - 查看并发类会话等待：
SELECT SESSADDR, EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE WAIT_CLASS = 'Concurrency'; - 查看用户 I/O 类会话等待：
SELECT SESSADDR, EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE WAIT_CLASS = 'User I/O'; - 查看系统 I/O 类会话等待：
SELECT SESSADDR, EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE WAIT_CLASS = 'System I/O'; - 查看提交类会话等待：
SELECT SESSADDR, EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE WAIT_CLASS = 'Commit'; - 查看网络类会话等待：
SELECT SESSADDR, EVENT, TOTAL_WAITS FROM V$SESSION_EVENT WHERE WAIT_CLASS = 'Network';
六、SQL 执行与执行计划（176–235）
- 按执行耗时排序 SQL 历史：
SELECT SQL_ID, SESS_ID, START_TIME, TIME_USED, EXEC_ID FROM V$SQL_HISTORY ORDER BY TIME_USED DESC; - 按逻辑读排序 SQL 历史：
SELECT SQL_ID, SESS_ID, START_TIME, N_LOGIC_READ, N_PHY_READ FROM V$SQL_HISTORY ORDER BY N_LOGIC_READ DESC; - 按物理读排序 SQL 历史：
SELECT SQL_ID, SESS_ID, START_TIME, N_PHY_READ, N_LOGIC_READ FROM V$SQL_HISTORY ORDER BY N_PHY_READ DESC; - 查看 SQL 硬解析标记与命令类型：
SELECT SQL_ID, SESS_ID, EXEC_ID, HARD_PARSE_FLAG, COMMAND_TYPE FROM V$SQL_HISTORY; - 按耗时查看 SQL 主文本：
SELECT SQL_ID, TOP_SQL_TEXT, TIME_USED FROM V$SQL_HISTORY WHERE TOP_SQL_TEXT IS NOT NULL ORDER BY TIME_USED DESC; - 按耗时查看 SQL 次文本：
SELECT SQL_ID, SEC_SQL_TEXT, TIME_USED FROM V$SQL_HISTORY WHERE SEC_SQL_TEXT IS NOT NULL ORDER BY TIME_USED DESC; - 按影响行数排序 DML 历史：
SELECT SQL_ID, SESS_ID, TRX_ID, AFFECTED_ROWS FROM V$SQL_HISTORY ORDER BY AFFECTED_ROWS DESC; - 查找仍在执行的 SQL：
SELECT SQL_ID, IS_OVER, START_TIME FROM V$SQL_HISTORY WHERE IS_OVER = 0; - 按会话统计 SQL 执行次数：
SELECT SESS_ID, COUNT(*) AS CNT FROM V$SQL_HISTORY GROUP BY SESS_ID ORDER BY CNT DESC; - 按命令类型统计 SQL 执行次数：
SELECT COMMAND_TYPE, COUNT(*) AS CNT FROM V$SQL_HISTORY GROUP BY COMMAND_TYPE ORDER BY CNT DESC; - 统计硬解析与软解析次数：
SELECT HARD_PARSE_FLAG, COUNT(*) AS CNT FROM V$SQL_HISTORY GROUP BY HARD_PARSE_FLAG; - 按动态内存分配排序 SQL：
SELECT SQL_ID, BYTES_DYNAMIC_ALLOCED, BYTES_DYNAMIC_FREED FROM V$SQL_HISTORY ORDER BY BYTES_DYNAMIC_ALLOCED DESC; - 查看带调用栈的 SQL 记录：
SELECT SQL_ID, SESS_ID, CUR_SQL_NODE, STK_LEVEL FROM V$SQL_HISTORY WHERE STK_LEVEL > 0; - 查看 SQL 线程与 MPP 执行标识：
SELECT SQL_ID, THREAD_ID, MPP_EXEC_ID, MAL_ID FROM V$SQL_HISTORY; - 查看 SQL 序号与虚拟内存：
SELECT SQL_ID, SEQ_NO, VM FROM V$SQL_HISTORY; - 按执行时间排序 SQL 统计：
SELECT SQL_ID, SESSID, EXEC_TIME, EXEC_CPU FROM V$SQL_STAT ORDER BY EXEC_TIME DESC; - 按逻辑读排序 SQL 统计：
SELECT SQL_ID, SESSID, LOGIC_READ_CNT, PHY_READ_CNT FROM V$SQL_STAT ORDER BY LOGIC_READ_CNT DESC; - 按 I/O 等待排序 SQL 统计：
SELECT SQL_ID, SESSID, IO_WAIT_TIME, EXEC_TIME FROM V$SQL_STAT ORDER BY IO_WAIT_TIME DESC; - 按峰值内存排序 SQL 统计：
SELECT SQL_ID, SESSID, MAX_MEM_USED, REDO_SIZE FROM V$SQL_STAT ORDER BY MAX_MEM_USED DESC; - 按解析时间排序 SQL 统计：
SELECT SQL_ID, SESSID, PARSE_CNT, HARD_PARSE_CNT, PARSE_TIME FROM V$SQL_STAT ORDER BY PARSE_TIME DESC; - 按全表扫描次数排序 SQL：
SELECT SQL_ID, SESSID, TAB_SCAN_CNT, HASH_JOIN_CNT FROM V$SQL_STAT ORDER BY TAB_SCAN_CNT DESC; - 按网络发送量排序 SQL：
SELECT SQL_ID, SESSID, NET_BYTES_SEND, NET_BYTES_RECV, NET_ROUNDTRIPS FROM V$SQL_STAT ORDER BY NET_BYTES_SEND DESC; - 按动态执行次数排序 SQL：
SELECT SQL_ID, SESSID, DYN_EXEC_CNT, EXEC_CPU FROM V$SQL_STAT ORDER BY DYN_EXEC_CNT DESC; - 查看 SQL 执行时间与 CPU 消耗：
SELECT SQL_ID, SESSID, EXEC_TIME, EXEC_CPU FROM V$SQL_STAT ORDER BY EXEC_TIME DESC; - 查看 SQL 统计中的 SQL 文本：
SELECT SQL_ID, SESSID, SQL_TXT FROM V$SQL_STAT WHERE SQL_TXT IS NOT NULL; - 查看 SQL 计划地址与 CPU：
SELECT SQL_ID, SESSID, PLN_ADDR, EXEC_CPU FROM V$SQL_STAT; - 汇总 SQL 总执行时间与 CPU：
SELECT SUM(EXEC_TIME) AS TOTAL_EXEC_TIME, SUM(EXEC_CPU) AS TOTAL_EXEC_CPU FROM V$SQL_STAT; - 汇总 SQL 逻辑读与物理读：
SELECT SUM(LOGIC_READ_CNT) AS LOGIC_READ, SUM(PHY_READ_CNT) AS PHY_READ FROM V$SQL_STAT; - 查看计划地址、表 ID 与表数量：
SELECT SQL_ID, PLN_ADDR, TABLEID, N_TABLES FROM V$SQL_PLAN; - 按计划内存排序执行计划：
SELECT SQL_ID, MEM_SIZE, OPTIMIZER, RT_METHOD FROM V$SQL_PLAN ORDER BY MEM_SIZE DESC; - 查看计划哈希、类型与语句类型：
SELECT SQL_ID, HASH_VALUE, PLN_TYPE, STMT_TYPE FROM V$SQL_PLAN; - 查看计划可重用与递归标记：
SELECT SQL_ID, CAN_REUSE, IS_RECURSIVE, BINDED FROM V$SQL_PLAN; - 查看计划列数、表数与绑定变量：
SELECT SQL_ID, N_COLS, N_TABLES, N_CLNT_PARAS, N_LIT_PARAS FROM V$SQL_PLAN; - 查看计划用户、模式与对象标识：
SELECT SQL_ID, USER_ID, SCHID, OBJ_ID FROM V$SQL_PLAN; - 查看计划中缓存的 SQL 文本：
SELECT SQL_ID, SQLSTR FROM V$SQL_PLAN WHERE SQLSTR IS NOT NULL; - 统计计划缓存条目总数：
SELECT COUNT(*) AS CNT FROM V$SQL_PLAN; - 按优化器统计计划分布：
SELECT OPTIMIZER, COUNT(*) AS CNT FROM V$SQL_PLAN GROUP BY OPTIMIZER; - 按运行方式统计计划分布：
SELECT RT_METHOD, COUNT(*) AS CNT FROM V$SQL_PLAN GROUP BY RT_METHOD ORDER BY CNT DESC; - 按计划内存降序查看：
SELECT SQL_ID, MEM_SIZE FROM V$SQL_PLAN ORDER BY MEM_SIZE DESC; - 按计划生成耗时排序：
SELECT SQL_ID, PHD_TIME, PLN_ADDR FROM V$SQL_PLAN ORDER BY PHD_TIME DESC; - 按算子耗时排序历史算子：
SELECT EXEC_ID, NODE, TYPE$, TIME_USED, MEM_USED FROM V$SQL_NODE_HISTORY ORDER BY TIME_USED DESC; - 按算子内存排序历史算子：
SELECT EXEC_ID, NODE, TYPE$, MEM_USED, DISK_USED FROM V$SQL_NODE_HISTORY ORDER BY MEM_USED DESC; - 查看算子发送与接收行数：
SELECT EXEC_ID, NODE, ROWS_SEND, ROWS_RECV FROM V$SQL_NODE_HISTORY; - 查看算子进入次数与计划算子号：
SELECT EXEC_ID, NODE, N_ENTER, PLN_OP_ID FROM V$SQL_NODE_HISTORY; - 查看算子发送与接收字节：
SELECT EXEC_ID, NODE, BYTES_SEND, BYTES_RECV FROM V$SQL_NODE_HISTORY; - 按哈希冲突排序算子：
SELECT EXEC_ID, NODE, HASH_USED_CELLS, HASH_CONFLICT FROM V$SQL_NODE_HISTORY ORDER BY HASH_CONFLICT DESC; - 查看排序算子缓冲行数：
SELECT EXEC_ID, NODE, SORT_BUF_ROWS, SORT_BUF_AVG_ROWSIZE FROM V$SQL_NODE_HISTORY; - 定位发生磁盘溢出的算子：
SELECT EXEC_ID, NODE, DISK_USED FROM V$SQL_NODE_HISTORY WHERE DISK_USED > 0; - 按算子类型统计执行次数：
SELECT TYPE$, COUNT(*) AS CNT FROM V$SQL_NODE_HISTORY GROUP BY TYPE$ ORDER BY CNT DESC; - 汇总算子内存与磁盘使用：
SELECT SUM(MEM_USED) AS MEM_USED, SUM(DISK_USED) AS DISK_USED FROM V$SQL_NODE_HISTORY; - 按内存排序大内存 SQL：
SELECT SQL_ID, MEM_USED_BY_K, N_RUNS FROM V$SYSTEM_LARGE_MEM_SQLS ORDER BY MEM_USED_BY_K DESC; - 查看大内存 SQL 会话与事务：
SELECT SQL_ID, SESS_ID, TRX_ID, FINISH_TIME FROM V$SYSTEM_LARGE_MEM_SQLS; - 查看大内存 SQL 文本与占用：
SELECT SQL_ID, SQL_TEXT, MEM_USED_BY_K FROM V$SYSTEM_LARGE_MEM_SQLS; - 查看 SQL 解析优化类别：
SELECT SQL_ID, OPTIMIZE_CLASS, OPTIMIZE_TYPE FROM V$SQL_PARSE_HISTORY; - 查看解析阶段与线程信息：
SELECT SQL_ID, SESS_ID, PHASE_FLAG, THREAD_ID FROM V$SQL_PARSE_HISTORY; - 按优化类别统计解析事件：
SELECT OPTIMIZE_CLASS, COUNT(*) AS CNT FROM V$SQL_PARSE_HISTORY GROUP BY OPTIMIZE_CLASS ORDER BY CNT DESC; - 按优化类型统计解析事件：
SELECT OPTIMIZE_TYPE, COUNT(*) AS CNT FROM V$SQL_PARSE_HISTORY GROUP BY OPTIMIZE_TYPE ORDER BY CNT DESC; - 查看解析时使用的初始化参数：
SELECT SQL_ID, SQL_TEXT, INI_PARA FROM V$SQL_PARSE_HISTORY WHERE INI_PARA IS NOT NULL; - 查看解析事件事务与序号：
SELECT SQL_ID, TRX_ID, SEQ_NO FROM V$SQL_PARSE_HISTORY; - 按排序页数排序排序历史：
SELECT EXEC_ID, OP_SEQ_NO, OP_TYPE$, N_PAGES, SORT_ROWS FROM V$SORT_HISTORY ORDER BY N_PAGES DESC;
七、内存与线程池（236–270）
- 按总量排序内存池：
SELECT NAME, TOTAL_SIZE, DATA_SIZE, TARGET_SIZE, ORG_SIZE FROM V$MEM_POOL ORDER BY TOTAL_SIZE DESC; - 按分配次数排序内存池：
SELECT NAME, N_ALLOC, N_FREE, RESERVED_SIZE FROM V$MEM_POOL ORDER BY N_ALLOC DESC; - 查看发生正常扩展的内存池：
SELECT NAME, N_EXTEND_NORMAL, N_EXTEND_EXCLUSIVE, IS_OVERFLOW FROM V$MEM_POOL WHERE N_EXTEND_NORMAL > 0; - 定位已溢出的内存池：
SELECT NAME, IS_OVERFLOW, TOTAL_SIZE, TARGET_SIZE FROM V$MEM_POOL WHERE IS_OVERFLOW = 1; - 查看内存池创建者与来源文件：
SELECT NAME, CREATOR, FILE_NAME, TOTAL_SIZE FROM V$MEM_POOL; - 查看内存池最大扩展与扩展模式：
SELECT NAME, MAX_EXTEND_SIZE, EXTEND_SIZE, EXTEND_MODE FROM V$MEM_POOL; - 查看内存池泄漏检查与共享标记：
SELECT NAME, CHK_LEAK, CHK_MAGIC, IS_SHARED FROM V$MEM_POOL; - 查看内存池扩展长度与最小扩展：
SELECT NAME, EXTEND_LEN, MIN_EXTEND_SIZE FROM V$MEM_POOL; - 查看内存池地址与 DSA 属性：
SELECT NAME, IS_DSA_ITEM, ADDR FROM V$MEM_POOL; - 汇总内存池总量与数据量：
SELECT SUM(TOTAL_SIZE) AS TOTAL_SIZE, SUM(DATA_SIZE) AS DATA_SIZE FROM V$MEM_POOL; - 统计内存池数量：
SELECT COUNT(*) AS POOL_CNT FROM V$MEM_POOL; - 计算内存池空闲空间：
SELECT NAME, TOTAL_SIZE - DATA_SIZE AS FREE_SIZE FROM V$MEM_POOL ORDER BY FREE_SIZE DESC; - 计算内存池使用率：
SELECT NAME, DATA_SIZE * 100.0 / TOTAL_SIZE AS USED_PCT FROM V$MEM_POOL WHERE TOTAL_SIZE > 0 ORDER BY USED_PCT DESC; - 按保留空间排序内存池：
SELECT NAME, RESERVED_SIZE FROM V$MEM_POOL ORDER BY RESERVED_SIZE DESC; - 找出初始与目标大小不一致的内存池：
SELECT NAME, ORG_SIZE, TARGET_SIZE FROM V$MEM_POOL WHERE ORG_SIZE <> TARGET_SIZE; - 查看字典缓存池对象数与占用：
SELECT POOL_ID, DICT_NUM, TOTAL_SIZE, USED_SIZE FROM V$DICT_CACHE; - 计算字典缓存使用率：
SELECT POOL_ID, USED_SIZE * 100.0 / TOTAL_SIZE AS USED_PCT FROM V$DICT_CACHE WHERE TOTAL_SIZE > 0 ORDER BY USED_PCT DESC; - 汇总字典缓存对象数与占用：
SELECT SUM(DICT_NUM) AS DICT_NUM, SUM(USED_SIZE) AS USED_SIZE FROM V$DICT_CACHE; - 计算字典缓存剩余空间：
SELECT POOL_ID, TOTAL_SIZE - USED_SIZE AS FREE_SIZE FROM V$DICT_CACHE; - 查看线程池线程与容量：
SELECT N_THRDS, N_USED_THRDS, N_TEMP_THRDS, MAX_THRDS FROM V$THRDS_POOL; - 查看线程池内存占用：
SELECT MEM_USED FROM V$THRDS_POOL; - 查看模块线程池组线程数：
SELECT ID, TYPE, N_THRDS, N_USED_THRDS FROM V$THRD_POOL_GRP; - 按待办任务排序线程池组：
SELECT ID, N_WAIT_THRDS, N_SUSPEND_THRDS, N_TASK_TODO FROM V$THRD_POOL_GRP ORDER BY N_TASK_TODO DESC; - 按类型统计线程池组：
SELECT TYPE, COUNT(*) AS CNT FROM V$THRD_POOL_GRP GROUP BY TYPE; - 计算线程池组空闲线程：
SELECT ID, N_THRDS - N_USED_THRDS AS IDLE_THRDS FROM V$THRD_POOL_GRP ORDER BY IDLE_THRDS; - 找出有待办任务的线程池组：
SELECT ID, N_TASK_TODO FROM V$THRD_POOL_GRP WHERE N_TASK_TODO > 0; - 找出有挂起线程的线程池组：
SELECT ID, N_SUSPEND_THRDS FROM V$THRD_POOL_GRP WHERE N_SUSPEND_THRDS > 0; - 找出有等待线程的线程池组：
SELECT ID, N_WAIT_THRDS FROM V$THRD_POOL_GRP WHERE N_WAIT_THRDS > 0; - 汇总线程池总线程与使用线程：
SELECT SUM(N_THRDS) AS TOTAL_THRDS, SUM(N_USED_THRDS) AS USED_THRDS FROM V$THRD_POOL_GRP; - 查看资源限额与已用空间：
SELECT NAME, TYPE, SPACE_LIMIT, SPACE_USED FROM V$RESOURCE_LIMIT; - 计算资源限额使用率：
SELECT NAME, SPACE_USED * 100.0 / SPACE_LIMIT AS USED_PCT FROM V$RESOURCE_LIMIT WHERE SPACE_LIMIT > 0 ORDER BY USED_PCT DESC; - 查看资源限额编号与类型：
SELECT ID, NAME, TYPE, SPACE_LIMIT FROM V$RESOURCE_LIMIT; - 计算资源限额剩余空间：
SELECT NAME, SPACE_LIMIT - SPACE_USED AS FREE_SIZE FROM V$RESOURCE_LIMIT; - 查看 SQL 相关内存池占用：
SELECT NAME, TOTAL_SIZE, DATA_SIZE FROM V$MEM_POOL WHERE NAME LIKE '%SQL%'; - 查看缓存相关内存池占用：
SELECT NAME, TOTAL_SIZE, DATA_SIZE FROM V$MEM_POOL WHERE NAME LIKE '%CACHE%';
八、缓冲池与字典缓存（271–295）
- 查看缓冲池页数与最大页数：
SELECT NAME, N_PAGES, N_TOTAL_PAGES, N_MAX_PAGES FROM V$BUFFERPOOL; - 查看缓冲池脏页、固定页与空闲页：
SELECT NAME, N_DIRTY, N_FIXED, FREE FROM V$BUFFERPOOL; - 查看缓冲池逻辑读、物理读与写：
SELECT NAME, N_LOGIC_READS, N_PHY_READS, N_PHY_WRITE FROM V$BUFFERPOOL; - 查看缓冲池命中率与页大小：
SELECT NAME, RAT_HIT, PAGE_SIZE FROM V$BUFFERPOOL; - 按淘汰次数排序缓冲池：
SELECT NAME, N_DISCARD, N_EXP_BUFFERPOOL FROM V$BUFFERPOOL ORDER BY N_DISCARD DESC; - 查看缓冲池清理与累计淘汰：
SELECT NAME, N_CLEAR, N_DISCARD64 FROM V$BUFFERPOOL; - 查看缓冲池多块读次数：
SELECT NAME, N_PHY_M_READS, N_PHY_M_READS64 FROM V$BUFFERPOOL; - 查看缓冲池累计读写次数：
SELECT NAME, N_PHY_READS64, N_PHY_WRITE64 FROM V$BUFFERPOOL; - 查看缓冲池更新链查找与移除：
SELECT NAME, N_UPD_PUT, N_UPD_REMOVE, N_UPD_SEARCH FROM V$BUFFERPOOL; - 查看缓冲池累计更新链操作：
SELECT NAME, N_UPD_PUT64, N_UPD_REMOVE64, N_UPD_SEARCH64 FROM V$BUFFERPOOL; - 查看缓冲池内存与分区索引分配：
SELECT NAME, N_BUFMEM_ALLOCED, N_PTX_BI_ALLOCED FROM V$BUFFERPOOL; - 查看缓冲池编号与名称：
SELECT ID, NAME FROM V$BUFFERPOOL; - 汇总缓冲池总页与脏页：
SELECT SUM(N_PAGES) AS PAGES, SUM(N_DIRTY) AS DIRTY FROM V$BUFFERPOOL; - 汇总缓冲池逻辑读与物理读：
SELECT SUM(N_LOGIC_READS) AS LOGIC_READS, SUM(N_PHY_READS) AS PHY_READS FROM V$BUFFERPOOL; - 计算缓冲池脏页比例：
SELECT NAME, N_DIRTY * 100.0 / N_PAGES AS DIRTY_PCT FROM V$BUFFERPOOL WHERE N_PAGES > 0 ORDER BY DIRTY_PCT DESC; - 计算缓冲池空闲页比例：
SELECT NAME, FREE * 100.0 / N_PAGES AS FREE_PCT FROM V$BUFFERPOOL WHERE N_PAGES > 0 ORDER BY FREE_PCT; - 按固定页排序缓冲池：
SELECT NAME, N_FIXED FROM V$BUFFERPOOL ORDER BY N_FIXED DESC; - 定位发生过扩展的缓冲池：
SELECT NAME, N_EXP_BUFFERPOOL FROM V$BUFFERPOOL WHERE N_EXP_BUFFERPOOL > 0; - 按累计淘汰排序缓冲池：
SELECT NAME, N_DISCARD64 FROM V$BUFFERPOOL ORDER BY N_DISCARD64 DESC; - 按对象数排序字典缓存池：
SELECT POOL_ID, DICT_NUM FROM V$DICT_CACHE ORDER BY DICT_NUM DESC; - 按占用排序字典缓存池：
SELECT POOL_ID, USED_SIZE FROM V$DICT_CACHE ORDER BY USED_SIZE DESC; - 按总量排序字典缓存池：
SELECT POOL_ID, TOTAL_SIZE FROM V$DICT_CACHE ORDER BY TOTAL_SIZE DESC; - 查看缓冲池页大小：
SELECT NAME, PAGE_SIZE FROM V$BUFFERPOOL; - 统计缓冲池数量：
SELECT COUNT(*) AS CNT FROM V$BUFFERPOOL; - 计算缓冲池可用页数：
SELECT NAME, N_PAGES - N_FIXED AS AVAILABLE_PAGES FROM V$BUFFERPOOL;
九、表空间与数据文件（296–345）
- 查看表空间状态、总页与已用页：
SELECT ID, NAME, TYPE$, STATUS$, TOTAL_SIZE, USED_SIZE, FILE_NUM FROM V$TABLESPACE; - 计算表空间空闲页与使用率：
SELECT ID, NAME, TOTAL_SIZE - USED_SIZE AS FREE_PAGES, USED_SIZE * 100.0 / TOTAL_SIZE AS USED_PCT FROM V$TABLESPACE WHERE TOTAL_SIZE > 0 ORDER BY USED_PCT DESC; - 查看表空间限额与扩展失败计数：
SELECT ID, NAME, SPACE_LIMIT_CNT, EXTEND_FAILED_CNT FROM V$TABLESPACE; - 查看表空间最大大小与增长模式：
SELECT ID, NAME, MAX_SIZE, SIZE_MODE FROM V$TABLESPACE; - 查看表空间空闲簇与保留簇：
SELECT ID, NAME, FREE_EXTENTS, N_RESERVE_EXTENTS FROM V$TABLESPACE; - 查看表空间缓存属性与副本数：
SELECT ID, NAME, CACHE, COPY_NUM FROM V$TABLESPACE; - 查看表空间加密名与优化节点：
SELECT ID, NAME, ENCRYPT_NAME, OPT_NODE FROM V$TABLESPACE; - 查看表空间主数据文件空闲计数：
SELECT ID, NAME, MDF_FREE_COUNT FROM V$TABLESPACE; - 按状态统计表空间：
SELECT STATUS$, COUNT(*) AS CNT FROM V$TABLESPACE GROUP BY STATUS$; - 按类型统计表空间：
SELECT TYPE$, COUNT(*) AS CNT FROM V$TABLESPACE GROUP BY TYPE$; - 汇总表空间总页与已用页：
SELECT SUM(TOTAL_SIZE) AS TOTAL_PAGES, SUM(USED_SIZE) AS USED_PAGES FROM V$TABLESPACE; - 查看数据文件所属表空间与路径：
SELECT GROUP_ID, ID, PATH, TOTAL_SIZE, FREE_SIZE FROM V$DATAFILE; - 计算数据文件已用页与空闲比例：
SELECT GROUP_ID, ID, TOTAL_SIZE - FREE_SIZE AS USED_PAGES, FREE_SIZE * 100.0 / TOTAL_SIZE AS FREE_PCT FROM V$DATAFILE WHERE TOTAL_SIZE > 0 ORDER BY FREE_PCT; - 查看数据文件自动扩展与增量：
SELECT GROUP_ID, ID, AUTO_EXTEND, MAX_SIZE, NEXT_SIZE FROM V$DATAFILE; - 查看数据文件状态与读写状态：
SELECT GROUP_ID, ID, STATUS$, RW_STATUS FROM V$DATAFILE; - 查看数据文件页大小与真实空闲：
SELECT GROUP_ID, ID, PAGE_SIZE, REAL_FREE_SIZE FROM V$DATAFILE; - 按读页数排序数据文件：
SELECT GROUP_ID, ID, PAGES_READ, PAGES_WRITE FROM V$DATAFILE ORDER BY PAGES_READ DESC; - 按写请求排序数据文件：
SELECT GROUP_ID, ID, READ_REQUEST, WRITE_REQUEST FROM V$DATAFILE ORDER BY WRITE_REQUEST DESC; - 查看数据文件创建与最后检查点时间：
SELECT GROUP_ID, ID, CREATE_TIME, LAST_CKPT_TIME FROM V$DATAFILE; - 查看数据文件修改时间与事务：
SELECT GROUP_ID, ID, MODIFY_TIME, MODIFY_TRX FROM V$DATAFILE; - 查看数据文件主路径与镜像路径：
SELECT GROUP_ID, ID, CLIENT_PATH, MIRROR_PATH FROM V$DATAFILE; - 查看数据文件空闲页号：
SELECT GROUP_ID, ID, FREE_PAGE_NO FROM V$DATAFILE; - 统计数据文件总数：
SELECT COUNT(*) AS FILE_CNT FROM V$DATAFILE; - 按表空间统计数据文件数：
SELECT GROUP_ID, COUNT(*) AS FILE_CNT FROM V$DATAFILE GROUP BY GROUP_ID ORDER BY FILE_CNT DESC; - 汇总数据文件总页与空闲页：
SELECT SUM(TOTAL_SIZE) AS TOTAL_PAGES, SUM(FREE_SIZE) AS FREE_PAGES FROM V$DATAFILE; - 按字节排序数据文件：
SELECT TABLESPACE_NAME, FILE_ID, BYTES, BLOCKS FROM DBA_DATA_FILES ORDER BY BYTES DESC; - 按表空间汇总数据文件字节：
SELECT TABLESPACE_NAME, SUM(BYTES) AS BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME ORDER BY BYTES DESC; - 查看数据文件自动扩展属性：
SELECT FILE_ID, FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE FROM DBA_DATA_FILES; - 查看数据文件最大字节与增量：
SELECT FILE_ID, FILE_NAME, MAXBYTES, INCREMENT_BY FROM DBA_DATA_FILES; - 查看数据文件状态与在线状态：
SELECT FILE_ID, FILE_NAME, STATUS, ONLINE_STATUS FROM DBA_DATA_FILES; - 按表空间汇总空闲字节：
SELECT TABLESPACE_NAME, SUM(BYTES) AS FREE_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY FREE_BYTES; - 查看空闲空间块明细：
SELECT TABLESPACE_NAME, FILE_ID, BLOCK_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE ORDER BY BYTES DESC; - 按表空间统计空闲块数量：
SELECT TABLESPACE_NAME, COUNT(*) AS CNT FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY CNT; - 查看表空间块大小、状态与内容类型：
SELECT TABLESPACE_NAME, BLOCK_SIZE, STATUS, CONTENTS FROM DBA_TABLESPACES; - 查看表空间日志、区管理与分配方式：
SELECT TABLESPACE_NAME, LOGGING, EXTENT_MANAGEMENT, ALLOCATION_TYPE FROM DBA_TABLESPACES; - 查看表空间大文件、最大大小与保留：
SELECT TABLESPACE_NAME, BIGFILE, MAX_SIZE, RETENTION FROM DBA_TABLESPACES; - 查看表空间加密与压缩属性：
SELECT TABLESPACE_NAME, ENCRYPTED, COMPRESS_FOR FROM DBA_TABLESPACES; - 查看表空间初始、下一与最小区大小：
SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTLEN FROM DBA_TABLESPACES; - 查看表空间增长比例与区数限制：
SELECT TABLESPACE_NAME, PCT_INCREASE, MIN_EXTENTS, MAX_EXTENTS FROM DBA_TABLESPACES; - 查看表空间段管理与默认压缩：
SELECT TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT, DEF_TAB_COMPRESSION FROM DBA_TABLESPACES; - 查看表空间插入与谓词评估属性：
SELECT TABLESPACE_NAME, PLUGGED_IN, PREDICATE_EVALUATION FROM DBA_TABLESPACES; - 查看表空间强制日志设置：
SELECT TABLESPACE_NAME, FORCE_LOGGING FROM DBA_TABLESPACES; - 按内容类型统计表空间：
SELECT CONTENTS, COUNT(*) AS CNT FROM DBA_TABLESPACES GROUP BY CONTENTS; - 按状态统计表空间：
SELECT STATUS, COUNT(*) AS CNT FROM DBA_TABLESPACES GROUP BY STATUS; - 按字节排序大段：
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, BYTES FROM DBA_SEGMENTS ORDER BY BYTES DESC; - 查看段所属表空间与字节：
SELECT OWNER, SEGMENT_NAME, TABLESPACE_NAME, BYTES FROM DBA_SEGMENTS ORDER BY BYTES DESC; - 按表空间汇总段占用字节：
SELECT TABLESPACE_NAME, SUM(BYTES) AS BYTES FROM DBA_SEGMENTS GROUP BY TABLESPACE_NAME ORDER BY BYTES DESC; - 按段类型统计数量与占用：
SELECT SEGMENT_TYPE, COUNT(*) AS CNT, SUM(BYTES) AS BYTES FROM DBA_SEGMENTS GROUP BY SEGMENT_TYPE ORDER BY BYTES DESC; - 按区数排序段：
SELECT OWNER, SEGMENT_NAME, EXTENTS, BLOCKS FROM DBA_SEGMENTS ORDER BY EXTENTS DESC; - 查看段头文件与块位置：
SELECT OWNER, SEGMENT_NAME, HEADER_FILE, HEADER_BLOCK FROM DBA_SEGMENTS;
十、Redo 与检查点（346–370）
- 查看重做日志文件组、路径与大小：
SELECT GROUP_ID, FILE_ID, PATH, RLOG_SIZE FROM V$RLOGFILE; - 查看日志文件创建时间与版本下限：
SELECT GROUP_ID, FILE_ID, CREATE_TIME, MIN_DCT_VER, MIN_EXEC_VER FROM V$RLOGFILE; - 按组统计日志文件数与总大小：
SELECT GROUP_ID, COUNT(*) AS CNT, SUM(RLOG_SIZE) AS SIZE FROM V$RLOGFILE GROUP BY GROUP_ID; - 汇总重做日志总大小：
SELECT SUM(RLOG_SIZE) AS TOTAL_SIZE FROM V$RLOGFILE; - 查看当前、检查点、刷写与文件 LSN：
SELECT CUR_LSN, CKPT_LSN, FLUSH_LSN, FILE_LSN FROM V$RLOG_HISTORY; - 查看当前与检查点文件号及偏移：
SELECT CUR_FILE, CKPT_FILE, CUR_OFF, CKPT_OFF FROM V$RLOG_HISTORY; - 查看日志总空间、空闲空间与采样时间：
SELECT TOTAL_SPACE, FREE_SPACE, RECORD_TIME FROM V$RLOG_HISTORY; - 查看刷写页与待刷页数量：
SELECT FLUSH_PAGES, FLUSHING_PAGES, TOTAL_FLUSH_PAGES FROM V$RLOG_HISTORY; - 查看累计刷写次数与下一序列：
SELECT TOTAL_FLUSH_TIMES, G_NEXT_SEQ, RECORD_TIME FROM V$RLOG_HISTORY; - 查看日志包内存与已用内存：
SELECT RPKG_MEM_SIZE, RPKG_USED_SIZE FROM V$RLOG_HISTORY; - 按采样时间倒序查看检查点 LSN：
SELECT CKPT_LSN, CKPT_FILE, CKPT_OFF, RECORD_TIME FROM V$RLOG_HISTORY ORDER BY RECORD_TIME DESC; - 计算日志空闲空间比例：
SELECT FREE_SPACE, TOTAL_SPACE, FREE_SPACE * 100.0 / TOTAL_SPACE AS FREE_PCT FROM V$RLOG_HISTORY WHERE TOTAL_SPACE > 0; - 按开始时间查看检查点历史：
SELECT CKPT_CMD, CKPT_LSN, START_TIME, TIME_USED FROM V$CKPT_HISTORY ORDER BY START_TIME DESC; - 按刷页数排序检查点：
SELECT CKPT_CMD, PAGE_FLUSHED, TIME_USED FROM V$CKPT_HISTORY ORDER BY PAGE_FLUSHED DESC; - 查看检查点文件、偏移与空闲空间：
SELECT CKPT_CMD, FIL, OFF, FREE_SPACE FROM V$CKPT_HISTORY; - 查看检查点线程、序号与进行状态：
SELECT CKPT_CMD, THREAD_ID, SEQNO, IN_PROCESS FROM V$CKPT_HISTORY; - 按触发原因统计检查点：
SELECT CKPT_CMD, COUNT(*) AS CNT FROM V$CKPT_HISTORY GROUP BY CKPT_CMD; - 按原因计算检查点平均耗时：
SELECT CKPT_CMD, AVG(TIME_USED) AS AVG_TIME FROM V$CKPT_HISTORY GROUP BY CKPT_CMD; - 按序号倒序查看检查点：
SELECT SEQNO, CKPT_LSN, START_TIME FROM V$CKPT_HISTORY ORDER BY SEQNO DESC; - 查看检查点最大耗时：
SELECT MAX(TIME_USED) AS MAX_TIME FROM V$CKPT_HISTORY; - 统计正在进行的检查点：
SELECT COUNT(*) AS CNT FROM V$CKPT_HISTORY WHERE IN_PROCESS = 1; - 筛选耗时超过 10 秒的检查点：
SELECT CKPT_CMD, PAGE_FLUSHED FROM V$CKPT_HISTORY WHERE TIME_USED > 10; - 按耗时排序检查点：
SELECT CKPT_CMD, TIME_USED FROM V$CKPT_HISTORY ORDER BY TIME_USED DESC; - 按空闲空间排序检查点：
SELECT FIL, OFF, FREE_SPACE FROM V$CKPT_HISTORY ORDER BY FREE_SPACE; - 按线程统计检查点次数：
SELECT THREAD_ID, COUNT(*) AS CNT FROM V$CKPT_HISTORY GROUP BY THREAD_ID;
十一、归档与备份（371–410）
- 查看归档类型、目标、来源与状态：
SELECT ARCH_TYPE, ARCH_DEST, ARCH_SRC, ARCH_STATUS FROM V$ARCH_STATUS; - 按归档类型统计目的地：
SELECT ARCH_TYPE, COUNT(*) AS CNT FROM V$ARCH_STATUS GROUP BY ARCH_TYPE; - 按归档状态统计目的地：
SELECT ARCH_STATUS, COUNT(*) AS CNT FROM V$ARCH_STATUS GROUP BY ARCH_STATUS; - 查看归档来源与目标路径：
SELECT ARCH_SRC, ARCH_DEST FROM V$ARCH_STATUS; - 查看归档队列就绪与等待任务数：
SELECT ARCH_TYPE, READY, WAITING FROM V$ARCH_QUEUE; - 查看归档队列当前、最大与平均等待：
SELECT ARCH_TYPE, CUR_WAIT_TIME, MAX_WAIT_TIME, AVERAGE_WAIT_TIME FROM V$ARCH_QUEUE; - 查看归档队列累计等待时间：
SELECT ARCH_TYPE, TOTAL_WAIT_TIME FROM V$ARCH_QUEUE; - 定位存在等待任务的归档队列：
SELECT ARCH_TYPE, WAITING FROM V$ARCH_QUEUE WHERE WAITING > 0; - 按就绪任务排序归档队列：
SELECT ARCH_TYPE, READY FROM V$ARCH_QUEUE ORDER BY READY DESC; - 查看归档备份的 LSN 与序号范围：
SELECT BEGIN_LSN, END_LSN, BEGIN_SEQ, END_SEQ FROM V$ARCH_BACKUP_HISTORY; - 查看归档备份起止路径：
SELECT BEGIN_ARCH_PATH, END_ARCH_PATH FROM V$ARCH_BACKUP_HISTORY; - 查看备份集名称、时间、类型与级别：
SELECT BACKUP_ID, BACKUP_NAME, BACKUP_TIME, TYPE, LEVEL FROM V$BACKUPSET; - 查看备份集 LSN 范围与集类型：
SELECT BACKUP_ID, BEGIN_LSN, END_LSN, BAKSET_TYPE FROM V$BACKUPSET; - 查看备份压缩、加密与是否含日志：
SELECT BACKUP_ID, COMPRESS_LEVEL, ENCRYPT_TYPE, WITHOUT_LOG FROM V$BACKUPSET; - 查看备份并行度、包大小与设备：
SELECT BACKUP_ID, PARALLEL_NUM, PKG_SIZE, DEVICE_TYPE FROM V$BACKUPSET; - 查看备份对象名、对象 ID 与文件数：
SELECT BACKUP_ID, OBJECT_NAME, OBJECT_ID, DBF_NUM FROM V$BACKUPSET; - 查看备份父集、基线与累积属性：
SELECT BACKUP_ID, PARENT_ID, BASE_NAME, CUMULATIVE FROM V$BACKUPSET; - 查看备份版本与执行版本下限：
SELECT BACKUP_ID, MIN_DCT_VER, MIN_EXEC_VER, VERSION FROM V$BACKUPSET; - 按备份类型统计备份集：
SELECT TYPE, COUNT(*) AS CNT FROM V$BACKUPSET GROUP BY TYPE; - 按备份级别统计备份集：
SELECT LEVEL, COUNT(*) AS CNT FROM V$BACKUPSET GROUP BY LEVEL; - 按集类型统计备份集：
SELECT BAKSET_TYPE, COUNT(*) AS CNT FROM V$BACKUPSET GROUP BY BAKSET_TYPE; - 查看最近一次备份时间：
SELECT MAX(BACKUP_TIME) AS LAST_BACKUP_TIME FROM V$BACKUPSET; - 查看备份历史类型、状态与起止时间：
SELECT BACKUP_NAME, TYPE, STATUS, START_TIME, END_TIME FROM V$BACKUP_HISTORY; - 定位备份失败记录与错误：
SELECT BACKUP_NAME, ERROR, START_TIME FROM V$BACKUP_HISTORY WHERE STATUS <> 0; - 查看备份读写量与平均速率：
SELECT BACKUP_NAME, READ_SIZE, WRITE_SIZE, AVG_READ, AVG_WRITE FROM V$BACKUP_HISTORY; - 查看备份读写峰值与谷值：
SELECT BACKUP_NAME, MAX_READ, MIN_READ, MAX_WRITE, MIN_WRITE FROM V$BACKUP_HISTORY; - 查看备份路径与执行 SQL：
SELECT BACKUP_NAME, PATH, SQL FROM V$BACKUP_HISTORY; - 查看备份集标识与开始时间：
SELECT BACKUP_NAME, BAKSET_MAGIC, START_TIME FROM V$BACKUP_HISTORY; - 按备份类型统计历史：
SELECT TYPE, COUNT(*) AS CNT FROM V$BACKUP_HISTORY GROUP BY TYPE; - 按备份状态统计历史：
SELECT STATUS, COUNT(*) AS CNT FROM V$BACKUP_HISTORY GROUP BY STATUS; - 查看最近备份结束时间：
SELECT MAX(END_TIME) AS LAST_END_TIME FROM V$BACKUP_HISTORY; - 按开始时间倒序查看备份历史：
SELECT BACKUP_NAME, START_TIME, END_TIME FROM V$BACKUP_HISTORY ORDER BY START_TIME DESC; - 计算备份耗时天数：
SELECT BACKUP_NAME, END_TIME - START_TIME AS DURATION_DAYS FROM V$BACKUP_HISTORY; - 按写入量排序备份：
SELECT BACKUP_NAME, WRITE_SIZE FROM V$BACKUP_HISTORY ORDER BY WRITE_SIZE DESC; - 按读取量排序备份：
SELECT BACKUP_NAME, READ_SIZE FROM V$BACKUP_HISTORY ORDER BY READ_SIZE DESC; - 按平均写入速率排序备份：
SELECT BACKUP_NAME, AVG_WRITE FROM V$BACKUP_HISTORY ORDER BY AVG_WRITE DESC; - 查看备份路径：
SELECT BACKUP_NAME, PATH FROM V$BACKUP_HISTORY; - 查看带错误信息的备份记录：
SELECT BACKUP_NAME, ERROR FROM V$BACKUP_HISTORY WHERE ERROR IS NOT NULL; - 统计状态非正常的备份数：
SELECT COUNT(*) AS CNT FROM V$BACKUP_HISTORY WHERE STATUS <> 0; - 查看有执行 SQL 的备份记录：
SELECT BACKUP_NAME, SQL FROM V$BACKUP_HISTORY WHERE SQL IS NOT NULL;
十二、主备、DSC 与 MAL（411–440）
- 查看 MAL 控制链路与数据链路状态：
SELECT SRC_SITE, DEST_SITE, CTL_LINK_STATUS, DATA_LINK_STATUS FROM V$MAL_LINK_STATUS; - 查看 MAL 链路本地与远端站点：
SELECT SRC_SITE, DEST_SITE FROM V$MAL_LINK_STATUS; - 按控制链路状态统计链路：
SELECT CTL_LINK_STATUS, COUNT(*) AS CNT FROM V$MAL_LINK_STATUS GROUP BY CTL_LINK_STATUS; - 按数据链路状态统计链路：
SELECT DATA_LINK_STATUS, COUNT(*) AS CNT FROM V$MAL_LINK_STATUS GROUP BY DATA_LINK_STATUS; - 定位控制链路异常：
SELECT SRC_SITE, DEST_SITE, CTL_LINK_STATUS FROM V$MAL_LINK_STATUS WHERE CTL_LINK_STATUS <> 1; - 定位数据链路异常：
SELECT SRC_SITE, DEST_SITE, DATA_LINK_STATUS FROM V$MAL_LINK_STATUS WHERE DATA_LINK_STATUS <> 1; - 查看备库恢复目标与发送 LSN：
SELECT PRIMARY_NAME, STANDBY_NAME, SEND_LSN, PRIM_LSN FROM V$RECOVER_STATUS; - 查看备库恢复进度与待恢复文件数：
SELECT PRIMARY_NAME, STANDBY_NAME, RECOVER_PERCENT, FILES_TO_RECOVER FROM V$RECOVER_STATUS; - 查看备库待恢复数据量：
SELECT PRIMARY_NAME, STANDBY_NAME, KBYTES_TOTAL, KBYTES_TO_RECOVER FROM V$RECOVER_STATUS; - 按恢复进度排序备库：
SELECT PRIMARY_NAME, STANDBY_NAME, RECOVER_PERCENT FROM V$RECOVER_STATUS ORDER BY RECOVER_PERCENT; - 查看备库重演活跃状态与日志序号：
SELECT APPLYING, KSEQ, SLSN, KLSN FROM V$RAPPLY_SYS; - 查看重演任务数与已完成任务数：
SELECT SEQNO, SSEQ, TASK_NUM, TASK_NUM_APPLIED FROM V$RAPPLY_SYS; - 查看重演内存、最后重做与累计时间：
SELECT TASK_MEM_USED, LAST_REDO_TIME, APPLIED_TOTAL_TIME FROM V$RAPPLY_SYS; - 查看主库名、重演类型与日志包保留：
SELECT PRIMARY_NAME, RREC_TYPE, HAS_KEEP_RPKG FROM V$RAPPLY_SYS; - 查看重演任务开始时间与数量：
SELECT TASK_START_TIME, TASK_NUM FROM V$RAPPLY_SYS; - 查看重演日志包 LSN 范围与大小：
SELECT PKG_SEQNO, MIN_LSN, MAX_LSN, LOG_SIZE FROM V$RAPPLY_LOG_TASK; - 查看重演日志包长度与类型：
SELECT PKG_SEQNO, PKG_LEN, PKG_TYPE, TYPE FROM V$RAPPLY_LOG_TASK; - 查看日志包前序 LSN 与偏移：
SELECT PKG_SEQNO, PREV_LSN, PTX_OFF, DB_MAGIC FROM V$RAPPLY_LOG_TASK; - 按 DSC 节点统计重演任务：
SELECT DSC_SEQNO, COUNT(*) AS CNT FROM V$RAPPLY_LOG_TASK GROUP BY DSC_SEQNO; - 汇总重演日志总大小：
SELECT SUM(LOG_SIZE) AS TOTAL_LOG_SIZE FROM V$RAPPLY_LOG_TASK; - 按日志包大小排序重演任务：
SELECT PKG_SEQNO, LOG_SIZE FROM V$RAPPLY_LOG_TASK ORDER BY LOG_SIZE DESC; - 查看数据库角色、状态、归档与节点数：
SELECT NAME, ROLE$, STATUS$, ARCH_MODE, DSC_NODES FROM V$DATABASE; - 查看数据库容量、打开与启动次数：
SELECT NAME, TOTAL_SIZE, MAX_SIZE, OPEN_COUNT, STARTUP_COUNT FROM V$DATABASE; - 查看实例在 DSC 中的角色与序号：
SELECT INSTANCE_NAME, HOST_NAME, DSC_ROLE, DSC_SEQNO FROM V$INSTANCE; - 查看实例 OGUID 与实例编号：
SELECT INSTANCE_NAME, OGUID, INSTANCE_NUMBER FROM V$INSTANCE; - 查看实例打开模式与打开时间：
SELECT SYS_MODE, OPEN_TIME, N_EP, CUR_INST_NAME FROM SYSOPENHISTORY ORDER BY OPEN_TIME DESC; - 查看当前与主库 DB_MAGIC 及 RGUID：
SELECT CUR_DB_MAGIC, PRIMARY_DB_MAGIC, RGUID FROM SYSOPENHISTORY; - 查看当前与主库实例名：
SELECT CUR_INST_NAME, PRIMARY_INST_NAME FROM SYSOPENHISTORY; - 查看节点 0 的 APPLY_LSN 与包序号：
SELECT APPLY_LSN_0, PKG_SEQNO_0, N_EP FROM SYSOPENHISTORY; - 查看实例主机与启动时间：
SELECT INSTANCE_NAME, HOST_NAME, START_TIME FROM V$INSTANCE;
十三、作业管理（441–460）
- 查看作业启用、有效与创建者：
SELECT ID, NAME, ENABLE, VALID, USERNAME FROM SYSJOB.SYSJOBS; - 查看作业创建与修改时间：
SELECT ID, NAME, CREATETIME, MODIFYTIME FROM SYSJOB.SYSJOBS; - 查看作业执行节点与系统标志：
SELECT ID, NAME, EP_SEQNO, SYS_FLAG FROM SYSJOB.SYSJOBS; - 查看作业邮件与网络通知开关：
SELECT ID, NAME, ENABLE_EMAIL, ENABLE_NETSEND FROM SYSJOB.SYSJOBS; - 查看作业邮件与网络通知类型：
SELECT ID, NAME, EMAIL_TYPE, NETSEND_TYPE FROM SYSJOB.SYSJOBS; - 查看作业所属 RAFT 组：
SELECT ID, NAME, RAFT_NAME FROM SYSJOB.SYSJOBS; - 按启用状态统计作业：
SELECT ENABLE, COUNT(*) AS CNT FROM SYSJOB.SYSJOBS GROUP BY ENABLE; - 按有效性统计作业：
SELECT VALID, COUNT(*) AS CNT FROM SYSJOB.SYSJOBS GROUP BY VALID; - 按创建者统计作业：
SELECT USERNAME, COUNT(*) AS CNT FROM SYSJOB.SYSJOBS GROUP BY USERNAME; - 查看作业执行历史与起止时间：
SELECT JOBID, NAME, EXEC_ID, START_TIME, END_TIME FROM SYSJOB.SYSJOBHISTORIES2; - 定位作业执行失败与错误信息：
SELECT JOBID, NAME, ERRCODE, ERRINFO FROM SYSJOB.SYSJOBHISTORIES2 WHERE ERRCODE <> 0; - 按作业统计执行次数：
SELECT JOBID, COUNT(*) AS CNT FROM SYSJOB.SYSJOBHISTORIES2 GROUP BY JOBID ORDER BY CNT DESC; - 按开始时间倒序查看作业历史：
SELECT NAME, START_TIME, END_TIME FROM SYSJOB.SYSJOBHISTORIES2 ORDER BY START_TIME DESC; - 查看作业执行节点与通知状态：
SELECT JOBID, EP_SEQNO, HAS_NOTIFIED FROM SYSJOB.SYSJOBHISTORIES2; - 查看作业步骤名称与类型：
SELECT ID, JOBID, NAME, TYPE FROM SYSJOB.SYSJOBSTEPS; - 查看作业步骤重试次数与间隔：
SELECT ID, JOBID, SEQNO, RETRY_ATTEMPTS, RETRY_INTERVAL FROM SYSJOB.SYSJOBSTEPS; - 查看作业步骤成功与失败动作：
SELECT ID, JOBID, SUCC_ACTION, FAIL_ACTION FROM SYSJOB.SYSJOBSTEPS; - 查看作业步骤输出文件与追加标志：
SELECT ID, JOBID, OUTPUT_FILE_PATH, APPEND_FLAG FROM SYSJOB.SYSJOBSTEPS; - 查看作业步骤命令与目标库：
SELECT ID, JOBID, COMMAND, DBNAME FROM SYSJOB.SYSJOBSTEPS; - 按作业统计步骤数：
SELECT JOBID, COUNT(*) AS CNT FROM SYSJOB.SYSJOBSTEPS GROUP BY JOBID;
十四、用户与权限（461–480）
- 查看账号状态、锁定与过期时间：
SELECT USERNAME, ACCOUNT_STATUS, LOCK_DATE, EXPIRY_DATE FROM DBA_USERS; - 查看用户默认表空间与临时表空间：
SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE FROM DBA_USERS; - 查看用户创建时间、配置与认证方式：
SELECT USERNAME, CREATED, PROFILE, AUTHENTICATION_TYPE FROM DBA_USERS; - 查看用户 ID 与口令版本：
SELECT USERNAME, USER_ID, PASSWORD_VERSIONS FROM DBA_USERS; - 查看外部名与版本启用状态：
SELECT USERNAME, EXTERNAL_NAME, EDITIONS_ENABLED FROM DBA_USERS; - 按账号状态统计用户：
SELECT ACCOUNT_STATUS, COUNT(*) AS CNT FROM DBA_USERS GROUP BY ACCOUNT_STATUS; - 按默认表空间统计用户：
SELECT DEFAULT_TABLESPACE, COUNT(*) AS CNT FROM DBA_USERS GROUP BY DEFAULT_TABLESPACE; - 按配置统计用户：
SELECT PROFILE, COUNT(*) AS CNT FROM DBA_USERS GROUP BY PROFILE; - 查看角色口令与认证要求：
SELECT ROLE, PASSWORD_REQUIRED, AUTHENTICATION_TYPE FROM DBA_ROLES; - 查看直接授予的系统权限：
SELECT GRANTEE, PRIVILEGE, ADMIN_OPTION FROM DBA_SYS_PRIVS; - 按被授权者统计系统权限数：
SELECT GRANTEE, COUNT(*) AS CNT FROM DBA_SYS_PRIVS GROUP BY GRANTEE ORDER BY CNT DESC; - 查看角色授予与默认角色：
SELECT GRANTEE, GRANTED_ROLE, DEFAULT_ROLE FROM DBA_ROLE_PRIVS; - 查看角色管理选项：
SELECT GRANTEE, ADMIN_OPTION FROM DBA_ROLE_PRIVS; - 按角色统计被授予人数：
SELECT GRANTED_ROLE, COUNT(*) AS CNT FROM DBA_ROLE_PRIVS GROUP BY GRANTED_ROLE ORDER BY CNT DESC; - 查看对象权限授予明细：
SELECT OWNER, TABLE_NAME, GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS; - 按被授权者统计对象权限数：
SELECT GRANTEE, COUNT(*) AS CNT FROM DBA_TAB_PRIVS GROUP BY GRANTEE ORDER BY CNT DESC; - 查看对象权限与可转授权标志：
SELECT OWNER, TABLE_NAME, PRIVILEGE, GRANTABLE FROM DBA_TAB_PRIVS; - 查看权限授予者与接收者：
SELECT GRANTOR, GRANTEE, PRIVILEGE FROM DBA_TAB_PRIVS; - 按系统权限统计授予次数：
SELECT PRIVILEGE, COUNT(*) AS CNT FROM DBA_SYS_PRIVS GROUP BY PRIVILEGE ORDER BY CNT DESC; - 查看用户与当前系统时间：
SELECT USERNAME, NOWDATE FROM DBA_USERS;
十五、对象与统计信息（481–500）
- 按行数排序用户表：
SELECT OWNER, TABLE_NAME, NUM_ROWS, BLOCKS, LAST_ANALYZED FROM DBA_TABLES ORDER BY NUM_ROWS DESC; - 查看表所属表空间与状态：
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME, STATUS FROM DBA_TABLES; - 查看表分区与临时表属性：
SELECT OWNER, TABLE_NAME, PARTITIONED, TEMPORARY FROM DBA_TABLES; - 查看表压缩、日志与并行度：
SELECT OWNER, TABLE_NAME, COMPRESSION, LOGGING, DEGREE FROM DBA_TABLES; - 按模式统计表数量：
SELECT OWNER, COUNT(*) AS CNT FROM DBA_TABLES GROUP BY OWNER ORDER BY CNT DESC; - 查看索引唯一性与状态：
SELECT OWNER, INDEX_NAME, TABLE_NAME, UNIQUENESS, STATUS FROM DBA_INDEXES; - 按 BLEVEL 排序索引层级：
SELECT OWNER, INDEX_NAME, INDEX_TYPE, BLEVEL FROM DBA_INDEXES ORDER BY BLEVEL DESC; - 按叶块数排序索引：
SELECT OWNER, INDEX_NAME, NUM_ROWS, LEAF_BLOCKS FROM DBA_INDEXES ORDER BY LEAF_BLOCKS DESC; - 查看索引统计时间与去重键数：
SELECT OWNER, INDEX_NAME, LAST_ANALYZED, DISTINCT_KEYS FROM DBA_INDEXES; - 按状态统计索引：
SELECT STATUS, COUNT(*) AS CNT FROM DBA_INDEXES GROUP BY STATUS; - 按索引类型统计索引：
SELECT INDEX_TYPE, COUNT(*) AS CNT FROM DBA_INDEXES GROUP BY INDEX_TYPE; - 定位失效对象：
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE STATUS <> 'VALID'; - 按模式与类型统计对象：
SELECT OWNER, OBJECT_TYPE, COUNT(*) AS CNT FROM DBA_OBJECTS GROUP BY OWNER, OBJECT_TYPE ORDER BY CNT DESC; - 按最后 DDL 时间倒序查看对象变更：
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, LAST_DDL_TIME FROM DBA_OBJECTS ORDER BY LAST_DDL_TIME DESC; - 查看表与分区统计信息概况：
SELECT TABLE_OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED, STALE_STATS FROM DBA_TAB_STATISTICS; - 定位统计信息过旧的对象：
SELECT TABLE_OWNER, TABLE_NAME, STALE_STATS, SAMPLE_SIZE FROM DBA_TAB_STATISTICS WHERE STALE_STATS = 'YES'; - 查看分区行数与统计：
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, NUM_ROWS FROM DBA_TAB_PARTITIONS; - 查看分区边界值：
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM DBA_TAB_PARTITIONS; - 查看列类型、去重数与空值数：
SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, NUM_DISTINCT, NUM_NULLS FROM DBA_TAB_COLUMNS; - 查看列统计时间与直方图类型：
SELECT OWNER, TABLE_NAME, COLUMN_NAME, LAST_ANALYZED, HISTOGRAM FROM DBA_TAB_COLUMNS;
原文地址: https://www.cveoy.top/t/topic/qHWc 著作权归作者所有。请勿转载和采集!