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
Messages: 2
Registered: October 2008
Junior Member

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


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.


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
Messages: 4766
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: Table Variables
Next Topic: Zip a File From PL/SQL
Goto Forum:

Current Time: Sat Aug 19 06:25:54 CDT 2017

Total time taken to generate the page: 0.04605 seconds