SQL Script Table Extents for Given Owner

Updated: 01-Jun-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

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links