SQL Script Table Extents for Given Owner

Updated: 13-Sep-2002
Version: 2.1

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. 

SET PAGES 999 
SET FEEDBACK OFF
SET HEADING ON
SET PAGESIZE 999
SET LINES 79
set trimspool on
SET VERIFY OFF

col segment_type FORMAT a5 heading 'Type'
col segment_name FORMAT A25 HEADING 'Seg Name' 
col partition_name FORMAT A25 HEADING 'Part Name' 
col extents FORMAT 999 HEADING 'CNT'
col ratio format 99.9 heading '%age'

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

BREAK ON REPORT
COMPUTE SUM OF sum(bytes) ON REPORT

spool extentst.lst

SELECT 
 segment_type
,segment_name
,extents
,max_extents
,(extents/max_extents)*100 ratio
,bytes/1024/1024 Mb
FROM dba_segments
WHERE segment_type IN ('TABLE','INDEX')
AND owner = UPPER('&owner')
AND extents >= max_extents*&min_ext
AND max_extents != 2147483645 
order by (EXTENTS/MAX_EXTENTS) desc
/

PROMPT
PROMPT Partitions
PROMPT

col segment_type form a20 heading "Segment Type"

SELECT segment_type
,partition_name
,extents
,(extents/max_extents)*100 ratio
,max_extents
,bytes/1024/1024 Mb
FROM dba_segments
WHERE segment_type IN ('TABLE PARTITION','INDEX PARTITION')
AND owner = UPPER('&owner')
AND extents >= max_extents*&min_ext
AND max_extents != 2147483645 
order by (EXTENTS/MAX_EXTENTS) desc
/

spool off
CLEAR

Related Links

Oracle 8: (SQL Script) Table Extents for Given Owner

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