### Oracle维护常用SQL语句详解
#### 一、查询表空间大小
**1. 查询所有表空间及其总大小**
```sql
SELECT t.tablespace_name,
ROUND(SUM(d.bytes / (1024 * 1024)), 0) AS ts_size
FROM dba_tablespaces t,
dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
```
此查询通过连接`dba_tablespaces`和`dba_data_files`两个视图来获取每个表空间的总大小（单位为MB）。这对于理解整体存储分配情况非常有帮助。
#### 二、查询数据文件信息
**2. 查询所有数据文件及其大小**
```sql
SELECT tablespace_name,
file_id,
file_name,
ROUND(bytes / (1024 * 1024), 0) AS total_space
FROM dba_data_files
ORDER BY tablespace_name;
```
这个查询显示了每个表空间下所有数据文件的基本信息，包括文件ID、文件名及文件总大小，方便管理员了解每个表空间的具体组成。
#### 三、查询回滚段信息
**3. 查询所有回滚段信息**
```sql
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) AS InitialExtent,
(next_extent / 1024) AS NextExtent,
max_extents,
v.cur_ext AS 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;
```
此命令返回所有控制文件的位置信息，对于恢复或迁移数据库时非常有用。
#### 五、查询日志文件信息
**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;
```
这个查询统计了每个表空间中的空闲空间总量，是进行空间管理的重要工具。
**高级查询**
```sql
SELECT A.tablespace_name,
A.bytes AS total,
B.bytes AS used,
C.bytes AS free,
(B.bytes * 100) / A.bytes AS percent_used,
(C.bytes * 100) / A.bytes AS percent_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;
```
这是一个更复杂的查询，用于计算表空间的使用率和空闲率，帮助管理员更好地规划存储资源。
#### 七、查询对象信息
**7. 查询所有数据库对象及其状态**
```sql
SELECT owner,
object_type,
status,
COUNT(*) AS count
FROM all_objects
GROUP BY owner,
object_type,
status;
```
此查询提供了数据库中所有对象的类型、所有者和状态的汇总，对于监控数据库完整性非常重要。
#### 八、查询数据库版本
**8. 查询Oracle数据库版本**
```sql
SELECT version
FROM product_component_version
WHERE SUBSTR(product, 1, 6) = 'Oracle';
```
通过这个简单的查询可以快速确认Oracle数据库的确切版本，以便于后续的升级或补丁应用。
#### 九、查询数据库模式
**9. 查询数据库的日志模式**
```sql
SELECT created,
log_mode,
log_mode
FROM v$database;
```
此查询返回数据库的日志模式信息，包括创建日期和当前的日志模式（例如归档或非归档模式），这对于故障排查和备份策略设计至关重要。
#### 十、查询长时间运行的SQL
**10. 查询长时间运行的SQL操作**
```sql
COLUMN username FORMAT A12
COLUMN opname FORMAT A16
COLUMN progress FORMAT A8
SELECT username,
sid,
opname,
ROUND(sofar * 100 / totalwork, 0) AS '%' AS progress,
time_remaining,
sql_text
FROM v$session_longops,
v$sql
WHERE time_remaining <> 0
AND sql_address = address
AND sql_hash_value = hash_value;
```
这个查询用于识别正在执行且耗时较长的操作，包括SQL语句，对于性能调优很有帮助。
#### 十一、查询分区信息
**11. 查询表的分区信息**
```sql
SELECT partition_name,
high_value,
high_value_length,
tablespace_name,
pct_free,
pct_used,
ini_trans,
max_trans,
initial_extent,
next_extent,
min_extent,
max_extent,
pct_increase,
freelists,
freelist_groups,
logging,
buffer_pool,
num_rows,
blocks,
empty_blocks,
avg_space,
chain_cnt,
avg_row_len,
sample_size,
last_analyzed
FROM dba_tab_partitions
-- WHERE table_name = :tname
-- AND table_owner = :towner
ORDER BY partition_position;
```
这个查询展示了表的分区详细信息，如分区名称、值范围、表空间名等，对管理和调整分区表非常重要。
#### 十二、查询锁定对象
**12. 查询锁定的对象**
```sql
SELECT *
FROM v$locked_object;
```
```sql
SELECT *
FROM v$transaction;
```
这些查询可以用来查看当前被锁定的对象以及正在进行的事务，有助于解决死锁问题。
#### 十三、查询对象详细信息
**13. 查询对象详细信息**
```sql
SELECT p.spid,
s.sid,
s.serial# AS serial_num,
s.username AS user_name,
a.type AS object_type,
s.osuser AS os_user_name
FROM v$process p,
v$session s,
all_objects a
WHERE p.addr = s.paddr
AND s.sql_hash_value = a.hash_value;
```
这个查询结合了进程、会话和对象信息，提供了关于用户、会话和正在执行的操作的详细视图，对于调试和性能分析非常有用。
以上列举了Oracle维护中常用的SQL语句，涵盖了表空间管理、对象信息查询、性能监控等多个方面，是Oracle数据库管理员日常工作中不可或缺的工具。通过熟练掌握这些查询语句，可以有效地提高数据库性能、保障数据安全并简化日常管理任务。
