Oracle Row count in schema
Dynamic sql example
execute immediate sql example
set serveroutput on size 1000000
DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- <<<<<<<<<<<<<<<<<<Only show tables with more rows
cursor c1 is select table_name from user_tables order by table_name;
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')|| to_char(t_total_records,'99999999')||' record(s)');
end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/
declare
tbl1 varchar2(40);
sql1 varchar2(2000);
l_cnt varchar2(30);
begin
for i in (select table_name from user_tables) loop
tbl1 := i.table_name ;
sql1 := 'select count(*) from ' || tbl1 ;
execute immediate sql1 into l_cnt ;
dbms_output.put_line(tbl1||': '|| l_cnt);
end loop;
end;
/
BONUS: 0
SALGRADE: 5
MYEMP: 2
EMP: 14
ANUJ_BIG_TABLE: 9999
ANUJ_BIG_TABLE1: 9999
AA: 14
TEST: 0
ANUJ10: 0
MYEMP_WORK: 0
set serveroutput on size 1000000
DECLARE
t_c1_tname user_tables.table_name%TYPE;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 0; -- Only show tables with 0 rows
cursor c1 is select table_name from user_tables where tablespace_name = 'USERS';
BEGIN
t_limit := 0;
open c1;
loop
fetch c1 into t_c1_tname;
exit when c1%NOTFOUND;
t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records = t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||to_char(t_total_records,'99999999')||' record(s)');
end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
close c1;
END;
/
TEST 0 record(s)
ANUJ10 0 record(s)
BONUS 0 record(s)
set serveroutput on size 1000000
declare
t_c1_tname dba_tables.table_name%type;
t_c1_owner dba_tables.owner%type;
t_command varchar2(200);
t_cid integer;
t_total_records number(10);
stat integer;
row_count integer;
t_limit integer := 1; -- <<<<<<<<<<<<<<<<<<Only show tables with more then 1 rows
begin
for i in (select owner,table_name from dba_tables where owner='ANUJ') LOOP
t_limit := 1;
t_command := 'SELECT COUNT(0) FROM '||i.owner||'.'||i.TABLE_NAME;
t_cid := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
stat := DBMS_SQL.EXECUTE(t_cid);
row_count := DBMS_SQL.FETCH_ROWS(t_cid);
DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
if t_total_records > t_limit then
DBMS_OUTPUT.PUT_LINE(rpad(i.TABLE_NAME,55,' ')|| to_char(t_total_records,'99999999')||' record(s)');
end if;
DBMS_SQL.CLOSE_CURSOR(t_cid);
end loop;
end;
/
TEST 10000 record(s)
ANUJ_BIG_TABLE 5119488 record(s)
DEPT 4 record(s)
T_CHILD 12 record(s)
T_PARENT 10 record(s)
TEST9 400000 record(s)
EMP_IND 3670016 record(s)
BV_DEMO 19999 record(s)
TEST_TAB 20000000 record(s)
CUST 900 record(s)
T1 10000 record(s)
set serveroutput on size 100000 feedback off
declare
rs integer;
cur integer;
rp integer;
trs integer;
n integer;
un varchar2(30);
begin
dbms_output.put_line(rpad('Table Name',40)||' Number of Rows Created Date');
dbms_output.put_line(rpad('-',73,'-'));
cur:= dbms_sql.open_cursor;
for t in (select object_name, created from user_objects where object_type='TABLE') loop
dbms_sql.parse(cur,'select count(*) from ' || t.object_name, dbms_sql.v7);
dbms_sql.define_column(cur, 1, rs);
rp:= dbms_sql.execute(cur);
n:=dbms_sql.fetch_rows(cur);
dbms_sql.column_value(cur, 1, rs);
dbms_output.put_line(rpad(t.object_name,48,'.')||rpad(rs,15,'.')||t.created);
end loop;
dbms_sql.close_cursor(cur);
select count(*) into n from tab where tabtype='TABLE';
select user into un from dual;
dbms_output.put_line(rpad('-',73,'-'));
dbms_output.put_line(un||' User contain '||n||' Table(s)');
end;
/
set serveroutput off feedback on feedback 6
Table Name Number of Rows Created Date
-------------------------------------------------------------------------
T2..............................................18.............17-NOV-11
T...............................................7..............18-NOV-11
SALGRADE........................................5..............22-SEP-11
ORDER_LINES.....................................0..............11-NOV-11
ORDERS..........................................0..............11-NOV-11
MYEMP_WORK......................................0..............22-SEP-11
MYEMP...........................................2..............22-SEP-11
EMP.............................................14.............22-SEP-11
DEPT............................................4..............22-SEP-11
CHAINED_ROWS....................................0..............23-NOV-11
BONUS...........................................0..............22-SEP-11
ANUJ_BIG_TABLE1.................................9999...........01-NOV-11
ANUJ_BIG_TABLE..................................9999...........31-OCT-11
ANUJ10..........................................0..............28-SEP-11
AA..............................................14.............04-NOV-11
-------------------------------------------------------------------------
SCOTT User contain 15 Table(s)
col TXT for a60 heading "Table Row Count "
select rpad(table_name,30,' ') || ' ===> ' || extractvalue (xmltype (dbms_xmlgen.getxml ( 'select count(*) cnt from ' || owner ||'.'||table_name ) )
,'//CNT' ) txt from all_tables
where owner=upper('&Owner') ;
Table Row Count
------------------------------------------------------------
TEST11 ===> 2
ANUJ ===> 4
EMP8 ===> 3
DEPT ===> 4
EMP ===> 14
BONUS ===> 0
SALGRADE ===> 5
7 rows selected.
==========
define TABLE_OWNER='XXX'
define TABLE_NAME='AUDIT_LOG'
set serverout on size 1000000 verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name from dba_tab_partitions where table_owner=upper('&TABLE_OWNER') and table_name='&TABLE_NAME'
--and rownum<20
;
begin
dbms_output.put_line('Record Counts for table_name..... ');
dbms_output.put_line('Log file to numrows_part_&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO 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;
end;
/
set verify on
====
define table_owner='SCOTT'
set serverout on size 1000000
set verify off
declare
sql_stmt varchar2(1024);
row_count number;
v_table_name varchar2(40);
cursor get_tab is
select table_name
from dba_tables
where owner=upper('&&TABLE_OWNER');
begin
dbms_output.put_line('Checking Record Counts for table_name');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER..lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab
loop
v_table_name := get_tab_rec.table_name;
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name;
dbms_output.put_line(sql_stmt);
EXECUTE IMMEDIATE sql_stmt INTO row_count;
dbms_output.put_line('Table '||rpad(get_tab_rec.table_name,50)||' '||TO_CHAR(row_count)||' rows.');
END loop;
EXCEPTION
when others then
dbms_output.put_line('Error counting rows for table '||v_table_name);
end;
/
define table_owner='SCOTT'
define TABLE_NAME='SALES_PART'
set serverout on size 1000000
set verify off
declare
sql_stmt varchar2(1024);
row_count number;
cursor get_tab is
select table_name,partition_name
from dba_tab_partitions
where table_owner=upper('&&TABLE_OWNER')
and table_name='&&TABLE_NAME'
;
begin
dbms_output.put_line('Checking Record Counts for table_name');
dbms_output.put_line('Log file to numrows_part_&&TABLE_OWNER.lst ....');
dbms_output.put_line('....');
for get_tab_rec in get_tab loop
BEGIN
sql_stmt := 'select count(*) from &&TABLE_OWNER..'||get_tab_rec.table_name||' partition ( '||get_tab_rec.partition_name||' )';
EXECUTE IMMEDIATE sql_stmt INTO 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;
end;
/
set verify on
=====
define 1 ='ANUJ';
set serveroutput on
declare
p_count number;
begin
for i in (select * from DBA_tab_partitions where table_name='&1') loop
execute immediate 'select count(*) from '||i.TABLE_OWNER||'.'||i.TABLE_NAME|| ' partition('||i.partition_name||')' into p_count;
dbms_output.put_line(i.partition_name||' '||to_char(p_count));
end loop;
end ;
===
with creation date !!!
define 1 ='XXXXXX';
set serveroutput on
declare
p_count number;
begin
for i in (select owner,object_name,subobject_name,created from dba_objects where object_name='&1' and owner= 'RRRR' and object_type='TABLE PARTITION' order by created) loop
execute immediate 'select /*+ parallel(112) */ count(*) from '||i.OWNER||'.'||i.object_name||' partition('||i.subobject_name||')' into p_count;
dbms_output.put_line(to_char(i.created,'dd-mm-yyyy hh24:mi:ss')||' ' ||i.subobject_name||' '||to_char(p_count));
end loop;
end ;
===
define 1 ='XXXX';
set serveroutput on
declare
p_count number;
begin
for i in (select owner,object_name,subobject_name,created from dba_objects where object_name='&1'
and owner= 'XXX'
and object_type='TABLE PARTITION' and created > TRUNC(SYSDATE+1) - INTERVAL '1' YEAR
order by created) loop
execute immediate 'select /*+ parallel(112) */ count(*) from '||i.OWNER||'.'||i.object_name||' partition('||i.subobject_name||')' into p_count;
dbms_output.put_line(to_char(i.created,'dd-mm-yyyy hh24:mi:ss')||' ' ||i.subobject_name||' '||to_char(p_count));
end loop;
end ;
set pagesize 0
select 'prompt PARTITION_NAME-- '||PARTITION_NAME , 'SELECT count(*),MIN("time") min , max("time") MAX from reports.reports partition ('||PARTITION_NAME ||');'from dba_tab_partitions
where 1=1 and TABLE_NAME='XXX'
set pagesize 0
select 'prompt PARTITION_NAME-- '||PARTITION_NAME , 'SELECT count(*),to_char(MIN("time"),''dd-mm-yyyy hh24:mi:ss'') min ,
to_char(max("time"),''dd-mm-yyyy hh24:mi:ss'') MAX from Anuj.test partition ('||PARTITION_NAME ||');'
from dba_tab_partitions where 1=1
and TABLE_NAME='XXX'
/
