Home » SQL & PL/SQL » SQL & PL/SQL » number of rows in all tables in a schema...
number of rows in all tables in a schema... [message #249720] Thu, 05 July 2007 11:50 Go to next message
shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI
Senior Member
Hi,

What is the query to find out the number of rows in all tables in a schema?

select count(*), table_name
from all_tables
where owner = 'OWNER_NAME'
group by table_name; ????????????????????
Re: number of rows in all tables in a schema... [message #249721 is a reply to message #249720] Thu, 05 July 2007 11:51 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
If the statstics are current, you can query USER_TABLES.
Re: number of rows in all tables in a schema... [message #249723 is a reply to message #249720] Thu, 05 July 2007 11:58 Go to previous messageGo to next message
shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI
Senior Member
this query is not giving me the correct result user_tables or all_tables. I need to see how many rows are in each table. I know it is a very basic query but it has slipped from my mind. I could use OEM but the darn thing gives me the wrong number of rows.

select count(*), table_name
from all_tables
group by table_name;

COUNT(*) TABLE_NAME
---------- ------------------------------
1 ANALYSIS_CATEGORY
1 ANALYSIS_CHART
1 ANALYSIS_CHART_OLD
1 ANALYSIS_REPORT
1 ANSWER
1 APP_MENU
1 APP_MENU_PAGE
1 APP_MENU_ROLE
1 APP_PAGE
1 AUDIT_ACTIONS
1 AUDIT_TRAIL
1 AUTH_LEVEL
1 AW$AWCREATE
1 AW$AWCREATE10G
1 AW$AWMD
1 AW$AWREPORT
1 AW$AWXML
1 AW$EXPRESS
1 CERTIFICATION_METHOD
1 COMPANY
1 CONTACT
1 CONTACT_ALIAS
1 CONTACT_ANSWER
1 CONTACT_EMAIL
1 CONTACT_LOGIN
1 CONTACT_ROLE
1 CONTACT_TEST
...
.

[Updated on: Thu, 05 July 2007 12:00]

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249724 is a reply to message #249720] Thu, 05 July 2007 12:01 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
If the statstics are current, you can query USER_TABLES.

You can lead some folks to knowledge but you can't make them think.
icon10.gif  Re: number of rows in all tables in a schema... [message #249725 is a reply to message #249720] Thu, 05 July 2007 12:03 Go to previous messageGo to next message
shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI
Senior Member
PLeeeeeze could you tell me?? After all it is a SQL Newbies forum Smile
Re: number of rows in all tables in a schema... [message #249726 is a reply to message #249720] Thu, 05 July 2007 12:05 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
SQL> DESC USER_TABLES

which column might contain data to answer your question?
Re: number of rows in all tables in a schema... [message #249727 is a reply to message #249720] Thu, 05 July 2007 12:05 Go to previous messageGo to next message
shaseeb
Messages: 113
Registered: April 2007
Location: Madison, WI
Senior Member
I'm sorry....I'm soo stupid. I didn't see the num_rows column. I think that'll give me what I'm looking for Smile
Re: number of rows in all tables in a schema... [message #249737 is a reply to message #249720] Thu, 05 July 2007 13:06 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Please be aware that the num_rows column will only contain the number of rows at the last time the statistics were run and do not reflect the current count.
Re: number of rows in all tables in a schema... [message #249810 is a reply to message #249737] Fri, 06 July 2007 00:02 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
True, Bill, as anacedent said already as well.
But since the total number of rows in a schema is totally not interesting anyway, who cares if you're off 10%?
Previous Topic: Driving table problem
Next Topic: Trigger Problem
Goto Forum:
  


Current Time: Tue Dec 06 02:08:36 CST 2016

Total time taken to generate the page: 0.10167 seconds