Home » SQL & PL/SQL » SQL & PL/SQL » Select tables with more than one row (Oracle 10i)
Select tables with more than one row [message #356586] Fri, 31 October 2008 05:50 Go to next message
coolguydel
Messages: 2
Registered: October 2008
Junior Member
hello,

I want to select the tables with more than one rows within it.

For example I am useing the following statemtn

Select temp
from all_tables
where temp1 like 'test'
and rownum>0;

but I also want to show only tables that have more then 1 rows.any suggestions??thanks
Re: Select tables with more than one row [message #356588 is a reply to message #356586] Fri, 31 October 2008 06:01 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
If I understand your question correctly you want to list the tables which contains more than one row. If this being the case you need to do the following

a) Scan the user tables to get the table_name
b) Write an optimal query to find if there are more than 1 row to it. It will be something like
select count(*) from table where rownum < 3;

c) Since you don't know the table at the design time the options left for you either use dynamic_sql or dbms_Sql package.

So now I have given you the steps how to do it (brief outline). Read the oracle reference manual

http://tahiti.oracle.com

If you are stuck or having some issues come back to us and copy and paste the code along with the error message or the problem and somebody will be able to help you.

Last but not least follow the forum guidelines on how to format your post.

Regards

Raj
Re: Select tables with more than one row [message #356602 is a reply to message #356586] Fri, 31 October 2008 07:57 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I would just gather stats for the tables and look at the NUM_ROWS column of USER_TABLES view.

Have a look at dbms_stats.gather_schema_stats
Previous Topic: dbms_job from a procedure
Next Topic: Problem in Insert all in Oracle10g
Goto Forum:
  


Current Time: Fri Dec 09 13:43:05 CST 2016

Total time taken to generate the page: 0.17758 seconds