SQL Script Table/Index Extents within tablespace

Updated : 1-June-1998
Version : 1.0

Description

This script first gives a list of tablespaces. It then prompts to enter a tablespace name 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. This is a MUST script for all production DBA's. It can easily be amended to be put in a nightly schedule. You can spend a lot of cash on database monitoring utilities to do this for you, but it's very simple !!

Parameters

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

SQL Source

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'

Listing Tablespaces ...

select tablespace_name from dba_tablespaces;

ACCEPT tspace prompt 'Enter Tablespace name: '
ACCEPT min_ext prompt 'Enter Minimum Value of Extents : '

BREAK ON REPORT
COMPUTE SUM OF sum(bytes) ON REPORT
spool extentst.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.tablespace_name = UPPER('&tspace')
GROUP BY segment_type,segment_name,initial_extent,next_extent
HAVING COUNT(*) > &min_ext
ORDER BY segment_type desc,count(*) desc
/

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.tablespace_name = UPPER('&tspace')
GROUP BY segment_type,segment_name,initial_extent,next_extent
HAVING COUNT(*) > &min_ext
ORDER BY segment_type desc,count(*) desc
/
spool off
CLEAR

Return to Index of SQL Scripts

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