Home » SQL & PL/SQL » SQL & PL/SQL » listing tables with more than n rows
listing tables with more than n rows [message #233774] Fri, 27 April 2007 07:37 Go to next message
mylapuram
Messages: 8
Registered: April 2007
Location: Bangalore
Junior Member
Hi,

I want to get list of all tables which have more than n rows.

For ex i want to list all table which have more than 100 rows.

can any one of u help me in doing this. or give me a hint i will do the rest.

Thanks in Advance
Re: listing tables with more than n rows [message #233776 is a reply to message #233774] Fri, 27 April 2007 07:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
analyze/gather stats on tables and look into user_table.num_rows.
Be advised that, if the stats are updated/not collected properly, you get inconsistent results.
Re: listing tables with more than n rows [message #233777 is a reply to message #233776] Fri, 27 April 2007 07:43 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.orafaq.com/forum/m/156045/42800/?srch=numrows#msg_156045
Re: listing tables with more than n rows [message #233784 is a reply to message #233777] Fri, 27 April 2007 08:10 Go to previous messageGo to next message
compcoder
Messages: 30
Registered: April 2007
Location: Maryland USA
Member
declare
type row_count_rec is record ( table_name varchar(30),
num_of_rows pls_integer);
type row_count_table_type is table of row_count_rec;
row_count_table row_count_table_type := row_count_table_type();
type table_name_tab_type is table of varchar2(30) index by binary_integer;
table_name_tab table_name_tab_type;
looper binary_integer;

begin
-- Get all of the table names
select table_name bulk collect into table_name_tab from tabs;
if table_name_tab.count > 0 then
for looper in table_name_tab.first..table_name_tab.last loop
row_count_table.extend;
row_count_table(row_count_table.last).table_name := table_name_tab(looper);
-- Get the row counts
EXECUTE IMMEDIATE 'select count(*) from ' || table_name_tab(looper) into row_count_table(row_count_table.last).num_of_rows;
end loop;
end if;
-- output the results
for looper in row_count_table.first..row_count_table.last loop
--if row_count_table(looper).num_of_rows > 10 then
dbms_output.put_line(row_count_table(looper).table_name || ' has ' || row_count_table(looper).num_of_rows || ' row(s)');
--end if;
end loop;
end;
Re: listing tables with more than n rows [message #233796 is a reply to message #233784] Fri, 27 April 2007 08:48 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Reread this and compare it to Mahesh' solution.
Which one looks easier to you, considering the fact that statistics have to be up-to-date anyway?
Previous Topic: how to reading CLOB contains 20000 characters.
Next Topic: Generate Rows
Goto Forum:
  


Current Time: Wed Dec 07 12:29:19 CST 2016

Total time taken to generate the page: 0.10852 seconds