|
ORACLE数据库管理员的职责(39) -- analyze5pct.sql
-- To analyze tables and indexes quickly, using a 5% sample size
-- (do not use this script if you are performing the overnight
-- collection of volumetric data)
-- 11/30/98
BEGIN
dbms_utility.analyze_schema ( '&OWNER', 'ESTIMATE', NULL, 5 ) ;
END ;
/
3. nr_extents.sql
-- nr_extents.sql
-- To find out any object reaching <threshold>
-- extents, and manually upgrade it to allow unlimited
-- max_extents (thus only objects we *expect* to be big
-- are allowed to become big)
-- 11/30/98
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 e , dba_segments 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
4. spacebound.sql
-- spacebound.sql
-- To identify space-bound objects. If all is well, no rows are returned.
-- If any space-bound objects are found, look at value of NEXT extent
-- size to figure out what happened.
-- Then use coalesce (alter tablespace <foo> coalesce ASPectratio="t" v:ext="edit">.
|
