OraFAQ Forum: SQL & PL/SQL » number of rows in all tables in a schema...

Home » SQL & PL/SQL » SQL & PL/SQL » number of rows in all tables in a schema...

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

number of rows in all tables in a schema... [message #249720]

Thu, 05 July 2007 11:50

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; ????????????????????

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249721 is a reply to message #249720]

Thu, 05 July 2007 11:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

If the statstics are current, you can query USER_TABLES.

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249723 is a reply to message #249720]

Thu, 05 July 2007 11:58

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

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249725 is a reply to message #249720]

Thu, 05 July 2007 12:03

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

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249726 is a reply to message #249720]

Thu, 05 July 2007 12:05

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> DESC USER_TABLES

which column might contain data to answer your question?

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249727 is a reply to message #249720]

Thu, 05 July 2007 12:05

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

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249737 is a reply to message #249720]

Thu, 05 July 2007 13:06

Bill B
Messages: 1971
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.

Report message to a moderator

Re: number of rows in all tables in a schema... [message #249810 is a reply to message #249737]

Fri, 06 July 2007 00:02

Frank
Messages: 7901
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%?

Report message to a moderator

Previous Topic:	Driving table problem
Next Topic:	Trigger Problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 08 18:27:36 CST 2024