Oracle数据库经典SQL查询技巧

"本文主要介绍了Oracle数据库中的一些经典SQL查询语句，用于获取关于表空间、数据文件、回滚段、控制文件、日志文件以及表空间使用情况的关键信息。这些查询可以帮助数据库管理员监控和管理Oracle数据库的资源使用状况。" 在Oracle数据库管理中，了解如何有效地查询和获取关键信息对于优化性能和确保系统的正常运行至关重要。以下是一些常用的SQL查询： 1. 查看表空间的名称及大小 ```sql SELECT t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size FROM dba_tablespaces t, dba_data_files d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; ``` 这个查询返回每个表空间的名称及其占用的总磁盘空间（以MB为单位）。 2. 查看表空间物理文件的名称及大小 ```sql SELECT tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space FROM dba_data_files ORDER BY tablespace_name; ``` 此查询显示所有数据文件的详细信息，包括表空间名、文件ID、文件名和总大小（以MB为单位）。 3. 查看回滚段名称及大小 ```sql SELECT segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent, max_extents, v.curext CurExtent FROM dba_rollback_segs r, v$rollstat v WHERE r.segment_id = v.usn(+) ORDER BY segment_name; ``` 这个查询提供了回滚段的名称、表空间、状态、初始扩展大小、下一个扩展大小以及当前已使用的扩展数。 4. 查看控制文件 ```sql SELECT name FROM v$controlfile; ``` 这个简单的查询列出所有的控制文件路径，控制文件是Oracle数据库的重要组成部分，用于记录数据库的元数据和恢复信息。 5. 查看日志文件 ```sql SELECT member FROM v$logfile; ``` 此查询返回数据库的日志文件成员，这些文件用于记录事务的更改并支持数据库的恢复操作。 6. 查看表空间的使用情况 ```sql -- 方法一 SELECT sum(bytes)/(1024*1024) as free_space, tablespace_name FROM dba_free_space GROUP BY tablespace_name; -- 方法二 SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES "%USED", (C.BYTES*100)/A.BYTES "%FREE" FROM SYS.SM$TS_AVAILA, SYS.SM$TS_USEDB, SYS.SM$TS_FREEC WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME; ``` 这两个查询都用于查看表空间的使用情况，包括总空间、已使用空间、剩余空间以及空间利用率。 通过掌握这些SQL查询，数据库管理员可以实时监控Oracle数据库的状态，找出可能存在的问题，如空间不足、性能瓶颈等，并据此进行相应的调整和优化。此外，对于长时间运行的SQL语句和消耗资源的进程，可以结合`v$session`视图来追踪和分析，以进一步提升系统效率。例如，查询等待事件、CPU使用率高的会话等。