### Oracle常用经典SQL查询知识点详解
#### 一、查看表空间的名称及大小
- **SQL语句**：
```sql
select t.tablespace_name, round(sum(d.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;
```
- **知识点解析**：
- 这条SQL语句用于查询Oracle数据库中各个表空间的名称及其总大小（单位为MB）。
- `dba_tablespaces` 视图提供了所有表空间的信息，而 `dba_data_files` 视图则提供了表空间中每个数据文件的详细信息。
- 通过 `where` 子句将两个视图连接起来，并使用 `group by` 对每个表空间进行分组汇总，最后通过 `sum()` 函数计算每个表空间的数据文件总大小。
#### 二、查看表空间物理文件的名称及大小
- **SQL语句**：
```sql
select tablespace_name, file_id, file_name, round(bytes/(1024*1024), 0) total_space
from dba_data_files
order by tablespace_name;
```
- **知识点解析**：
- 此SQL语句可以列出所有表空间中的物理文件信息，包括文件ID、文件名以及文件的总大小（单位为MB）。
- 使用 `dba_data_files` 视图来获取这些信息，并按表空间名称排序。
- 通过 `round()` 函数对文件大小进行四舍五入处理，便于阅读。
#### 三、查看回滚段名称及大小
- **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;
```
- **知识点解析**：
- 该查询展示了所有回滚段的详细信息，包括回滚段名称、所在表空间、状态、初始扩展量等。
- `dba_rollback_segs` 视图提供回滚段的基本信息，而 `v$rollstat` 动态性能视图则提供了当前活动的回滚段统计信息。
- 通过 `segment_id` 和 `usn` 字段进行连接，以获取回滚段的实时状态信息。
#### 四、查看控制文件
- **SQL语句**：
```sql
select name from v$controlfile;
```
- **知识点解析**：
- 此SQL语句用于获取所有控制文件的路径和名称。
- `v$controlfile` 是一个动态性能视图，包含了所有控制文件的信息。
- 控制文件是Oracle数据库的重要组成部分，用于记录数据库的所有物理结构信息，如数据文件、重做日志文件的位置等。
#### 五、查看日志文件
- **SQL语句**：
```sql
select member from v$logfile;
```
- **知识点解析**：
- 通过这条SQL语句可以查询所有在线重做日志文件的成员信息。
- `v$logfile` 动态性能视图包含了所有在线重做日志文件的信息。
- 在线重做日志文件记录了数据库的所有更改操作，对于恢复数据库至关重要。
#### 六、查看表空间的使用情况
- **SQL语句**：
```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语句通过 `dba_free_space` 视图计算每个表空间的空闲空间大小（单位为MB）。
- 第二条SQL语句更进一步地展示了每个表空间的总空间、已使用空间、空闲空间以及相应的百分比。
- 这些信息对于监控表空间使用情况非常重要，有助于预防因表空间满而导致的数据写入失败问题。
#### 七、查看数据库对象
- **SQL语句**：
```sql
select owner, object_type, status, count(*) count_from all_objects
group by owner, object_type, status;
```
- **知识点解析**：
- 此查询可以获取数据库中所有对象的统计信息，包括对象的所有者、类型、状态以及数量。
- `all_objects` 视图包含了所有可访问的对象信息。
- 通过 `group by` 子句按所有者、类型和状态进行分组统计。
#### 八、查看数据库的版本
- **SQL语句**：
```sql
Select version FROM Product_component_version
Where SUBSTR(PRODUCT, 1, 6) = 'Oracle';
```
- **知识点解析**：
- 该SQL语句用于查询当前Oracle数据库的版本信息。
- `Product_component_version` 视图包含数据库的所有组件及其版本信息。
- 通过 `SUBSTR()` 函数筛选出产品名称为 “Oracle” 的版本信息。
#### 九、查看数据库的创建日期和归档方式
- **SQL语句**：
```sql
Select Created, Log_Mode, Log_Mode From V$Database;
```
- **知识点解析**：
- 此查询用于获取数据库的创建日期以及当前的日志模式（是否为归档模式）。
- `V$Database` 动态性能视图包含了有关数据库实例的关键信息。
- 数据库的日志模式对于备份和恢复策略的设计非常重要。
#### 十、捕捉运行很久的SQL
- **SQL语句**：
```sql
column username format a12
column opname format a16
column progress format a8
select username, sid, opname,
round(sofar*100/totalwork, 0)||'%' 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语句用于查找正在执行时间较长的SQL语句及其进度信息。
- `v$session_longops` 视图记录了长时间运行的操作信息，而 `v$sql` 视图则包含了最近执行过的SQL语句信息。
- 通过匹配 `address` 和 `hash_value` 字段，可以关联这两个视图，从而获取长时间运行的SQL语句及其执行状态。
#### 十一、查看数据表的参数信息
- **SQL语句**：
```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;
```
- **知识点解析**：
- 该查询用于获取分区表的详细信息，包括分区名称、所在表空间、存储参数等。
- `dba_tab_partitions` 视图包含了所有分区表的详细信息。
- 可以通过修改 `WHERE` 子句来指定特定的表或分区进行查询。
#### 十二、查看未提交的事务
- **SQL语句**：
```sql
select * from v$locked_object;
```
- **知识点解析**：
- 此SQL语句可以列出所有被锁定的对象及其相关信息。
- `v$locked_object` 动态性能视图记录了当前数据库会话中被锁定的对象信息。
- 查看这些信息有助于理解数据库的并发行为，及时发现并解决问题。
以上SQL查询涵盖了Oracle数据库管理与维护过程中常用的一些关键查询语句，通过对这些查询的理解和应用，可以有效地提升数据库的运维效率。
