本文档风哥主要介绍Oracle数据库数据字典相关知识，包括Oracle数据库数据字典的概念、Oracle数据库数据字典的结构、Oracle数据库常用数据字典视图、Oracle数据库数据字典查询、Oracle数据库数据字典使用等内容，由风哥教程参考Oracle官方文档Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
Part01-基础概念与理论知识
1.1 Oracle数据库数据字典的概念
Oracle数据库数据字典（Data Dictionary）是一组只读的表和视图，记录了数据库的元数据（Metadata），包括数据库对象、用户、权限、存储参数等信息。数据字典是Oracle数据库的核心组件，由Oracle自动维护，用户只能读取不能直接修改。更多视频教程www.fgedu.net.cn
- 只读：用户不能直接修改
- 自动维护：Oracle自动更新
- 存储在SYSTEM表空间
- 包含数据库所有元数据
- 提供多种视图供查询
1.2 Oracle数据库数据字典的结构
Oracle数据库数据字典的结构：
- 基表（Base Tables）：存储实际数据的表，如SYS用户下的表
- 用户视图（User Views）：以USER_开头，只显示当前用户的对象
- 所有视图（All Views）：以ALL_开头，显示用户有权限访问的对象
- DBA视图（DBA Views）：以DBA_开头，显示数据库所有对象（需要DBA权限）
- 动态性能视图（Dynamic Performance Views）：以V$开头，显示实时性能数据
1.3 Oracle数据库数据字典视图类型
Oracle数据库数据字典视图类型：
- USER_视图：USER_TABLES、USER_INDEXES、USER_VIEWS等
- ALL_视图：ALL_TABLES、ALL_INDEXES、ALL_VIEWS等
- DBA_视图：DBA_TABLES、DBA_INDEXES、DBA_VIEWS等
- V$视图：V$SESSION、V$SQL、V$LOCK等
- GV$视图：GV$SESSION、GV$SQL等（RAC环境）
Part02-生产环境规划与建议
2.1 Oracle数据库数据字典使用场景
Oracle数据库数据字典使用场景：
– 查看数据库对象信息
– 查看用户和权限信息
– 查看表空间和数据文件信息
– 查看会话和进程信息
# 监控诊断场景
– 查看性能指标
– 查看锁和等待事件
– 查看SQL执行情况
– 查看存储使用情况
# 故障排查场景
– 查看错误信息
– 查看事务状态
– 查看资源使用情况
– 查看历史记录
# 报表生成场景
– 生成空间使用报表
– 生成对象统计报表
– 生成性能报表
– 生成审计报表
2.2 Oracle数据库数据字典性能优化
Oracle数据库数据字典性能优化建议：
– 使用合适的视图（USER_ vs ALL_ vs DBA_）
– 避免查询不必要的列
– 使用WHERE条件过滤
– 避免在高峰期频繁查询
– 使用绑定变量
– 定期统计数据字典信息
# 常见优化方法
– ANALYZE TABLE或DBMS_STATS收集统计信息
– 使用适当的索引
– 避免全表扫描
– 使用并行查询（大表）
2.3 Oracle数据库数据字典安全建议
Oracle数据库数据字典安全建议：
- 权限管理：只授予必要的权限
- 审计：审计对数据字典的访问
- 保护SYS用户：限制SYS用户的访问
- 定期检查：定期检查数据字典的完整性
- 备份：定期备份SYSTEM表空间
Part03-生产环境项目实施方案
3.1 Oracle数据库常用数据字典查询
3.1.1 Oracle数据库表相关查询
SQL> select table_name, tablespace_name, status from user_tables order by table_name;TABLE_NAME TABLESPACE_NAME STATUS
—————————— —————————— ——–
FGEDU_EMPLOYEES USERS VALID
FGEDU_DEPARTMENTS USERS VALID
# 查看所有可访问的表
SQL> select owner, table_name, tablespace_name from all_tables
where table_name like ‘FGEDU%’ order by owner, table_name;OWNER TABLE_NAME TABLESPACE_NAME
———- —————————— ——————————
FGEDU FGEDU_EMPLOYEES USERS
FGEDU FGEDU_DEPARTMENTS USERS
# 查看数据库所有表（需要DBA权限）
SQL> select owner, table_name, tablespace_name, num_rows
from dba_tables where owner = ‘FGEDU’ order by table_name;OWNER TABLE_NAME TABLESPACE_NAME NUM_ROWS
———- —————————— —————————— ———-
FGEDU FGEDU_EMPLOYEES USERS 10000
FGEDU FGEDU_DEPARTMENTS USERS 100
# 查看表的列信息
SQL> select column_name, data_type, data_length, nullable
from user_tab_columns where table_name = ‘FGEDU_EMPLOYEES’ order by column_id;COLUMN_NAME DATA_TYPE DATA_LENGTH NUL
—————————— ————— ———– —
EMPLOYEE_ID NUMBER 22 N
EMP_NAME VARCHAR2 50 Y
EMP_EMP_EMP_LAST_NAME VARCHAR2 50 N
HIRE_DATE DATE 7 Y
SALARY NUMBER 22 Y
3.1.2 Oracle数据库表空间相关查询
SQL> select tablespace_name, status, contents, extent_management
from dba_tablespaces order by tablespace_name;TABLESPACE_NAME STATUS CONTENTS EXTENT_MAN
—————————— ——— ——— ———-
SYSTEM ONLINE PERMANENT LOCAL
SYSAUX ONLINE PERMANENT LOCAL
UNDOTBS1 ONLINE UNDO LOCAL
TEMP ONLINE TEMPORARY LOCAL
USERS ONLINE PERMANENT LOCAL
# 查看表空间使用情况
SQL> select
tablespace_name,
sum(bytes)/1024/1024 as total_mb,
sum(decode(autoextensible, ‘YES’, maxbytes, bytes))/1024/1024 as max_mb,
sum(bytes – decode(status, ‘ONLINE’, nvl(bytes, 0), 0))/1024/1024 as used_mb
from dba_data_files
group by tablespace_name
order by tablespace_name;TABLESPACE_NAME TOTAL_MB MAX_MB USED_MB
—————————— ———- ———- ———-
SYSTEM 1024 1024 800
SYSAUX 512 512 400
UNDOTBS1 256 2048 100
USERS 128 2048 50
3.2 Oracle数据库管理相关视图
3.2.1 Oracle数据库用户和权限查询
SQL> select username, account_status, default_tablespace, created
from dba_users order by username;USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE CREATED
—————————— ——————————– —————— ———
ANONYMOUS EXPIRED & LOCKED SYSAUX 31-MAR-26
FGEDU OPEN USERS 31-MAR-26
SYS OPEN SYSTEM 31-MAR-26
SYSTEM OPEN SYSTEM 31-MAR-26
# 查看用户权限
SQL> select grantee, privilege, admin_option
from dba_sys_privs where grantee = ‘FGEDU’ order by privilege;GRANTEE PRIVILEGE ADM
—————————— —————————————- —
FGEDU CREATE SESSION NO
FGEDU CREATE TABLE NO
FGEDU UNLIMITED TABLESPACE NO
# 查看用户角色
SQL> select grantee, granted_role, admin_option
from dba_role_privs where grantee = ‘FGEDU’;GRANTEE GRANTED_ROLE ADM
—————————— —————————— —
FGEDU CONNECT NO
FGEDU RESOURCE NO
3.3 Oracle数据库会话和性能视图
3.3.1 Oracle数据库会话查询
SQL> select sid, serial#, username, status, program, machine
from v$session where username is not null order by username;SID SERIAL# USERNAME STATUS PROGRAM MACHINE
———- ———- —————————— ——– ——————————– —————-
123 45678 FGEDU ACTIVE sqlplus@fgedu.net.cn fgedu.net.cn
124 45679 SYS INACTIVE oracle@fgedu.net.cn fgedu.net.cn
# 查看会话等待事件
SQL> select s.sid, s.serial#, s.username, s.program,
w.event, w.wait_time, w.seconds_in_wait
from v$session s, v$session_wait w
where s.sid = w.sid and s.username is not null;SID SERIAL# USERNAME PROGRAM EVENT WAIT_TIME SECONDS_IN_WAIT
———- ———- ———- ——————————– —————————— ———- —————
123 45678 FGEDU sqlplus@fgedu.net.cn SQL*Net message from client 0 10
# 查看活跃SQL
SQL> select s.sid, s.serial#, s.username,
q.sql_text, q.elapsed_time/1000000 as elapsed_sec
from v$session s, v$sql q
where s.sql_id = q.sql_id and s.status = ‘ACTIVE’;
Part04-生产案例与实战讲解
4.1 Oracle数据库监控与诊断
使用数据字典进行数据库监控与诊断：
4.1.1 Oracle数据库性能监控
SQL> select event, total_waits, time_waited, average_wait
from v$system_event order by time_waited desc;EVENT TOTAL_WAITS TIME_WAITED AVERAGE_WAIT
———————————– ———– ———– ————
db file sequential read 100000 50000000 50
log file sync 50000 30000000 600
db file scattered read 20000 10000000 500
# 查看Top SQL
SQL> select sql_id, sql_text, elapsed_time/1000000 as elapsed_sec,
executions, buffer_gets
from v$sql
order by elapsed_time desc
fetch first 10 rows only;SQL_ID SQL_TEXT ELAPSED_SEC EXECUTIONS BUFFER_GETS
————- ——————————————— ———– ———- ———–
abc123xyz000 select * from fgedu_employees where … 300.5 100 500000
# 查看表空间使用告警
SQL> select tablespace_name,
sum(bytes)/1024/1024 as total_mb,
sum(bytes – nvl(free_space, 0))/1024/1024 as used_mb,
(sum(bytes – nvl(free_space, 0))/sum(bytes))*100 as used_pct
from dba_data_files
group by tablespace_name
having (sum(bytes – nvl(free_space, 0))/sum(bytes))*100 > 80
order by used_pct desc;
4.2 Oracle数据库故障排查
SQL> select
l.sid,
s.username,
s.program,
l.type,
l.lmode,
l.request,
l.ctime
from v$lock l, v$session s
where l.sid = s.sid and l.request > 0;SID USERNAME PROGRAM TY LMODE REQUEST CTIME
———- ———- ——————————– — ———- ———- ———-
123 FGEDU sqlplus@fgedu.net.cn TX 0 6 120
# 查看阻塞会话
SQL> select
blocking_session,
sid,
serial#,
username,
seconds_in_wait
from v$session
where blocking_session is not null;BLOCKING_SESSION SID SERIAL# USERNAME SECONDS_IN_WAIT
—————- ———- ———- —————————— —————
124 123 45678 FGEDU 120
# 查看最近的错误
SQL> select
timestamp,
username,
os_username,
userhost,
action_name,
returncode
from dba_audit_trail
where timestamp > sysdate – 1
order by timestamp desc;
4.3 Oracle数据库报表生成
使用数据字典生成各类报表：
- 空间使用报表：表空间、数据文件、段使用情况
- 对象统计报表：表、索引、视图等统计信息
- 性能报表：Top SQL、等待事件、资源使用
- 安全报表：用户、权限、审计信息
- 备份报表：备份集、备份片、恢复信息
Part05-风哥经验总结与分享
5.1 Oracle数据库数据字典使用最佳实践
Oracle数据库数据字典使用最佳实践：
- 选择合适的视图：USER_、ALL_、DBA_按需选择
- 使用WHERE条件：避免全表扫描
- 避免频繁查询：在高峰期减少数据字典查询
- 定期收集统计：使用DBMS_STATS收集统计信息
- 权限控制：严格控制对DBA_视图的访问
- 审计访问：审计对重要数据字典视图的访问
5.2 Oracle数据库常用视图检查清单
– [ ] DBA_TABLES / USER_TABLES
– [ ] DBA_INDEXES / USER_INDEXES
– [ ] DBA_TAB_COLUMNS / USER_TAB_COLUMNS
– [ ] DBA_TABLESPACES
– [ ] DBA_DATA_FILES
– [ ] DBA_USERS
# 性能监控常用视图
– [ ] V$SESSION
– [ ] V$SQL
– [ ] V$LOCK
– [ ] V$SESSION_WAIT
– [ ] V$SYSTEM_EVENT
– [ ] V$SQLAREA
# 存储管理常用视图
– [ ] DBA_SEGMENTS
– [ ] DBA_EXTENTS
– [ ] DBA_FREE_SPACE
– [ ] V$TEMPFILE
– [ ] V$LOG
– [ ] V$ARCHIVED_LOG
# 安全审计常用视图
– [ ] DBA_ROLE_PRIVS
– [ ] DBA_SYS_PRIVS
– [ ] DBA_TAB_PRIVS
– [ ] DBA_AUDIT_TRAIL
5.3 Oracle数据库数据字典工具推荐
Oracle数据库数据字典工具推荐：
- SQL*Plus：直接查询数据字典视图
- Oracle Enterprise Manager：图形化展示数据字典信息
- PL/SQL Developer：方便的对象浏览器和查询工具
- Toad：强大的DBA管理工具
- AWR报告：自动生成性能分析报告
- ASH报告：活动会话历史分析
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
