本文主要是介绍oracle常规信息查询脚本，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
==>检查磁盘受限情况SELECT a.table_name, a.next_extent, a.tablespace_name
FROM all_tables a,
( SELECT tablespace_name, max(bytes) as big_chunk
FROM dba_free_space
GROUP BY tablespace_name ) f
WHERE f.tablespace_name = a.tablespace_name
AND a.next_extent > f.big_chunk;
==>查出scheduler日志的情况
select l.owner,l.log_id, l.job_name,decode(l.operation, 'COMPLETED','max num or date','BROKEN', '16','others') "operation", l.log_date
from dba_scheduler_job_log l
where l.operation in ('COMPLETED', 'BROKEN')
and l.status is null
and log_date like (select to_char(sysdate-1, 'dd-MON-yy') from dual) || '%';
==>检查flashbackup区使用情况
Select sum(percent_space_used) "%Used" from v$FLASH_RECOVERY_AREA_USAGE;
select name,space_limit/1024/1024/1024 ,space_used/1024/1024/1024 from v$recovery_file_dest;
--升级版本，更为详细
SELECT substr(name, 1, 30) name, space_limit/1024/1024/1024 AS quota,
space_used/1024/1024/1024 AS used,
space_reclaimable AS reclaimable,
number_of_files AS files
FROM v$recovery_file_dest ;
==>查看FRA的使用信息
select file_type, percent_space_used ,percent_space_reclaimable ,number_of_files
from v$flash_recovery_area_usage;
==>检查索引状态
Select owner, table_name, index_name, status
From dba_indexes --dba_indexes
Where status = 'UNUSABLE'
Order by 1, 2,3;
select index_owner,index_name,partition_name
from dba_ind_partitions
where status='UNUSABLE'
order by 1,2,3;
select index_owner,index_name,partition_name,subpartition_name,tablespace_name
from dba_ind_subpartitions
where status='UNUSABLE'
order by 1,2,3,4;
==>检查进程数量
select inst_id, count(inst_id) from gv$session group by inst_id order by inst_id;
==>查找在五天里因非正常原因的无效所有对象
select owner, object_name, object_type, created, last_ddl_time, status
from dba_objects
where status = 'INVALID'
and last_ddl_time <= (select sysdate from dual)
and last_ddl_time >= (select sysdate - 5 from dual);
==>根据会话找出对应的进程或线程号
SELECT p1.value||'/'||p2.value||'_ora_'||p.spid||'.trc' filename
FROM
v$process p,
v$session s,
v$parameter p1,
v$parameter p2
WHERE p1.name = 'user_dump_dest'
AND p2.name = 'db_name'
AND p.addr = s.paddr
AND s.audsid = USERENV ('SESSIONID');
==>查看低效的SQL语句
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC
==>查看redo日志切换时间
select sequence#,
first_time,
nexttime,
round(((first_time - nexttime) * 24) * 60, 2) diff
from (select sequence#,
first_time,
lag(first_time) over(order by sequence#) nexttime
from v$log_history
where thread# = 1)
order by sequence# desc;
==>查看分区表的最大分区信息
select TABLE_OWNER,table_name,max(partition_name)
from dba_tab_partitions t1
where TABLE_OWNER not in ('SYS','SYSTEM') and table_name in
(select table_name from dba_tables where partitioned='YES' and OWNER not in ('SYS','SYSTEM'))
group by TABLE_OWNER,table_name ;
==>查询某指定分区数据
select * from t_locrecord partition(PAR_2013_11_25);
==>AWR快照信息查询
select instance_number,snap_id,
to_char(begin_interval_time, 'YYYY-MM-DD'),
to_char(end_interval_time, 'YYYY-MM-DD')
from dba_hist_snapshot
order by snap_id;
==>手工生成AWR快照
exec dbms_workload_repository.create_snapshot;
==>查看AWR快照保留时间
select snap_interval,retention from dba_hist_wr_control;
==>生成AWR报告脚本
@?/rdbms/admin/awrrpt.sql
==>统计数据最近七天的每隔一小时的DB TIME情况，利用excel做成折线图进行分析
with sysstat as
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char(begin_interval_time, 'mm-dd hh24:mi') ||
to_char(end_interval_time, ' hh24:mi') date_time,
stat_name,
round((e_value - nvl(b_value, 0)) /
(extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 +
extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 +
extract(minute from(end_interval_time - begin_interval_time)) * 60 +
extract(second from(end_interval_time - begin_interval_time))),
0) per_sec
from sysstat
where (e_value - nvl(b_value, 0)) > 0
and nvl(b_value, 0) > 0;
==>检查数据库的tablespace 的使用率，如果大于50%，看业务要对相应tablespace增加数据文件
select ts.tablespace_name,
(case
when size_info.max_size <= 0 then
size_info.pct_used
else
round((1 - size_info.max_megs_available / size_info.max_size) * 100)
end) pct_used_of_max
from (select a.tablespace_name,
round(a.bytes_alloc / 1024 / 1024) megs_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024) megs_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) megs_used,
round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) pct_free,
100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) pct_used,
round(a.maxbytes / 1048576) max_size,
round((a.maxbytes - a.bytes_alloc + nvl(b.bytes_free, 0)) /
1048576) max_megs_available
from (select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible,
'YES',
f.maxbytes,
'NO',
f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
(select f.tablespace_name, sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
union all
select h.tablespace_name,
round(sum(h.bytes_free + h.bytes_used) / 1048576) megs_alloc,
round(sum((h.bytes_free + h.bytes_used) -
nvl(p.bytes_used, 0)) / 1048576) megs_free,
round(sum(nvl(p.bytes_used, 0)) / 1048576) megs_used,
round((sum((h.bytes_free + h.bytes_used) -
nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_free,
100 - round((sum((h.bytes_free + h.bytes_used) -
nvl(p.bytes_used, 0)) /
sum(h.bytes_used + h.bytes_free)) * 100) pct_used,
round(sum(f.maxbytes) / 1048576) max_size,
round(case
when sum(f.maxbytes) - sum(nvl(p.bytes_used, 0)) > 0 then
(sum(f.maxbytes) - sum(nvl(p.bytes_used, 0))) / 1048576
else
(sum(nvl(p.bytes_used, 0)) - sum(f.maxbytes)) / 1048576
end) max_megs_available
from sys.v_$temp_space_header h,
sys.v_$temp_extent_pool p,
dba_temp_files f
where p.file_id(+) = h.file_id
and p.tablespace_name(+) = h.tablespace_name
and f.file_id = h.file_id
and f.tablespace_name = h.tablespace_name
group by h.tablespace_name) size_info,
sys.dba_tablespaces ts
where ts.tablespace_name = size_info.tablespace_name
and (case
when size_info.max_size <= 0 then
size_info.pct_used
else
round((1 - size_info.max_megs_available / size_info.max_size) * 100)
end) > =50
order by pct_used_of_max desc, size_info.max_megs_available desc;
这篇关于oracle常规信息查询脚本的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
