Partition Table Info

var h varchar2(30)
 begin :h := 'XXXX'; end;  ---- table name here!!!!
/

 set linesize 300 pagesize 300
col TABLE_NAME for a20
select
table_name,
   partitioned,   -- 'YES'
   num_rows,
   blocks,
 LAST_ANALYZED 
from
   dba_tables
where 1=1
  -- owner      = 'XXX' and
 and  table_name = :h
;



col SUBOBJECT_NAME for a20
col OBJECT_NAME for a20
col OWNER for a20
SELECT o.owner
     , o.object_name
     , o.subobject_name
     , tcp.part# AS partition_number
     , DENSE_RANK() OVER (PARTITION BY o.object_name ORDER BY tcp.part#) AS dense_rank_by_object
     , DENSE_RANK() OVER (PARTITION BY o.owner, o.object_name ORDER BY tcp.part#) AS dense_rank_by_owner_object
FROM   dba_objects o
JOIN   sys.tabcompart$ tcp
ON     tcp.obj# = o.object_id
WHERE 1=1
--and   o.owner IN ('U1','U2')
AND    o.object_name = :h
ORDER BY 1,2,3;



col TABLE_NAME for a20
SELECT S.TABLE_NAME, S.TABLESPACE_NAME,COUNT(DISTINCT S.PARTITION_NAME) "num partitions", COUNT(DISTINCT S.SUBPARTITION_NAME) "num subpartitions"
FROM DBA_TAB_SUBPARTITIONS S 
WHERE S.TABLE_NAME=:h
GROUP BY S.TABLE_NAME, S.TABLESPACE_NAME;


col SUBPARTITION_NAME for a20
col PARTITION_NAME for a20
SELECT P.PARTITION_NAME, P.SUBPARTITION_NAME, S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME=:h
--AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
ORDER BY P.PARTITION_NAME, P.SUBPARTITION_NAME, S.BYTES DESC;


col SEGMENT_NAME  for a20
col "Subpartition name" for a20
SELECT  S.SEGMENT_NAME,S.PARTITION_NAME "Subpartition name", S.SEGMENT_TYPE,S.TABLESPACE_NAME,P.COMPRESSION,P.COMPRESS_FOR, P.NUM_ROWS, P.LAST_ANALYZED,ROUND(S.BYTES/1024/1024/1024,1) "gb" 
FROM DBA_SEGMENTS S JOIN DBA_TAB_SUBPARTITIONS P
ON (S.SEGMENT_NAME = P.TABLE_NAME)
WHERE S.SEGMENT_NAME=:h
--AND   S.OWNER = 'SCOTT'
AND S.PARTITION_NAME = P.SUBPARTITION_NAME
--AND P.PARTITION_NAME ='SYS_P14675'
ORDER BY S.BYTES DESC;

select
table_name,
   composite,
   partition_position,
   partition_name,
   subpartition_count,
   high_value,
   num_rows,
   blocks
   high_value_length
from
   dba_tab_partitions
where 1=1
  -- table_owner  = 'RENE'                
and   table_name   = :h
;


alter session set nls_date_format='dd-mm-yyyy hh24:mi';

set linesize 300 pagesize 300
col PARTITION_NAME   for a20
col SUBPARTITION_NAME  for a20
col TABPAR_HIGH_VALUE  for a20
col SUBPAR_HIGH_VALUE  for a20
col TABPAR_HIGH_VALUE for a45
col TABLE_NAME 		for a20
select
subpar.table_name,
   subpar.partition_name,
   subpar.subpartition_name,
  -- tabpar.high_value             tabpar_high_value,
  -- subpar.high_value             subpar_high_value,
  -- subpar.num_rows               subpar_num_rows,
  -- subpar.blocks                 subpar_blocks,
  -- tabpar.high_value_length      tabpar_high_value_length,
  -- subpar.high_value_length      subpar_high_value_length,
   subpar.partition_position,
   subpar.subpartition_position,
tabpar.LAST_ANALYZED,
subpar.LAST_ANALYZED
from
   dba_tab_partitions    tabpar     left join
   dba_tab_subpartitions subpar on
       tabpar.table_owner    = subpar.table_owner     and
       tabpar.table_name     = subpar.table_name      and
       tabpar.partition_name = subpar.partition_name
where 1=1
 --and  subpar.table_owner = 'XXX' 
and  subpar.table_name  = :h
order by   subpar.partition_position,  subpar.subpartition_position;





set linesize 400
col OWNER for a20
col TABLE_NAME for a20
col COL_PART_NAM for a20
col INTERVAL_SUBPARTITION for a20
col COL_SUBPART_NAM for a20
col INTERVAL for a26
select
   tab.owner,
   tab.table_name,
   tab.num_rows,
   tap.partitioning_type,
   tap.subpartitioning_type,
   pkc.column_name              col_part_nam,
   pkc.column_position          col_part_pos,
   tap.interval,
   psc.column_name              col_subpart_nam,
   psc.column_position          col_subpart_pos,
   tap.interval_subpartition,
   tap.autolist,
   tap.autolist_subpartition,
   tap.partitioning_key_count,
   tap.subpartitioning_key_count,
   tap.partition_count,
   tap.def_subpartition_count
from
   dba_tables                tab                                                 left join
   dba_part_tables           tap on tab.owner           = tap.owner       and
                                    tab.table_name      = tap.table_name         left join
   dba_part_key_columns      pkc on tab.owner           = pkc.owner       and
                                    tab.table_name      = pkc.name               left join
   dba_subpart_key_columns   psc on tab.owner           = psc.owner       and
                                    tab.table_name      = psc.name          
where 1=1
-- tab.owner not in ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'CTXSYS', 'APPQOSSYS', 'WMSYS', 'XDB')
  -- tab.owner ='xx'
and tab.table_name=:h
order by   tap.def_subpartition_count desc nulls last
;






SET LINES 333 PAGES 222
COL OWNER FOR A11
COL TABLE_NAME FOR A15
COL GLOBAL_STATS FOR A13
 
SELECT OWNER,TABLE_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'DD-MON-YYYY:HH24:MI:SS') LAST_ANALYZED,GLOBAL_STATS,STATUS FROM DBA_TABLES 
WHERE 1=1
-- OWNER='TBLUSR' 
AND TABLE_NAME=:h 
ORDER BY OWNER,TABLE_NAME,LAST_ANALYZED;




SET LINES 333 PAGES 222
COL OWNER FOR A11
COL TABLE_NAME FOR A15
COL GLOBAL_STATS FOR A13
COL PARTITION_NAME FOR A15
COL TABLE_OWNER FOR A15
 
SELECT TABLE_OWNER,TABLE_NAME, PARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'DD-MON-YYYY:HH24:MI:SS') LAST_ANALYZED,GLOBAL_STATS FROM DBA_TAB_PARTITIONS 
WHERE 1=1
--TABLE_OWNER='TBLUSR' 
AND TABLE_NAME=:h
ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME,LAST_ANALYZED;



SET LINES 333 PAGES 222
COL OWNER FOR A11
COL TABLE_NAME FOR A23
COL GLOBAL_STATS FOR A13
COL PARTITION_NAME FOR A15
COL SUBPARTITION_NAME FOR A19
COL TABLE_OWNER FOR A15
 
SELECT TABLE_OWNER,TABLE_NAME, PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,TO_CHAR(LAST_ANALYZED,'DD-MON-YYYY:HH24:MI:SS') LAST_ANALYZED,GLOBAL_STATS FROM DBA_TAB_SUBPARTITIONS 
WHERE  1=1
--TABLE_OWNER='TBLUSR'
AND TABLE_NAME=:h
--AND ROWNUM<11
 ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,LAST_ANALYZED;


                                                                                                                                             
set verify off
COL owner       FORMAT a15
COL table_name  FORMAT a40
COL STALE_STATS FORMAT a10

prompt 
prompt +------------+
prompt |Stale Stats |
prompt +------------+
prompt 

-- Process
SELECT OWNER,
      TABLE_NAME,
      to_char(LAST_ANALYZED,'yyyy.mm.dd hh24:mi') as LAST_ANALYZED,
      STALE_STATS 
FROM DBA_TAB_STATISTICS 
WHERE 1=1
-- STALE_STATS='YES'
--AND owner NOT IN('SYS','SYSTEM') 
--AND owner  IN('ODS')
and TABLE_NAME=:h
ORDER BY owner,table_name;






select
   subpar.partition_name,
   subpar.subpartition_name,
   tabpar.high_value             tabpar_high_value,
   subpar.high_value             subpar_high_value,
   subpar.num_rows               subpar_num_rows,
   subpar.blocks                 subpar_blocks,
   tabpar.high_value_length      tabpar_high_value_length,
   subpar.high_value_length      subpar_high_value_length,
   subpar.partition_position,
   subpar.subpartition_position
from
   dba_tab_partitions    tabpar     left join
   dba_tab_subpartitions subpar on
       tabpar.table_owner    = subpar.table_owner     and
       tabpar.table_name     = subpar.table_name      and
       tabpar.partition_name = subpar.partition_name
where 1=1
  -- subpar.table_owner = :h
and subpar.table_name  = :h
order by
   subpar.partition_position,
   subpar.subpartition_position;





*********************

accept x_owner prompt "Owner : "
accept x_name prompt "Name : "

declare
ln_no_of_parts number := 0;
ls_owner varchar2( 100 ) := upper( '&x_owner' );
ls_name varchar2( 100 ) := upper( '&x_name' );

cursor c_parts is
select unique owner, name, object_type
from dba_part_key_columns
where owner like '%' || ls_owner || '%'
and name like '%' || ls_name || '%'
order by owner, object_type;

cursor c_part_key_columns( x_owner in varchar2, x_table_name in varchar2 ) is
select *
from dba_part_key_columns
where owner = x_owner
and name = x_table_name
order by column_position;

ls_high_val varchar2( 10000 );

cursor c_seg_bytes( x_owner in varchar2, x_segment in varchar2, x_partition in varchar2 ) is
select *
from dba_segments
where owner = x_owner
and segment_name = x_segment
and partition_name = x_partition;
lt_seg_bytes dba_segments%rowtype;

-- ---------------------------------------------------------------------------------
procedure p_show_table( x_owner in varchar2, x_table_name in varchar2 ) is

ls_warning varchar2( 100 ) := '';
ls_seg_bytes varchar2( 100 ) := '';

cursor c_part_tables( x_owner in varchar2, x_table_name in varchar2 ) is
select *
from dba_part_tables
where owner = x_owner
and table_name = x_table_name;

cursor c_tab_partitions( x_owner in varchar2, x_table_name in varchar2 ) is
select *
from dba_tab_partitions
where table_owner = x_owner
and table_name = x_table_name
order by partition_position;

begin
for c_pt in c_part_tables( x_owner, x_table_name ) loop

dbms_output.put_line( 'Partition Type : ' || c_pt.partitioning_type );
dbms_output.put_line( 'Partition Count : ' || c_pt.partition_count );

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Columns : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Obj Type', 11 ) || ' ' || 'Column Name' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------', 11 ) || ' ' || '-----------' );
for c_pkc in c_part_key_columns( c_pt.owner, c_pt.table_name ) loop
dbms_output.put_line( to_char( c_pkc.column_position, '999' ) || ' ' ||
rpad( c_pkc.object_type, 11 ) || ' ' ||
c_pkc.column_name );

end loop;

if c_pt.partitioning_type <> 'HASH' then
for c_p in c_tab_partitions( c_pt.owner, c_pt.table_name ) loop
ls_high_val := c_p.high_value;
-- if the high_value exceeds 255 characters procedure bigt will be required
-- e.g. bigt( chr(10) || 'High Value : ' || c_p.partition_position || chr(10) || trim( ls_high_val ) );
dbms_output.put_line( 'High Value : ' || c_p.partition_position || chr(9) || trim( ls_high_val ) );
end loop;
end if;

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Partitions : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Partition Name', 30 ) || ' ' || rpad( 'Tablespace', 30 ) || ' Bytes (Mb)' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------------', 30 ) || ' ' || rpad( '----------', 30 ) || ' ----------' );

for c_p in c_tab_partitions( c_pt.owner, c_pt.table_name ) loop
if c_p.subpartition_count > 0 then
ls_warning := ' Warning : sub-partitions attached';
ls_seg_bytes := '';
else
ls_warning := '';
open c_seg_bytes( c_pt.owner, c_pt.table_name, c_p.partition_name );
fetch c_seg_bytes into lt_seg_bytes;
close c_seg_bytes;
ls_seg_bytes := to_char( lt_seg_bytes.bytes/( 1024*1024), '999,999,999' ) || 'Mb';
end if;
dbms_output.put_line( to_char( c_p.partition_position, '999' ) || ' ' ||
rpad( c_p.partition_name, 30 ) || ' ' ||
rpad( c_p.tablespace_name, 30 ) ||
ls_warning ||
ls_seg_bytes );
end loop;

end loop;
end;
-- ---------------------------------------------------------------------------------

procedure p_show_index( x_owner in varchar2, x_index_name in varchar2 ) is

ls_warning varchar2( 100 ) := '';
ls_seg_bytes varchar2( 100 ) := '';

cursor c_part_indexes( x_owner in varchar2, x_index_name in varchar2 ) is
select *
from dba_part_indexes
where owner = x_owner
and index_name = x_index_name;

cursor c_ind_partitions( x_owner in varchar2, x_index_name in varchar2 ) is
select *
from dba_ind_partitions
where index_owner = x_owner
and index_name = x_index_name
order by partition_position;

begin
for c_pt in c_part_indexes( x_owner, x_index_name ) loop
dbms_output.put_line( 'Partition Type : ' || c_pt.partitioning_type );
dbms_output.put_line( 'Partition Count : ' || c_pt.partition_count );

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Columns : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Obj Type', 11 ) || ' ' || 'Column Name' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------', 11 ) || ' ' || '-----------' );
for c_pkc in c_part_key_columns( c_pt.owner, c_pt.index_name ) loop
dbms_output.put_line( to_char( c_pkc.column_position, '999' ) || ' ' ||
rpad( c_pkc.object_type, 11 ) || ' ' ||
c_pkc.column_name );

end loop;

if c_pt.partitioning_type <> 'HASH' then
for c_p in c_ind_partitions( c_pt.owner, c_pt.index_name ) loop
ls_high_val := c_p.high_value;
-- if the high_value exceeds 255 characters procedure bigt will be required
-- e.g. bigt( chr(10) || 'High Value : ' || c_p.partition_position || chr(10) || ls_high_val );
dbms_output.put_line( chr(10) || 'High Value : ' || c_p.partition_position || chr(10) || ls_high_val );
end loop;
end if;

dbms_output.put_line( ' ' );
dbms_output.put_line( 'Partitions : ' );
dbms_output.put_line( ' Pos' || ' ' || rpad( 'Partition Name', 30 ) || ' ' || rpad( 'Tablespace', 30 ) || ' Bytes (Mb)' );
dbms_output.put_line( ' ---' || ' ' || rpad( '--------------', 30 ) || ' ' || rpad( '----------', 30 ) || ' ----------' );

for c_p in c_ind_partitions( c_pt.owner, c_pt.index_name ) loop
if c_p.subpartition_count > 0 then
ls_warning := ' Warning : sub-partitions attached';
ls_seg_bytes := '';
else
ls_warning := '';
open c_seg_bytes( c_pt.owner, c_pt.table_name, c_p.partition_name );
fetch c_seg_bytes into lt_seg_bytes;
close c_seg_bytes;
ls_seg_bytes := to_char( lt_seg_bytes.bytes/( 1024*1024), '999,999,999' ) || 'Mb';
end if;
dbms_output.put_line( to_char( c_p.partition_position, '999' ) || ' ' ||
rpad( c_p.partition_name, 30 ) || ' ' ||
rpad( c_p.tablespace_name, 30 ) ||
ls_warning ||
ls_seg_bytes );
end loop;

end loop;
end;
-- ---------------------------------------------------------------------------------

begin

for i_c_parts in c_parts loop
ln_no_of_parts := ln_no_of_parts + 1;
dbms_output.put_line( rpad( '-', 100, '-' ) );
dbms_output.put_line( 'Owner : ' || i_c_parts.owner );
dbms_output.put_line( 'Name : ' || i_c_parts.name );
dbms_output.put_line( 'Type : ' || i_c_parts.object_type );

if trim( i_c_parts.object_type ) = 'TABLE' then
p_show_table( i_c_parts.owner, i_c_parts.name );
elsif trim( i_c_parts.object_type ) = 'INDEX' then
p_show_index( i_c_parts.owner, i_c_parts.name );
else
dbms_output.put_line( 'Type : ' || i_c_parts.object_type || ' not recognised.' );
end if;
dbms_output.put_line( rpad( '-', 100, '-' ) );

end loop;

if ln_no_of_parts = 0 then
dbms_output.put_line( 'No partitioned tables found matching criteria.' );
end if;

end;
/


====