Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Wednesday, 27 June 2012

Script: To Check daily Sizing and current Extent information:

--Script: To analyze Table and indexes
SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME like '%DEFAULT%';
BEGIN
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;
END ;
/
--To find out any object reaching <threshold>  extents
Note: Manually upgrade it to allow unlimited max_extents thus only objects we expect to be big  are allowed to become big.
SELECT e.owner, e.segment_type , e.segment_name , count(*) as nr_extents ,
s.max_extents
, to_char ( sum ( e.bytes ) / ( 1024 * 1024 ) , '999,999.90') as MB
FROM dba_extents@orcl3 e , dba_segments@orcl3 s
WHERE e.segment_name = s.segment_name
GROUP BY e.owner, e.segment_type , e.segment_name , s.max_extents
HAVING count(*) > &THRESHOLD
OR ( ( s.max_extents - count(*) ) < &&THRESHOLD )
ORDER BY count(*) desc;

0 comments:

Post a Comment