Thursday, January 11, 2018

How to check for table partitions using PL/SQL

I created the following piece of code a long time ago.
It checks if a table is partitioned or not, and list its partition.
It takes the table name and its owner as parameters, and list it.

If you need a template for a simple script using an anonymous PL/SQL block, I assume it could serve a purpose.

For what it's worth, here it is:
set serveroutput on
DECLARE
  p_table_name  varchar2(100) := '&table_name';
  p_table_owner varchar2(100) := '&owner';
  p_count       number        := 0;
BEGIN
    SELECT count(*) into p_count
    FROM   dba_tab_partitions
    WHERE  table_name   = p_table_name
    AND    table_owner  = p_table_owner;
  IF (p_count = 0) THEN
    dbms_output.put_line('There were no partitions on table ' || p_table_name );
  ELSE
     For l_rec in ( 
     
        select p.owner, p.table_name, t.partition_name, t.TABLESPACE_NAME ,p.partitioning_type
        from  dba_part_tables p, dba_tab_partitions t
        where p.table_name = p_table_name
        and   p.owner = t.TABLE_OWNER
        and   t.TABLE_NAME = p.TABLE_NAME
        )
     LOOP
     dbms_output.put_line(l_rec.partition_name || '    ' || l_rec.tablespace_name || '  ' || l_rec.partitioning_type);
     END LOOP;
  END IF;
END;
/

No comments:

Post a Comment