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;