【Oracle数据库指南】第24篇：Oracle数据字典与动态性能视图详解-酒店常州论坛

上一篇【第23篇】Oracle模式对象管理详解
下一篇【第25篇】Oracle备份与恢复基础详解

摘要

数据字典（Data Dictionary）是Oracle数据库系统的"元数据库"，存储了数据库对象的定义、用户权限、存储结构等核心信息。动态性能视图（Dynamic Performance Views）则以实时方式展示数据库的运行状态。本文详细讲解数据字典和动态性能视图的体系结构、常用视图查询、实时监控与故障诊断，是DBA和开发人员的必备技能。

一、数据字典概述

1.1 什么是数据字典

数据字典是Oracle自动维护的一组表和视图，存储数据库的元数据，包括：

所有数据库对象的定义（表、索引、视图等）
用户账户和权限信息
存储结构信息（表空间、数据文件）
完整性约束
数据库实例配置

数据字典存储在SYSTEM表空间中，由Oracle自动维护，用户只能查询，不能修改。

1.2 数据字典视图的三层结构

Oracle提供三层数据字典视图，权限范围不同：

视图前缀	说明	访问权限
`USER_`	当前用户拥有的对象	所有用户可访问
`ALL_`	当前用户可访问的对象（含授权）	所有用户可访问
`DBA_`	数据库中所有对象	仅DBA权限可访问

-- 示例：查看表信息SELECTtable_nameFROMuser_tables;-- 当前用户的表SELECTtable_nameFROMall_tables;-- 当前用户可访问的表SELECTtable_nameFROMdba_tables;-- 所有表（需要DBA权限）

二、常用数据字典视图

2.1 用户信息视图

-- 查看所有用户SELECTusername,account_status,created,default_tablespaceFROMdba_usersORDERBYusername;-- 查看当前用户信息SELECT*FROMuser_users;-- 查看用户系统权限SELECTprivilege,admin_optionFROMuser_sys_privs;-- 查看用户对象权限SELECTtable_name,privilegeFROMuser_tab_privs;-- 查看用户角色权限SELECTgranted_role,admin_optionFROMuser_role_privs;

2.2 表/视图信息视图

-- 查看当前用户的表SELECTtable_name,tablespace_name,num_rows,blocksFROMuser_tablesORDERBYtable_name;-- 查看表的所有列SELECTcolumn_name,data_type,data_length,nullableFROMuser_tab_columnsWHEREtable_name='EMP'ORDERBYcolumn_id;-- 查看表的约束SELECTconstraint_name,constraint_type,statusFROMuser_constraintsWHEREtable_name='EMP';-- 查看视图定义SELECTview_name,textFROMuser_views;

2.3 索引信息视图

-- 查看当前用户的索引SELECTindex_name,table_name,uniqueness,statusFROMuser_indexesORDERBYtable_name,index_name;-- 查看索引列SELECTindex_name,column_name,column_positionFROMuser_ind_columnsWHEREtable_name='EMP'ORDERBYindex_name,column_position;

2.4 表空间与数据文件视图

-- 查看所有表空间SELECTtablespace_name,block_size,status,contentsFROMdba_tablespacesORDERBYtablespace_name;-- 查看数据文件SELECTfile_name,tablespace_name,bytes/1024/1024ASsize_mb,autoextensible,maxbytes/1024/1024ASmax_mbFROMdba_data_filesORDERBYtablespace_name;-- 查看临时文件SELECTfile_name,tablespace_name,bytes/1024/1024ASsize_mbFROMdba_temp_files;

三、动态性能视图概述

3.1 什么是动态性能视图

动态性能视图（Dynamic Performance Views）以V$开头，展示数据库实时运行状态，数据来源于：

内存结构（SGA）
控制文件
操作系统状态

这些视图在数据库启动后可用，关闭后数据丢失。

3.2 常用动态性能视图分类

类别	视图前缀	说明
实例/数据库	`V$DATABASE`,`V$INSTANCE`	数据库和实例信息
内存	`V$SGA`,`V$SGAINFO`,`V$PGASTAT`	内存使用统计
进程	`V$PROCESS`,`V$SESSION`	进程和会话信息
日志	`V$LOG`,`V$LOGFILE`,`V$ARCHIVED_LOG`	重做日志信息
数据文件	`V$DATAFILE`,`V$DATAFILE_HEADER`	数据文件状态
性能统计	`V$SYSSTAT`,`V$SESSTAT`	系统和会话统计
SQL执行	`V$SQL`,`V$SQLAREA`	SQL语句统计
锁	`V$LOCK`,`V$LOCKED_OBJECT`	锁信息
等待事件	`V$SYSTEM_EVENT`,`V$SESSION_WAIT`	等待事件统计

四、实例与数据库信息视图

4.1 数据库信息

-- 查看数据库信息SELECTname,dbid,created,log_mode,open_modeFROMv$database;-- 查看实例信息SELECTinstance_name,host_name,version,status,database_statusFROMv$instance;-- 查看数据库选项SELECT*FROMv$option;

4.2 版本与参数信息

-- 查看数据库版本SELECT*FROMv$version;-- 查看初始化参数SELECTname,value,isdefault,issys_modifiableFROMv$parameterWHEREnameLIKE'%memory%'ORDERBYname;-- 查看隐藏参数（需要特殊权限）SELECTx.ksppinmASname,y.ksppstvlASvalueFROMx$ksppi x,x$ksppcv yWHEREx.indx=y.indxANDx.ksppinmLIKE'%&parameter_name%';

五、内存监控视图

5.1 SGA监控

-- 查看SGA组件大小SELECTcomponent,current_size/1024/1024AScurrent_mb,min_size/1024/1024ASmin_mb,max_size/1024/1024ASmax_mbFROMv$sga_dynamic_componentsORDERBYcomponent;-- 查看SGA信息SELECT*FROMv$sga;-- 查看SGA详细统计SELECT*FROMv$sgainfo;

5.2 PGA监控

-- 查看PGA统计SELECTname,value/1024/1024ASvalue_mbFROMv$pgastatORDERBYname;-- 查看每个会话的PGA使用SELECTs.sid,s.serial#, s.username, p.program,pm.category,pm.allocated/1024/1024ASallocated_mb,pm.used/1024/1024ASused_mbFROMv$process_memory pm,v$process p,v$sessionsWHEREpm.pid=p.pidANDp.addr=s.paddrORDERBYpm.allocatedDESC;

六、会话与进程监控

6.1 会话监控

-- 查看所有会话SELECTsid,serial#, username, status, machine, programFROMv$sessionWHEREusernameISNOTNULLORDERBYusername,sid;-- 查看当前会话SELECTsid,serial#, username, status, sql_id, prev_sql_idFROMv$sessionWHEREsid=(SELECTSYS_CONTEXT('USERENV','SID')FROMDUAL);-- 查看会话正在执行的SQLSELECTs.sid,s.serial#, s.username, q.sql_textFROMv$sessionsJOINv$sqlqONs.sql_id=q.sql_idWHEREs.usernameISNOTNULL;

6.2 进程监控

-- 查看所有进程SELECTpid,spid,program,backgroundFROMv$processORDERBYpid;-- 查看后台进程SELECTname,descriptionFROMv$bgprocessWHEREpaddr!='00';

七、日志与归档监控

7.1 在线重做日志

-- 查看在线重做日志组SELECTgroup#, thread#, sequence#, bytes/1024/1024 AS size_mb, members, statusFROMv$logORDERBYgroup#;-- 查看在线重做日志文件SELECTgroup#, member, type, is_recovery_dest_fileFROMv$logfileORDERBYgroup#, member;

7.2 归档日志

-- 查看归档日志历史SELECTsequence#, first_time, next_time, archived, deletedFROMv$archived_logORDERBYsequence# DESC;-- 查看归档日志目标SELECTdest_id,dest_name,status,target,scheduleFROMv$archive_dest_statusWHEREstatus='VALID';

八、锁与等待事件监控

8.1 锁监控

-- 查看当前锁信息SELECTs.sid,s.serial#, s.username, l.type, l.mode_held, l.mode_requestedFROMv$locklJOINv$sessionsONl.sid=s.sidWHEREl.block=1;-- 查看被锁定的对象SELECTs.sid,s.serial#, s.username, o.owner, o.object_name, o.object_typeFROMv$locked_object loJOINdba_objects oONlo.object_id=o.object_idJOINv$sessionsONlo.session_id=s.sid;

8.2 等待事件监控

-- 查看系统级等待事件SELECTevent,total_waits,total_timeouts,time_waited/100AStime_waited_sFROMv$system_eventWHEREtotal_waits>0ORDERBYtime_waitedDESC;-- 查看会话级等待事件SELECTs.sid,s.serial#, s.username, w.event, w.wait_time, w.seconds_in_waitFROMv$session_wait wJOINv$sessionsONw.sid=s.sidWHEREs.usernameISNOTNULLORDERBYw.seconds_in_waitDESC;

九、SQL执行监控

9.1 SQL语句统计

-- 查看最耗CPU的SQLSELECTsql_id,executions,cpu_time/1000000AScpu_s,elapsed_time/1000000ASelapsed_s,sql_textFROMv$sqlORDERBYcpu_timeDESCFETCHFIRST10ROWSONLY;-- 查看最耗磁盘I/O的SQLSELECTsql_id,executions,disk_reads,buffer_gets,sql_textFROMv$sqlareaORDERBYdisk_readsDESCFETCHFIRST10ROWSONLY;-- 查看执行次数最频繁的SQLSELECTsql_id,executions,parse_calls,sql_textFROMv$sqlORDERBYexecutionsDESCFETCHFIRST10ROWSONLY;

9.2 实时SQL监控（Oracle 11g+）

-- 查看正在执行的SQLSELECTsql_id,sid,serial#, username, status, sql_textFROMv$sessionsJOINv$sqlqONs.sql_id=q.sql_idWHEREs.status='ACTIVE'ANDs.usernameISNOTNULL;

十、数据字典与动态视图的联合查询

10.1 实用查询示例

-- 1. 查找没有索引的外键（性能隐患）SELECTc.constraint_name,c.table_name,cc.column_nameFROMdba_constraints cJOINdba_cons_columns ccONc.constraint_name=cc.constraint_nameWHEREc.constraint_type='R'ANDNOTEXISTS(SELECT1FROMdba_ind_columns icWHEREic.table_name=c.table_nameANDic.column_name=cc.column_name);-- 2. 查看表空间使用率SELECTd.tablespace_name,d.bytes/1024/1024AStotal_mb,(d.bytes-f.bytes)/1024/1024ASused_mb,f.bytes/1024/1024ASfree_mb,ROUND((d.bytes-f.bytes)/d.bytes*100,2)ASused_pctFROM(SELECTtablespace_name,SUM(bytes)ASbytesFROMdba_data_filesGROUPBYtablespace_name)d,(SELECTtablespace_name,SUM(bytes)ASbytesFROMdba_free_spaceGROUPBYtablespace_name)fWHEREd.tablespace_name=f.tablespace_name(+);-- 3. 查看会话的详细资源使用SELECTs.sid,s.serial#, s.username, s.osuser, s.machine,s.program,s.logon_time,st.valueAScpu_usageFROMv$sessionsJOINv$sesstat stONs.sid=st.sidJOINv$statname snONst.statistic# = sn.statistic#WHEREsn.name='CPU used by this session'ANDs.usernameISNOTNULLORDERBYst.valueDESC;

十一、最佳实践

11.1 数据字典查询建议

优先使用USER_视图：减少权限需求，提高查询效率
避免频繁查询DBA_视图：这些视图查询代价较高
使用ALL_视图检查权限：验证当前用户是否有权访问对象
定期收集统计信息：确保数据字典统计信息准确

11.2 动态性能视图使用建议

监控关键指标：定期检查SGA、PGA、会话数、锁等待
AWR报告：使用@?/rdbms/admin/awrrpt.sql生成AWR报告
ASH报告：使用@?/rdbms/admin/ashrpt.sql分析活跃会话
实时监控：结合V$SESSION_WAIT和V$SYSTEM_EVENT

11.3 常用监控脚本

-- 监控脚本：查找长时间运行的SQLSELECTs.sid,s.serial#, s.username, q.sql_text,ROUND(q.elapsed_time/1000000,2)ASelapsed_s,q.executionsFROMv$sqlqJOINv$sessionsONq.sql_id=s.sql_idWHEREq.elapsed_time>10000000-- 超过10秒ORDERBYq.elapsed_timeDESC;

十二、总结

数据字典与动态性能视图的核心要点：

数据字典：USER_、ALL_、DBA_三层视图，存储元数据
动态性能视图：V$视图，实时展示运行状态
常用视图：用户信息、表/索引信息、表空间信息
实例监控：V$INSTANCE、V$DATABASE
内存监控：V$SGA、V$PGASTAT
会话监控：V$SESSION、V$PROCESS
锁与等待事件：V$LOCK、V$SESSION_WAIT
SQL监控：V$SQL、V$SQLAREA
最佳实践：合理选择视图、定期监控、使用AWR/ASH