oracle.doc

经常会使用到的sql查询1、查看表空间的名称及大小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; ### Oracle SQL查询技巧详解####一、查看表空间的名称及大小在Oracle数据库管理中，了解每个表空间的大小对于优化存储空间至关重要。以下SQL查询语句可以帮助我们快速获取这一信息： ```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; ``` **解析：** 1. **FROM子句**：这里通过`dba_tablespaces`（存储所有表空间的信息）和`dba_data_files`（存储表空间中的数据文件信息）两个视图进行连接。 2. **WHERE子句**：用于匹配两个视图中相同的表空间名称。 3. **GROUP BY子句**：按表空间名称分组结果。 4. **SELECT子句**： - `tablesapce_name`：返回表空间的名称。 - `round(sum(bytes/(1024*1024)),0) as ts_size`：计算每个表空间的数据文件总大小（单位为MB），并四舍五入取整。 ####二、查看表空间物理文件的名称及大小此查询提供了更详细的表空间信息，包括每个物理文件的名称和大小： ```sql select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space from dba_data_files order by tablespace_name; ``` **解析：** 1. **SELECT子句**： - `tablespace_name`：表空间名称。 - `file_id`：数据文件ID。 - `file_name`：数据文件的物理路径。 - `round(bytes/(1024*1024),0) as total_space`：文件大小（单位为MB）。 2. **ORDER BY子句**：按照表空间名称排序。 ####三、查看回滚段名称及大小该查询提供了有关回滚段的信息，包括其初始扩展大小、最大扩展次数等： ```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; ``` **解析：** 1. **FROM子句**：通过`dba_rollback_segs`和`v$rollstat`视图进行连接。 2. **WHERE子句**：用于匹配两个视图中的回滚段ID。 3. **SELECT子句**： - `segment_name`：回滚段名称。 - `tablespace_name`：回滚段所在的表空间名称。 - `status`：回滚段的状态。 - `initial_extent`：回滚段的初始扩展大小（单位为KB）。 - `next_extent`：下一次扩展的大小（单位为KB）。 - `max_extents`：最大扩展次数。 - `curext`：当前已使用的扩展次数。 4. **ORDER BY子句**：按回滚段名称排序。 ####四、查看控制文件是Oracle数据库的重要组成部分，包含数据库的物理结构信息。以下查询可以显示控制文件的位置： ```sql select name from v$controlfile; ``` ####五、查看日志文件对于跟踪数据库活动至关重要，以下查询展示了日志文件的位置： ```sql select member from v$logfile; ``` ####六、查看表空间的使用情况除了查看表空间的总大小外，了解其使用情况同样重要： ```sql select sum(bytes)/(1024*1024) as free_space, tablespace_name from dba_free_space group by tablespace_name; ```此外，还可以使用以下SQL查询来获取表空间的使用百分比： ```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_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 select owner, object_type, status, count(*) count_ from all_objects group by owner, object_type, status; ``` ####八、查看数据库版本确定Oracle数据库的具体版本有助于确保兼容性： ```sql Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)='Oracle'; ``` ####九、查看数据库的创建日期和归档方式此查询提供了关于数据库创建时间以及是否支持归档模式的信息： ```sql Select Created, Log_Mode From 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 SELECT partition_name, 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; ``` ####十二、查看未提交的事务检查当前存在哪些未提交的事务： ```sql select * from v$locked_object; ```以上SQL查询涵盖了Oracle数据库管理中的多个关键方面，从基本的表空间管理到复杂的性能调优，这些工具都是日常工作中不可或缺的一部分。通过熟练掌握这些查询，DBA和开发人员能够更加有效地管理和维护Oracle数据库系统。