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/