Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 18 June 2012

How can count all tables with rows in a schema

set serveroutput on
declare
row_cnt number;
BEGIN
for i in (select table_name from user_tables) loop
execute immediate 'select count(*) from '|| i.table_name into row_cnt;
dbms_output.put_line('Table sum for '||i.table_name||' is ' ||row_cnt);
end loop;
END;
/


If you are facing the issue "ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes" then use "set serveroutput on size 100000" in place of "set serveroutput on"
By default, you can output 20000 characters, the maximum is 1,000,000. However, If the code excedes the limit you will get the same error. You can use DBMS_OUTPUT.ENABLE(1000000) for PL/SQL block. 
In Oracle 10G R2 you can use this command "set serveroutput on size unlimited" and dbms_output.enable(null) to set it unlimited in PL/SQL block.

0 comments:

Post a Comment