-
Hi Gurus,
I have a doubt. I want to list all the tables that has 0 rows. I can do this by analayzing the tables and select num_rows from dba_tables where rows=0 . Since i cannot afford to analyze the tables, is it possible to write a small pl/sql code which does this..
May be something like this...
declare a variable
open a cursor
select table_name from dba_tables till notfound
begin
fetch cursor into variable
select count(*) from variable
if row count is = 0
print the variable
do this iteration till last table name
exit
The problem is that i donot know exact pl/sql syntax and i will really appreciate if someone helps me out with this task.
Thanks in advance for your time
Bala
-
what is ur oracle version
Jr.
-
More simple:
EXEC DBMS_UTILITY.ANALYZE_SCHEMA('owner','COMPUTE');
select table_name from all_tables where owner='ownwr' and NUM_ROWS = 0;
for each schema which u need.
-
First, enable output
SQL> exec dbms_output.enable(1000000);
Then, paste the following code on the command line:
declare
cursor ownercur is select owner, table_name from dba_tables where owner NOT IN ('SYS', 'SYSTEM', 'DBSNMP','CTXSYS', 'OUTLN') order by owner, table_name;
v_rowcount number;
begin
for ownerrec in ownercur loop
exit when ownercur%NOTFOUND;
v_rowcount := 0;
execute immediate 'select count(*) from '||ownerrec.owner||'.'||ownerrec.table_name into v_rowcount;
if v_rowcount = 0 then
dbms_output.put_line(rpad(ownerrec.owner||'.'||ownerrec.table_name,60)||' '||lpad(to_char(v_rowcount),30));
end if;
end loop;
end;
/
Oracle DBA and Developer
-
I created a function to do the same thing. This allows you to specify the table name easily. Of course you have to have the correct priveleges.
function row_count ( owner in varchar2, table_name in varchar2 ) return number is
/*----------------------------------------------------------------------------
Purpose: Count the number of rows in a table. Although you can do the
same thing with a "select count" statement, the function will let
you do a count in a sql statement for all tables belonging to a
user.
Date: 12/19/2001
Author: Jeff Lee
Inputs: owner Owner of the table
table_name Name of the table
Example: select table_name, row_count(user, table_name) "ROW_COUNT"
from user_tables;
Notes:
Revision History:
----------------------------------------------------------------------------*/
v_sql varchar2(255);
v_row_count number;
begin
v_sql := 'select count(*) from ' || owner || '.' || table_name;
execute immediate v_sql into v_row_count;
return v_row_count;
end row_count;
-
Code:
declare
numrows number;
begin
dbms_output.enable(1000000);
for i in (select * from dba_tables where owner not in ('SYS, 'SYSTEM' bla blah)
loop
select count(*)
into numrows
from i.table_name;
if numrows > 0 then
dbms_output.put_line(i.owner||'.'||i.table_name||' has '|| numrows||' rows';
end if;
end loop;
end;
/
-
change "select count(*) into numrow from mytab" to "select 1 into numrow from mytab where rownum<2"
because if your table has many billion of rows a full table scan will be performed,but we not need that.
-
select count(1) == select count(*)
no difference at all
-
Originally posted by pando
select count(1) == select count(*)
no difference at all
Pando, you are missing Guo's point (and he's got good one!). he suggested to use ... where rownum < 2... in order to exit full table scan as soon as the first row is found. This will indeed substantially speed the whole process of detecting "empty" tables.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
true I didnt read the whole select
sorry !
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|