Once I have to calculate the no. of rows of all tables in a schema. It seems quite tedious to count rows of all the tables one by one as :
SQL>select count(*) from table_name;
The second method is to export the schemas virtually by using the parameter estimate of Datapump. Using this method,we can check the logfile for tables row counts . Even this method is not so efficient because if the schemas size is large then we will take long time. We can export the schemas as
C:\> expdp system/xxxx@noida directory=dpump schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCK
create or replace function table_count (i_table_name varchar2)
return number
as
t_cnt number default 0;
begin
execute immediate 'select count(*) from '||i_table_name into t_cnt;
return t_cnt;
end;
/
show errors
select table_name, table_count(table_name) from user_tables;
drop function table_count;
Hence, this script allows for counting rows in tables without the need for sqlplus or temporary script files.
If we want to check the empty tables i.e; table not having in any rows can be found from the below scripts :
SQL>select count(*) from table_name;
The second method is to export the schemas virtually by using the parameter estimate of Datapump. Using this method,we can check the logfile for tables row counts . Even this method is not so efficient because if the schemas size is large then we will take long time. We can export the schemas as
C:\> expdp system/xxxx@noida directory=dpump schemas=hr logfile=hrlog11.log dumpfile=hr.dmp ESTIMATE=BLOCK
Another option to find table row counts is to use the pl/sql scripts .I found this script from http://www.dba-village.com and is quite efficient and useful. Here is the below script to check the no. of rows in tables.
create or replace function table_count (i_table_name varchar2)
return number
as
t_cnt number default 0;
begin
execute immediate 'select count(*) from '||i_table_name into t_cnt;
return t_cnt;
end;
/
show errors
select table_name, table_count(table_name) from user_tables;
drop function table_count;
Hence, this script allows for counting rows in tables without the need for sqlplus or temporary script files.
If we want to check the empty tables i.e; table not having in any rows can be found from the below scripts :
set termout off
col sql for a120
spool 'C:\checkempty_temp.sql'set pages 0 feed off echo off
select
'select '''||owner ||''' owner,'''||table_name||''' table_name '||chr(10)||
'FROM '||owner||'.'||table_name||' where rownum<2 having count(*)=0; ' sql
from all_tables
where owner not in ('SYS','SYSTEM','OUTLN','WMSYS') and substr(owner,1,4)<>'OPS$'
--and (blocks>0 or last_analyzed is null)
--and owner='XXXX'
--and blocks=0
order by 1;
spool off
set termout on pages 100
@"c:\checkempty_temp.sql"
Enjoy :-)
