set termout off
store set sqlplus_settings replace

set serveroutput on size 1000000
set linesize 120
set pagesize 120

set verify off
set head off
ttitle off
btitle off

clear columns
clear breaks

PROMPT
PROMPT ###################################################################### 
PROMPT 
PROMPT --  Name          : listExtentsInfo.sql
PROMPT     
PROMPT --  Copyright (c) DTA Inc., All Rights Reserved (2003)
PROMPT 
PROMPT --  Description   : This SQL Plus script lists extents for segments. It can list 
PROMPT                     segments in all schemas which have more than 1 extent or you can 
PROMPT                     specify the schema and a threshold for extents.
PROMPT 
PROMPT --  Input(s)     : None
PROMPT 
PROMPT --  Output       : The output is in a file named extentsInfo_<DBNAME>_<HOST>.lst
PROMPT 
PROMPT --  Note         : Run this script while connected as the SYS user
PROMPT ######################################################################
PROMPT 

column  segment_name format a25           heading "Segment|Name" word_wrap
column  segment_type format a18           heading "Segment|Type" 
column  owner        format a10           heading "Owner"        wrap
column  pct_increase format 999           heading "Percent|Increase" 
column  extents      format 9999          heading "Extents" 
column  next_extent  format 999,999,999   heading "Next" 
column  initial_extent format 999,999,999 heading "Initial" 
column  size         format 99999,999.99  heading "Size (MB)"

-- Get database name and store in variable
column name new_value s_dbname
SELECT rtrim(name) name FROM v$database;

-- Get today's date
column today new_value s_curDate
SELECT to_char(sysdate, 'Month DD, YYYY') today FROM dual;

-- Get host name and store in variable
column host_name new_value s_machine
SELECT host_name from v$instance;

set termout on

PROMPT The database has the following schema owners : 
PROMPT
-- Get list of segment owners
select distinct(owner) "schema owner" from dba_segments
 where owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP')
 order by owner;

-- allow user name to be entered.  NULL/return key is for all locks/users
accept u_name prompt 'Enter the name of schema owner to show extents for (return for all owners) : ' 
prompt
accept extentsMax NUMBER default '1' prompt 'Enter number of extents threshold (return for all greater than 1) : '

--set termout off
set feedback off

set head on

spool extentsInfo_&s_dbname._&s_machine

ttitle left &s_curDate -
       center "List of Extents for Tables/Indexes By Owner for "  &s_dbname " on " &s_machine skip 1 -
       center "Extents Threshold for this report are greater than: " &extentsMax skip 2

-- Get Extent information
select owner, 
       segment_name, 
       segment_type, 
       extents, 
       initial_extent, 
       next_extent, 
       pct_increase, 
       bytes/(1024*1024) "size" 
from dba_segments
where owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'OUTLN', 'DBSNMP')
  and nvl(owner,1) like upper(nvl('&&u_name','%'))
  and extents > nvl(&&extentsMax,1)
order by extents desc, owner, segment_name;

spool off

ttitle off
btitle off
clear columns
clear breaks
set feedback on
set termout on
set verify on 

PROMPT
PROMPT -- If file is empty, no extent information was found for the database &s_dbname
PROMPT --                or no extent information was found for the username &u_name
PROMPT
PROMPT
PROMPT Output file extentsInfo_&s_dbname._&s_machine..LST produced
PROMPT

undefine u_name
undefine extentsMax

@sqlplus_settings