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