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