Oracle数据库经典SQL查询实用集锦

"Oracle数据库系统中的SQL查询是数据库管理员和开发人员日常工作中不可或缺的一部分。本文将探讨几个Oracle中常用的经典SQL查询，以帮助理解和管理数据库的各个层面，包括表空间、回滚段、控制文件、日志文件以及表空间的使用情况。" 1. 查看表空间的名称及大小 这个查询用于获取Oracle数据库中所有表空间的名称以及它们所占用的总大小（以MB为单位）。通过`dba_tablespaces`和`dba_data_files`视图联接，可以获取每个表空间及其对应的磁盘文件大小，然后使用`GROUP BY`对表空间进行分组并计算总大小。 ```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; ``` 2. 查看表空间物理文件的名称及大小 此查询展示每个表空间内所有数据文件的详细信息，包括文件名和文件大小（同样以MB为单位）。通过查询`dba_data_files`视图，可以得到每个表空间内所有数据文件的详细列表。 ```sql SELECT tablespace_name, file_id, file_name, ROUND(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ORDER BY tablespace_name; ``` 3. 查看回滚段名称及大小 回滚段是Oracle用来存储事务回滚信息的数据结构。这个查询展示了每个回滚段的名称、表空间、状态以及初始和最大扩展大小。通过`dba_rollback_segs`和`v$rollstat`视图联合查询，可以获取这些信息。 ```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. 查看控制文件 控制文件记录了数据库的元数据信息，包括表空间、数据文件、重做日志等。查询`v$controlfile`视图可以直接获取控制文件的名称。 ```sql SELECT name FROM v$controlfile; ``` 5. 查看日志文件 重做日志文件用于记录数据库的所有更改。查询`v$logfile`视图可以列出所有活跃的日志文件成员。 ```sql SELECT member FROM v$logfile; ``` 6. 查看表空间的使用情况 最后一个查询提供了关于表空间使用状况的详细报告，包括总大小、已使用空间、剩余空间以及使用率和空闲率。通过`sys.sm$ts_availa`、`sys.sm$ts_usedb`和`sys.sm$ts_freec`视图，可以计算出这些指标。 ```sql 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 A, SYS.SM$TS_USEDB B, SYS.SM$TS_FREEC C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME; ``` 以上查询对于监控和优化Oracle数据库的性能至关重要，它们可以帮助数据库管理员有效地管理存储资源，识别可能的瓶颈，并确保数据库的健康运行。在实际操作中，可以根据需要调整这些查询，以适应特定的环境和需求。