Obtain the number of rows in each subpartition of a specified partition table in batches!
Otherwise, manual query is too troublesome!
--- View the number of rows in each sub-partition of the specified partition table
Create or replace procedure show_tabparcnt
(Partiton_tablename in user_tab_partitions.table_name % type) -- input parameter
As
TYPE cur is ref cursor return user_tab_partitions % RowType; -- declares that the cursor type is ref
A cur; -- declare the variable as the ref cursor type
Re number;
Osname user_tab_partitions % rowtype;
Begin
Open a for select * from user_tab_partitions where table_name = upper (partiton_tablename );
Loop
Fetch a into osname;
Exit when a % notfound;
Execute immediate 'select count (*) from' | upper (partiton_tablename) | 'partition ('| osname. partition_name | ')'
Into re;
Dbms_output.put_line ('user partition table' | upper (partiton_tablename) | 'partition '| osname. partition_name | ':' | re | ';');
End loop;
Close;
End;
/
Exec show_tabparcnt ('orderlog ');
This article is from "record bit by bit !" Blog, please be sure to keep this source http://ocpyang.blog.51cto.com/3401739/1194457