An Oracle database state check Synthesis script

----Query the physical file for offline

Select Name, Bytes, status from v$datafile where status = ' OFFLINE ';

----Query failed objects

SELECT owner, object_name, object_type
From Dba_objects
WHERE status = ' INVALID '
and owner not in (' SYS ', ' SYSTEM ');

----View redo Information

SELECT * from V$log;

----View Redo File status

SELECT * from V$logfile;

----View constraints on disable

SELECT owner, Constraint_name, TABLE_NAME, constraint_type, status
From Dba_constraints
WHERE status = ' DISABLED '
and Constraint_type in (' P ', ' U ', ' C ', ' O ', ' R ', ' V ')
and owner not in (' SYS ', ' SYSTEM ');

--View PGA usage

Select S.username username,s.serial# serial,se.sid,n.name,
Max (Se.value) maxmem
From V$sesstat SE,
V$statname N,
V$session s
where n.statistic# = se.statistic#
and S.sid=se.sid
And s.username is not NULL
GROUP BY n.name,se.sid,s.username,s.serial#
Order by 2;

Statements that do not use binding variables in--share pool

Select D.plan_hash_value plan_hash_value,


d.execnt execnt,


A.hash_value Hash_value,


A.sql_text Sql_text


from V$sqltext A,


(select Plan_hash_value, Hash_value, execnt


from (select C.plan_hash_value,


B.hash_value,


c.execnt,


rank (partition by C.plan_hash_value ORDER by B.hash_value) as Hashrank


from V$sql B,


(SELECT COUNT (*) as execnt, Plan_hash_value


from V$sql


Where Plan_hash_value <> 0


GROUP BY Plan_hash_value


having Count (*) > 10


ORDER BY Count (*) desc) C


where b.plan_hash_value = C.plan_hash_value


GROUP by C.plan_hash_value, B.hash_value, c.execnt)


where Hashrank <= 3) d


where a.hash_value = D.hash_value


ORDER BY d.execnt Desc, A.hash_value, a.piece;

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

--View all scan and full index scan times

Select Name, value
From V$sysstat
where name in
(' table scans (short tables) ', ' table scans (long tables) ',
' Index fast full scans ',
' Index fast full scans (rowID ranges) ',
' Index fast full scans (direct read) ', ' table scans (rowID ranges) ',
' Table scans (cache partitions) ', ' table scans (direct read) ',
' Table scan blocks gotten ');

--View Archive frequency

SELECT


to_char (first_time, ' mm/dd ') Day


--, to_char (first_time, ' yyyy/mm/dd ') DAY2


, SUM (DECODE (To_char (first_time, ' HH24 '), ' ', 1,0 ') H00


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H01


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H02


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H03


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H04


, SUM (DECODE (To_char (first_time, ' HH24 '), ' a ', 1,0)) H05


, SUM (DECODE (To_char (first_time, ' HH24 '), ' a ', 1,0)) H06


, SUM (DECODE (To_char (first_time, ' HH24 '), ' modified ', 1,0)) H07


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H08


, SUM (DECODE (To_char (first_time, ' HH24 '), ' a ', 1,0)) H09


, SUM (DECODE (To_char (first_time, ' HH24 '), ' ten ', 1,0)) H10


, SUM (DECODE (To_char (first_time, ' HH24 '), ' one ', 1,0)) H11


, SUM (DECODE (To_char (first_time, ' HH24 '), ' a ', 1,0)) H12


, SUM (DECODE (To_char (first_time, ' HH24 '), ' the ', 1,0)) H13


, SUM (DECODE (To_char (first_time, ' HH24 '), ' ', 1,0 ') H14


, SUM (DECODE (To_char (first_time, ' HH24 '), ' ', 1,0 ') H15


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H16


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H17


, SUM (DECODE (To_char (first_time, ' HH24 '), ' a ', 1,0)) H18


, SUM (DECODE (To_char (first_time, ' HH24 '), ', 1,0 ') H19


, SUM (DECODE (To_char (first_time, ' HH24 '), ' m ', 1,0)) H20


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H21


, SUM (DECODE (first_time, ' HH24 '), ' To_char ', 1,0)) H22


, SUM (To_char (first_time, ' HH24 '), ' DECODE ', 1,0)) H23


, COUNT (*) | | ('|| Trim (To_char sum (blocks*block_size)/1024/1024/1024, ' 99,999.9 ') | | G) ' Total


from


(select max (blocks) Blocks,max (block_size) Block_size,max first_time first_time (
)

from


V$archived_log a


where Completion_time > Sysdate-&day


and dest_id = 1


GROUP BY sequence#


   )


GROUP BY To_char (First_time, ' mm/dd '), To_char (First_time, ' yyyy/mm/dd ')


ORDER by To_char (First_time, ' yyyy/mm/dd ') desc;