SQL Script Count Partition Row Counts

Updated : 30-January-2002
Version : 1.0

Description

Displays each individual table partition row counts.

Parameters

&1 - Table Owner

SQL Source

set serverout on size 1000000
set verify off
spool numrowspart_&&1..lst

declare
w_p_string varchar2(254);
w_cursor_id integer;
w_p_exec integer;
col1 number;
row_count number;
row1 number;
row2 number;

cursor get_tab is
select table_name,partition_name
from dba_tab_partitions
where table_owner=upper('&&1');

begin

dbms_output.put_line('Checking Record Counts for schema &&1 ');
dbms_output.put_line('Log file to numrows_part_&&1.lst ....');
dbms_output.put_line('....');

w_cursor_id:=dbms_sql.open_cursor;

for get_tab_rec in get_tab loop

BEGIN
w_p_string := 'select count(*) col1 from &&1..'||get_tab_rec.table_name
                   ||' partition ( '||get_tab_rec.partition_name||' )'; 

dbms_sql.parse(w_cursor_id,w_p_string,1);

dbms_sql.define_column(w_cursor_id, 1, col1);
w_p_exec := dbms_sql.execute(w_cursor_id);
w_p_exec := dbms_sql.fetch_rows(w_cursor_id);
dbms_sql.column_value (w_cursor_id,1,row_count);

dbms_output.put_line('Table '||rpad(get_tab_rec.table_name
                            ||'('||get_tab_rec.partition_name||')',50)
                             ||' '||TO_CHAR(row_count)||' rows.');

exception when others then
dbms_output.put_line('Error counting rows for table '||get_tab_rec.table_name);

END;

end loop;

dbms_sql.close_cursor(w_cursor_id);

end;
/
set verify on
spool off

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal