Partition Table Info

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;
/