Forum Stats

  • 3,769,603 Users
  • 2,252,991 Discussions
  • 7,875,117 Comments

Discussions

Categories

Tricky question ... schema table row counts

Mike301
Mike301 Member Posts: 180 Blue Ribbon
edited Jun 5, 2013 10:50AM in General Database Discussions
Hello friends n gurus...

Oracle: 11g
OS: Linux

I have this very tricky questions which I am trying to solve but not able to get definite answers...
I did search on google... otn etc but not luck with my requirement...

Schema statistics are not reliable so want to query dba_tables..

Q.
How to spool all table row count of a particular schema and also display table_name?

A.
I can display count easily in spool but not able to get table name beside count..
e.g.
Table_Name Count
tab1 200
tab2 500
tab3 300

with below I can get count but not able to figure out table_name display in result...
spool runme.sql

select 'select count(*) from '|| owner || '.' || table_name || ';' 
from dba_tables
where owner = 'user1'
order by table_name;

spool off
thanks
mike
Tagged:
· Share on TwitterShare on Facebook

Answers

  • FreddieEssex
    FreddieEssex Member Posts: 757
    edited Jun 5, 2013 10:24AM
    From my script library......to state the obvious, replace <schema_name> with your schema name and run:
    set lines 150 pagesize 0 feed off head off echo off trimspool off verify off
spool db_row_count_<schema_name>.sql
select 'select '||'''' || table_name || ':' ||   ''''|| '||' || ' count(*) '|| 'from ' ||owner||'.'||table_name ||';' from dba_tables where owner = '<schema_name>' order by table_name;
spool off
spool db_row_count_<schema_name>.txt
@db_row_count_<schema_name>.sql
spool off
    · Share on TwitterShare on Facebook
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    SELECT TABLE_NAME, ROW_COUNT FROM USER_TABLES;
    · Share on TwitterShare on Facebook
  • jjk
    jjk Member Posts: 354
    I guess this is what you are looking for:
    select 'select ''' ||owner||'.'||table_name|| ''' TBL_NM, count(*) from '|| owner || '.' || table_name || ';' 
from dba_tables
where owner='SYS'
and table_name='&1';
    output: 
    SQL> select 'select ''' ||owner||'.'||table_name|| ''' TBL_NM, count(*) from '|| owner || '.' || table_name || ';'
from dba_tables
where owner='SYS'
and table_name='&1';  2    3    4
Enter value for 1: CH_ACCT_RPT_STAT_TAB
old   4: and table_name='&1'
new   4: and table_name='CH_ACCT_RPT_STAT_TAB'

'SELECT'''||OWNER||'.'||TABLE_NAME||'''TBL_NM,COUNT(*)FROM'||OWNER||'.'||TABLE_NAME||';'
-----------------------------------------------------------------------------------------------------------------------------------------------------------
select 'SYS.CH_ACCT_RPT_STAT_TAB' TBL_NM, count(*) from SYS.CH_ACCT_RPT_STAT_TAB;

SQL> select 'SYS.CH_ACCT_RPT_STAT_TAB' TBL_NM, count(*) from SYS.CH_ACCT_RPT_STAT_TAB;

TBL_NM                     COUNT(*)
------------------------ ----------
SYS.CH_ACCT_RPT_STAT_TAB          0
    Just modify the query to remove the table_name criteria
    jjk
    · Share on TwitterShare on Facebook
  • Mike301
    Mike301 Member Posts: 180 Blue Ribbon
    edited Jun 5, 2013 10:50AM
    Thanks Freddie & jjk ... that was awesome..

    I looked into all kind of complicated options but this was the simplest and perfect.

    Edited by: 974269 on Jun 5, 2013 7:50 AM
    added jjk for thank you
    · Share on TwitterShare on Facebook
This discussion has been closed.