SQL Script Table/Index extents for a given owner

Updated : 1-June-1998
Version : 1.0

Description

This script is very similar to the List all tables/indexes extents in a given tablespace script. The difference being that this list extents detail for a given table owner. It prompts to enter the owner and a minimum value of extents. The script then lists all tables and indexes with the the number of extents greater than the value entered regardless of tablespace. Another MUST script for all production DBA's.

Parameters

&tspace - The table owner to report on
&min_ext - the minimum number of extents

SQL Source

ACCEPT owner prompt 'Enter Table owner : '
ACCEPT min_ext prompt 'Enter Minimum Value of Extents : '

SET PAGES 999
SET FEEDBACK OFF
SET HEADING ON
SET PAGESIZE 999
SET LINES 79
SET VERIFY OFF

COLUMN segment_name FORMAT A25 HEADING 'Segment|Name'
COLUMN iniMb FORMAT 9999.9 HEADING 'Intl(M)'
COLUMN nxtMb FORMAT 9999.9 HEADING 'Next(M)'
COLUMN iniKb FORMAT 9999.9 HEADING 'Intl(K)'
COLUMN nxtKb FORMAT 9999.9 HEADING 'Next(K)'
COLUMN count(*) FORMAT 999 HEADING 'CNT'

BREAK ON REPORT
COMPUTE SUM OF sum(bytes) ON REPORT
spool extents.lst

SELECT segment_name
,initial_extent/(1024*1024) iniMb
,next_extent/(1024*1024) nxtMb
,initial_extent/1024 iniKb
,next_extent/1024 nxtKb
,count(*)
,sum(bytes)
FROM dba_extents, dba_tables
WHERE dba_extents.segment_name = dba_tables.table_name
AND dba_extents.owner = dba_tables.owner
AND dba_tables.owner = UPPER('&owner')
GROUP BY segment_type,segment_name,initial_extent,next_extent
HAVING COUNT(*) > &min_ext
ORDER BY segment_type desc,segment_name asc
/
TTITLE OFF
SET HEADING OFF
SELECT segment_name
,initial_extent/(1024*1024) iniMb
,next_extent/(1024*1024) nxtMb
,initial_extent/1024 iniKb
,next_extent/1024 nxtKb
,count(*)
,sum(bytes)
FROM dba_extents, dba_indexes
WHERE dba_extents.segment_name = dba_indexes.index_name
AND dba_extents.owner = dba_indexes.owner
AND dba_indexes.owner = UPPER('&owner')
GROUP BY segment_type,segment_name,initial_extent,next_extent
HAVING COUNT(*) > &min_ext
ORDER BY segment_type desc,segment_name asc
/
spool off
CLEAR

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal