Script to Tablespaces For User Objects

SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK OFF
SET PAGES 1000
SET LINES 132

REM
REM || Title : show_tspaceobj.sql
REM ||
REM || Purpose : This script will Tablespaces where user Objects reside.
REM ||
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : sys
REM ||
REM ||


COLUMN sname FORMAT a30 HEADING 'Segment Name' JUSTIFY l TRUNC
COLUMN stype FORMAT a10 HEADING 'Type' JUSTIFY l TRUNC
COLUMN owner FORMAT a10 HEADING 'Owner' JUSTIFY l TRUNC
COLUMN tablespace_name FORMAT a12 HEADING 'Tablespace' JUSTIFY l TRUNC
COLUMN extents FORMAT 9999 HEADING 'Extents' JUSTIFY l TRUNC
COLUMN max_extents FORMAT 9999 HEADING 'Max' JUSTIFY c TRUNC


SELECT segment_name sname,
INITCAP (segment_type) stype,
owner,
tablespace_name,
extents,
next_extent,
max_extents
FROM sys.dba_segments
WHERE OWNER NOT IN ('SYSTEM','SYS')
ORDER BY owner,
segment_type,
segment_name

SPOOL show_tspaceobj.lis
/
SPOOL OFF


SET SERVEROUTPUT ON
SET FEEDBACK ON
SET PAGES 80
This script is provided for educational purposes only. The script has been tested and appears to work as intended. However, you should always test any script before relying on it. No responsibility will be accepted for Lost or damage that may occur from it's use.