【高频SQL】Oracle Database
以前的文章太分散了，所以在整个博客搜索不是很方便，因此，针对不同的数据库，会将一些高频使用的SQL集中放在一篇日志中。
日志：Redo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
select lf.group#, lf.member from v$logfile lf, v$log l where lf.group#=l.group# order by lf.group#; select lf.group#, lf.member from v$logfile lf, v$log l where lf.group#=l.group# and lf.member like '%archive%' order by lf.group#; |
表空间：Tablespace
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
set linesize 400 col tablespace_name for a23 col free_rate for a18 select a.tablespace_name, round(a.total_size) "total_size(mb)", round(a.total_size - b.free_size, 3) "used_size(mb)", round(b.free_size, 3) "free_size(mb)", round((a.total_size - b.free_size) / total_size * 100, 2) || '%' "used_rate" round(b.free_size / total_size * 100, 2) || '%' "free_rate" from ( select tablespace_name, sum(bytes)/1024/1024 total_size from dba_data_files group by tablespace_name ) a, ( select tablespace_name, sum(bytes)/1024/1024 free_size from dba_free_space group by tablespace_name ) b where a.tablespace_name = b.tablespace_name(+) order by free_rate; |
表空间对应的数据文件是否自动扩展：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
set linesize 400 col file_name for a80 col tablespace_name for a20 select t.tablespace_name, d.file_name, d.autoextensible, d.bytes/1024/1024 mb, d.maxbytes/1024/1024 max_mb, d.status from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name order by tablespace_name, file_name; |
数据文件：
查询数据文件，及其创建时间：
|
1 2 3 4 5 6 7 8 9 |
select a.file_name, a.bytes/1024/1024 mb, a.tablespace_name, a.autoextensible, to_char(b.creation_time, 'yyyy-mm-dd hh24:mi:ss') creation_time from dba_data_files a, v$datafile b where a.file_id = b.file# order by creation_time; |
统计不同时间段内的数据文件的增长大小：
按天：
|
1 2 3 4 5 6 7 |
select to_char(b.creation_time, 'yyyy-mm-dd') creation_time, round(sum(bytes)/1024/1024, 3) mb from dba_data_files a, v$datafile b where a.file_id = b.file# group by to_char(b.creation_time, 'yyyy-mm-dd') order by 1; |
按月：
|
1 2 3 4 5 6 7 |
select to_char(b.creation_time, 'yyyy-mm') creation_time, round(sum(bytes)/1024/1024/1024, 3) gb from dba_data_files a, v$datafile b where a.file_id = b.file# group by to_char(b.creation_time, 'yyyy-mm') order by 1; |
查询实例生命周期内是否发生过资源限制的相关情况：
|
1 2 3 4 |
set linesize 2000; col resource_name for a30 select * from v$resource_limit; |
