Oracle DBA常用SQL语句

Oracle DBA在日常工作中会频繁使用SQL语句来管理和监控数据库。以下是一些常见的Oracle DBA SQL命令，它们涵盖了表空间、数据文件、回滚段、控制文件、日志文件、会话信息、字符集、SQL优化方式以及系统状态等多个方面的内容。 1. **查看表空间及大小**： 使用`SELECT`语句从`dba_tablespaces`和`dba_data_files`视图获取表空间的名称和大小。这有助于DBA了解数据库的存储分布和容量规划。 ```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. **查看数据文件**： 通过`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. **查看回滚段**： 回滚段是存储事务回滚信息的地方。下面的SQL查询显示了回滚段的名称、表空间、状态以及初始和下一个扩展的信息。 ```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语句，可以使用`v$rollname`、`v$transaction`、`v$session`和`v$sqltext`视图。 ```sql SELECT d.sql_text, a.name FROM v$rollname a, v$transaction b, v$session c, v$sqltext d WHERE a.usn=b.xidusn AND b.addr=c.taddr AND c.sql_address=d.address AND c.sql_hash_value=d.hash_value AND a.usn=1; ``` 5. **查看控制文件**： 控制文件记录了数据库的重要元数据。使用`v$controlfile`视图可获取其详细信息。 ```sql SELECT * FROM v$controlfile; ``` 6. **查看日志文件**： 日志文件用于记录数据库的更改。通过`v$logfile`视图可以查看这些文件。 ```sql COL member FORMAT a50 SELECT * FROM v$logfile; ``` 7. **查看SID和Serial#**： `v$session`视图提供当前SQL*PLUS会话的SID（系统标识符）和Serial#（序列号）。 ```sql SELECT sid, serial#, status FROM v$session WHERE audsid=userenv('sessionid'); ``` 8. **查看字符集**： 使用`USERENV`函数确定数据库和会话的字符集。 ```sql SELECT userenv('language'), userenv('LANG') FROM dual; ``` 9. **SQL优化方式**： 判断SQL优化器使用的是RBO（Rule-Based Optimization）还是CBO（Cost-Based Optimization），可以通过解释计划并检查`PLAN_TABLE`中的相关信息。 ```sql SELECT decode(nvl(position,-1),-1,'RBO',1,'CBO') FROM plan_table WHERE id=0; ``` 10. **查看最新SCN号**： System Change Number (SCN)是Oracle数据库的全局时间戳。以下查询返回当前系统中的最大SCN。 ```sql SELECT MAX(ktuxescnw * POWER(2,32) + ktuxescnb) FROM x$ktuxe; ``` 11. **查找TRACE文件**： 通过以下SQL语句可以定位到Oracle的TRACE文件，这对于故障排查和性能分析非常有用。 ```sql SELECT u_dump.value || '/' || instance.value || '_ora_' || v$process.spid || nvl2(v$process.traceid, '_' || v$process.traceid, null) || '.trc' "Trace File" FROM v$parameter u_dump, v$parameter instance, v$process, v$session WHERE u_dump.name = 'user_dump_dest' AND instance.name = 'instance_name' AND v$session.audsid = sys_context('userenv','sessionid'); ``` 这些SQL语句是Oracle DBA进行日常管理和维护时的必备工具，它们可以帮助DBA有效地监控数据库的状态，优化性能，并解决可能出现的问题。通过熟练掌握这些查询，DBA可以更好地确保Oracle数据库的稳定运行。