-- #############################################################################################
--
-- %Purpose: Show Partition Tables and Indexes (DBA_TAB_PARTITIONS)
--
-- Use:      Needs Oracle DBA Access
--
-- #############################################################################################
--
set feed off;
set pagesize 100000;
set wrap off;
set linesize 200;
set heading on;
set tab on;
set scan on;
set verify off;
--
ttitle left 'Show Partition Tables' -
skip 2

set linesize 200
set pagesize 500
column table_name format a20 heading "Table|Name"
column index_name format a20  heading "Index|Name"
column partition_name format a20  heading "Partition|Name"
column tablespace_name format a10 heading "Tablespace"
column partition_position format 999999 heading "Partition|Position"

break on table_name;

spool show_partition_tables.lst

select table_name,
       partition_name,
       partition_position,
       tablespace_name
  from dba_tab_partitions
  order by table_name,partition_position;

break on index_name;

select index_name,
       partition_name,
       partition_position,
       tablespace_name
  from dba_ind_partitions
  order by index_name,partition_position;

spool off
set feed on echo off termout on pages 24 verify on;
ttitle off;