Home » SQL & PL/SQL » SQL & PL/SQL » All table_names, row_count and size (Oracle 10g)
All table_names, row_count and size [message #346068] Fri, 05 September 2008 17:10 Go to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
Hi,
How can I retrieve a list of all tables, their row count and size of a particular schema?

Thanks

[Updated on: Fri, 05 September 2008 17:12]

Report message to a moderator

Re: All table_names, row_count and size [message #346070 is a reply to message #346068] Fri, 05 September 2008 17:20 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
if statistics are current & a close, close approximation is good enough, you can get answers from user_tables.

any other solution is VERY, VERY resource intensive.
SELECT COUNT(*) FROM <every table name here>
SELECT SUM(BYTES) FROM USER_SEGMENTS WHERE SEGMENT_NAME = <every table name here>
Re: All table_names, row_count and size [message #346072 is a reply to message #346070] Fri, 05 September 2008 17:31 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
Quote:
<every table name here>


You mean, I would have to do it one by one?

Isn't there a way to get all three things together/ i.e. table names, row_count and size

Thanks
Re: All table_names, row_count and size [message #346074 is a reply to message #346068] Fri, 05 September 2008 17:43 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>You mean, I would have to do it one by one?
Yes

>Isn't there a way to get all three things together/ i.e. table names, row_count and size
It depends upon required accuracy.
From USER_TABLES you have TABLE_NAME, NUM_ROWS & AVG_ROW_LEN; "size" = NUM_ROWS*AVG_ROW_LEN

Neither NUM_ROWS nor AVG_ROW_LEN are dynamic values & were valid as of LAST_ANALYZED value/date.

Either you accept close enough from USER_TABLE or you churn & burn disk activity at a high rate to obtain "better" values.
Keep in mind that on an active production system, any answer is no longer 100% accurate even before you can deliver the results to anyone.

Re: All table_names, row_count and size [message #346076 is a reply to message #346074] Fri, 05 September 2008 17:59 Go to previous messageGo to next message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
Oh yea, I don't care about 100% accurate results. I can live with last analyzed results.

So my query would be...?
Re: All table_names, row_count and size [message #346082 is a reply to message #346076] Fri, 05 September 2008 18:16 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
deepmachine wrote on Fri, 05 September 2008 23:59


So my query would be...?

Did Ana not just tell you what table to look at?
Re: All table_names, row_count and size [message #346088 is a reply to message #346068] Fri, 05 September 2008 18:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1660875645686
Re: All table_names, row_count and size [message #346162 is a reply to message #346088] Sat, 06 September 2008 16:08 Go to previous message
deepmachine
Messages: 80
Registered: August 2008
Location: United States
Member
Thanks Barbara.
Previous Topic: AND AND AND
Next Topic: sql query
Goto Forum:
  


Current Time: Tue Dec 06 02:38:14 CST 2016

Total time taken to generate the page: 0.08763 seconds