More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Oracle DBA Checklist - Weekly Procedures - existext.sql
-- existext.sql
--
-- To check existing extents
--
-- This tells us how many of each object's extents differ in size from
-- the tablespace's default size. If this report shows a lot of different
-- sized extents, your free space is likely to become fragmented. If so,
-- this tablespace is a candidate for reorganizing.
SELECT segment_name, segment_type
, count(*) as nr_exts
, sum ( DECODE ( dx.bytes,dt.next_extent,0,1) ) as nr_illsized_exts
, dt.tablespace_name, dt.next_extent as dflt_ext_size
FROM dba_tablespaces dt, dba_extents dx
WHERE dt.tablespace_name = dx.tablespace_name
AND dx.owner = '&OWNER'
GROUP BY segment_name, segment_type, dt.tablespace_name, dt.next_extent
(Continued on next question...)
