REM Script to show the structure of partitioned tables

set pages 500;
set lines 300;
set feed off;

–column table_name noprint;
column partition_position noprint;
column high_value format A20;

column partition_name format A22;
column tablespace_name format A15;
column fl format 99;
column flg format 99;

prompt ===================================================================================================

prompt Partitioned tables: general layout

select
table_name,partition_position,partition_name,
tablespace_name,high_value,pct_free,initial_extent,
freelists fl, freelist_groups flg,logging
from dba_tab_partitions
order by table_name,partition_position;

prompt ===================================================================================================

prompt Partitioned tables: usage stats

select
a.table_name,a.partition_position,a.partition_name,
a.num_rows,a.avg_space,a.blocks,a.empty_blocks,a.chain_cnt,
b.extents
–,a.last_analyzed
from dba_tab_partitions a, dba_segments b
where
a.table_owner = b.owner and
a.partition_name = b.partition_name and
a.table_name = b.segment_name
order by table_name,partition_position;

prompt ===================================================================================================

set feed on;