Oracle数据库经典SQL查询技巧

"Oracle数据库中的常用经典SQL查询方法" 在Oracle数据库管理系统中，SQL查询是管理员进行数据管理和系统监控的关键工具。以下是一些常用的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用于存储事务撤销信息的结构。此查询列出了所有回滚段的名称、表空间、状态，以及初始和下一个扩展的大小（以KB为单位）。 ```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. 查看控制文件 控制文件是Oracle数据库的重要组成部分，记录了数据库的元数据。这个简单的查询可以显示当前数据库的所有控制文件的名称。 ```sql SELECT name FROM v$controlfile; ``` 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_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME; ``` 这些SQL查询是Oracle数据库管理的基础，可以帮助数据库管理员监控和优化系统的性能，确保数据的安全性和可用性。掌握这些查询技巧，将有助于提升数据库管理效率。