Home » SQL & PL/SQL » SQL & PL/SQL » Row count of all tab's in my DB. (10g)
icon7.gif  Row count of all tab's in my DB. [message #395401] Wed, 01 April 2009 05:45 Go to next message
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
Re: Row count of all tab's in my DB. [message #395402 is a reply to message #395401] Wed, 01 April 2009 05:53 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

select count(*) from user_tables;

select count(*) from dba_tables;
Re: Row count of all tab's in my DB. [message #395405 is a reply to message #395402] Wed, 01 April 2009 05:57 Go to previous messageGo to next message
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?
Re: Row count of all tab's in my DB. [message #395411 is a reply to message #395401] Wed, 01 April 2009 06:10 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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.
Re: Row count of all tab's in my DB. [message #395413 is a reply to message #395411] Wed, 01 April 2009 06:16 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many examples here in SQL or PL/SQL, just search for them.

Regards
Michel
Re: Row count of all tab's in my DB. [message #395423 is a reply to message #395401] Wed, 01 April 2009 07:22 Go to previous messageGo to next message
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
Re: Row count of all tab's in my DB. [message #395427 is a reply to message #395423] Wed, 01 April 2009 07:39 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

A real educational approach Rolling Eyes
Re: Row count of all tab's in my DB. [message #395431 is a reply to message #395423] Wed, 01 April 2009 07:44 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Now THAT's a cool way!
Re: Row count of all tab's in my DB. [message #395450 is a reply to message #395431] Wed, 01 April 2009 08:30 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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
Re: Row count of all tab's in my DB. [message #395454 is a reply to message #395423] Wed, 01 April 2009 08:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Row count of all tab's in my DB. [message #395455 is a reply to message #395450] Wed, 01 April 2009 08:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
Frank
Messages: 7880
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..
Re: Row count of all tab's in my DB. [message #395462 is a reply to message #395457] Wed, 01 April 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
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
Re: Row count of all tab's in my DB. [message #395482 is a reply to message #395470] Wed, 01 April 2009 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Row count of all tab's in my DB. [message #395489 is a reply to message #395423] Wed, 01 April 2009 11:05 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Wonderful.
Re: Row count of all tab's in my DB. [message #395496 is a reply to message #395489] Wed, 01 April 2009 11:53 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
I got your point.

Thanks
Trivendra
Re: Row count of all tab's in my DB. [message #395592 is a reply to message #395401] Wed, 01 April 2009 23:44 Go to previous messageGo to next message
laknar
Messages: 22
Registered: February 2009
Junior Member
or try this
select TABLE_NAME, NUM_ROWS from USER_TABLES
Re: Row count of all tab's in my DB. [message #395610 is a reply to message #395592] Thu, 02 April 2009 01:13 Go to previous message
Frank
Messages: 7880
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..
Previous Topic: cursors with same codes
Next Topic: Exporting data which is having LONG data type
Goto Forum:
  


Current Time: Fri Dec 09 13:36:10 CST 2016

Total time taken to generate the page: 0.08917 seconds