Adriel Blog

Oracle Partitioning: useful scripts

Query number of records in each partition of the table:

SET SERVEROUTPUT ON SIZE 100000;
DECLARE
CURSOR c1
IS
SELECT table_name,
partition_name
FROM all_tab_partitions
WHERE table_name = 'MCP_CDR_HIST' order by partition_name asc;

v_sql VARCHAR2(2000);
temp_var NUMBER:=0;
BEGIN
dbms_output.put_line('Start');
FOR rec IN c1
LOOP
EXECUTE immediate 'select count(*) from '||rec.table_name||' partition('||rec.partition_name||')' INTO temp_var;
dbms_output.put_line('The Partition '||rec.partition_name||' of table '||rec.table_name||' has '||temp_var||' rows');
END LOOP;
dbms_output.put_line('END');
END;