sql checkdb

--SESSION:
select * from gv$RESOURCE_LIMIT where RESOURCE_NAME='sessions';

--DATAFILE
select round((sum(a.bytes_alloc)-sum(nvl(b.bytes_free, 0)))/1024/1024/1024,3)  "used ", 
       round(sum(a.bytes_alloc)/1024/1024/1024,2) "total "
from  (select  f.tablespace_name,
               sum(f.bytes) bytes_alloc
             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 (+);

--TBS
select* from (
select df.tablespace_name "Tablespace",df.bytes/(1024*1024) "Total Size(MB)",
sum(fs.bytes)/(1024*1024) "Free Size(MB)", round(sum(fs.bytes)*100/df.bytes) "% Free",
round((df.bytes-sum(fs.bytes))*100/df.bytes) "% Used"
from dba_free_space fs,
(select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df         
where fs.tablespace_name = df.tablespace_name
group by df.tablespace_name, df.bytes
) ORDER BY 5 DESC;

--archive log
select trunc(completion_time),round(sum(mb)/1024,2)||' G' day_GB from
(select name,completion_time,blocks*block_size/1024/1024 mb from v$archived_log
 where creator='ARCH' AND completion_time>sysdate-3 )
group by trunc(completion_time)
order by 1--SQL01;


select * from dba_jobs
where BROKEN<>'N'
--75956;

select * from dba_objects
where status='INVALID';


--------------------?I/O------------------------------------
select c.* from (
SELECT
    UPPER(b.username)                                       username
  , a.disk_reads                                            disk_reads
  , a.executions                                            executions
  , a.disk_reads / decode(a.executions, 0, 1, a.executions) reads_per_exec
  , a.address
  , a.sql_text || chr(10) || chr(10)                          sql
  , A.MODULE
    , a.last_load_time                                           last_time
 -- , a.sql_fulltext                                          sql
FROM
    sys.gv_$sql a
  , dba_users b
WHERE
      a.parsing_user_id = b.user_id
  AND a.disk_reads > 1000
  AND b.username NOT IN ('SYS','SYSTEM') ) c where c.reads_per_exec>=1000
ORDER BY
    c.reads_per_exec desc;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25583515/viewspace-712961/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/25583515/viewspace-712961/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值