OraFAQ Forum: SQL & PL/SQL » Row count of all tab's in my DB.

Home » SQL & PL/SQL » SQL & PL/SQL » Row count of all tab's in my DB. (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row count of all tab's in my DB. [message #395401]

Wed, 01 April 2009 05:45

mjkreddy1927
Messages: 20
Registered: February 2007

Junior Member

Hi,

I want Row Count of all the tables in my DB.

select count(*) from ;

this is for count on individual table.just like this i need for all the tables in my DB.
Can any one Helpme out...
i need sql for this...

Any efforts are appreciated

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395402 is a reply to message #395401]

Wed, 01 April 2009 05:53

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

select count(*) from user_tables;

select count(*) from dba_tables;

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395405 is a reply to message #395402]

Wed, 01 April 2009 05:57

mjkreddy1927
Messages: 20
Registered: February 2007

Junior Member

Sorrie for that,

there is a slight change in my question...

i need inividual count of the tables in the DB.ie howmany rows in each table has?

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395411 is a reply to message #395401]

Wed, 01 April 2009 06:10

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

If the count doesn't have to be 100% accurate then you can query user_tables.num_rows.

Otherwise dynamic sql is probably what you are looking for.

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395413 is a reply to message #395411]

Wed, 01 April 2009 06:16

trivendra
Messages: 211
Registered: October 2007
Location: Phoenix

Senior Member

Or create a script to count rows from USER_TABLES.

SELECT 'SELECT COUNT (*) FROM ' || TABLE_NAME || ';' FROM USER_TABLES;

or can use this dynamically as suggested by @cookiemonster.

[Updated on: Wed, 01 April 2009 06:16]

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395415 is a reply to message #395401]

Wed, 01 April 2009 06:19

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

There are many examples here in SQL or PL/SQL, just search for them.

Regards
Michel

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395423 is a reply to message #395401]

Wed, 01 April 2009 07:22

S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom

Senior Member

 select
      table_name,
      to_number(
        extractvalue(
          xmltype(
       dbms_xmlgen.getxml('select count(*) cnt from '||table_name))
          ,'/ROWSET/ROW/CNT')) count
    from user_tables;

I will leave it you to figure out what this query is doing.

Regards

Raj

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395427 is a reply to message #395423]

Wed, 01 April 2009 07:39

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

A real educational approach Rolling Eyes

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395431 is a reply to message #395423]

Wed, 01 April 2009 07:44

Frank
Messages: 7901
Registered: March 2000

Senior Member

Now THAT's a cool way!

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395450 is a reply to message #395431]

Wed, 01 April 2009 08:30

trivendra
Messages: 211
Registered: October 2007
Location: Phoenix

Senior Member

This is real amazing..

I have tried but I can see only 7 rows returned, am I missing some thing.

SQL> SELECT COUNT (1)
  FROM all_tables

  COUNT(1)
----------
      1423

1 row selected.
SQL> SELECT table_name
      ,to_number (extractvalue (XMLTYPE (dbms_xmlgen.getxml ('select count(*) cnt from ' || table_name))
                               ,'/ROWSET/ROW/CNT')) COUNT
  FROM all_tables

TABLE_NAME                          COUNT
------------------------------ ----------
DUAL                                    1
SYSTEM_PRIVILEGE_MAP                  166
TABLE_PRIVILEGE_MAP                    24
STMT_AUDIT_OPTION_MAP                 205
OL$                                     0
OL$HINTS                                0
OL$NODES                                0

7 rows selected.

Thanks
Trivendra

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395454 is a reply to message #395423]

Wed, 01 April 2009 08:47

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

"xmltype(dbms_xmlgen.getxml(" can be replaced by "dbms_xmlgen.getXMLtype(".
The following is the script I use:

select table_name,
       to_number(extractvalue(
                   dbms_xmlgen.getXMLtype(
                     'select count(*) cnt from '||table_name),
                     '/ROWSET/ROW/CNT')) rows_in_table
from user_tables
where tablespace_name is not null or partitioned='YES' 
order by 1
/

Regards
Michel

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395455 is a reply to message #395450]

Wed, 01 April 2009 08:49

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As you call a procedure roles are not enabled and so all_tables is the same as user_tables if you have no direct access to others table.
Actually, depending on your version you either has only your direct accessible tables or has an error like:

ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288

Which one is yours?

Regards
Michel

[Updated on: Wed, 01 April 2009 08:53]

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395457 is a reply to message #395455]

Wed, 01 April 2009 08:54

Frank
Messages: 7901
Registered: March 2000

Senior Member

In that case, either his SYS user is screwed up (PLUS he's working as SYS), or his DUAL in in the wrong schema..

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395462 is a reply to message #395457]

Wed, 01 April 2009 09:01

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sorry, maybe you didn't read it but I lately added "if you have no direct access to others table".
SELECT privilege has been granted to PUBLIC to all the mentionned tables and this is viewed as direct access.

Regards
Michel

[Updated on: Wed, 01 April 2009 09:02]

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395470 is a reply to message #395462]

Wed, 01 April 2009 09:48

trivendra
Messages: 211
Registered: October 2007
Location: Phoenix

Senior Member

Thanks Michel,

I tried on the schema which has only synonyms created, they have 'SELECT' grant with them, this also include the SYS and SYSTEM objects with PUBLIC grant.

This schema has, as such no objects of its own, that's why I tried this on ALL_TABLES.

I can see the same error on the execution.

Is this depends upon the GRANT for PUBLIC or specific to USER.

Thanks
Trivendra

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395482 is a reply to message #395470]

Wed, 01 April 2009 10:45

Michel Cadot
Messages: 68774
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I don't understand what you mean.
You only see your tables, the tables on which you or PUBLIC has been (directly) granted SELECT privilege.

Regards
Michel

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395489 is a reply to message #395423]

Wed, 01 April 2009 11:05

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Wonderful.

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395496 is a reply to message #395489]

Wed, 01 April 2009 11:53

trivendra
Messages: 211
Registered: October 2007
Location: Phoenix

Senior Member

I got your point.

Thanks
Trivendra

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395592 is a reply to message #395401]

Wed, 01 April 2009 23:44

laknar
Messages: 30
Registered: February 2009

Member

or try this

select TABLE_NAME, NUM_ROWS from USER_TABLES

Report message to a moderator

Re: Row count of all tab's in my DB. [message #395610 is a reply to message #395592]

Thu, 02 April 2009 01:13

Frank
Messages: 7901
Registered: March 2000

Senior Member

laknar wrote on Thu, 02 April 2009 06:44

or try this

select TABLE_NAME, NUM_ROWS from USER_TABLES

Which was already suggested by cookiemonster..

Report message to a moderator

Previous Topic:	cursors with same codes
Next Topic:	Exporting data which is having LONG data type

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 08 18:21:47 CST 2026