query to find number of empty tables in my schema name HR

Question

I want to find the number of empty tables in my schema hr and i have written a code for it:

set serveroutput on;


Declare
d Number;
c varchar2(25);
cursor c1 is SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'HR';

Begin
 for r in c1
Loop
select count(*) into d
from (r.object_name);
if (d = 0) then
dbms_output.put_line(r.object_name||'is Empty');
end if;
end loop;
end;

but its throwing error at this line from (r.object_name). can anyone help me in this?

You'll need to provide much more, specific, context for anyone to help you — New Alexandria, Oct 27 '17 at 13:34
Possible duplicate of [Dynamic SQL with table name as a parameter](https://stackoverflow.com/questions/43211749/dynamic-sql-with-table-name-as-a-parameter) — mustaccio, Oct 27 '17 at 13:45

score 3 · Answer 1 · answered Oct 27 '17 at 10:48

Querying dba_tables.num_rows is not reliable as that is an estimate. If the table has never been analyzed, it might not reflect the correct row count.

Dynamic SQL is also not required for this. Based on my answer that calculates the row count for all tables, you can simply add a where condition:

select table_name
from (
  select table_name, 
         to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from '||owner||'.'||table_name)),'/ROWSET/ROW/C')) as row_count
   from all_tables
   where owner = 'HR'
)
where row_count = 0;

Ponder Stibbons · Answer 2 · 2017-10-27T10:19:29.363

1

You need dynamic SQL (execute immediate) for that:

declare
  d number;
  v_sql varchar2(1000);

  cursor c1 is select object_name 
    from all_objects 
    where object_type = 'TABLE' and owner = 'HR';

begin
  for r in c1 loop
      v_sql := 'select count(*) from HR.'||r.object_name;
      execute immediate v_sql into d;
      if d = 0 then
          dbms_output.put_line(r.object_name||' is empty');
      end if;
  end loop;
end;

edited Oct 27 '17 at 10:19

answered Oct 27 '17 at 09:56

Ponder Stibbons

14,723
2
21
24

its still throwing an error table or view does not exist – aamir saif Oct 27 '17 at 10:09
Add owner: `v_sql := 'select count(*) from HR.'||r.object_name;` – Ponder Stibbons Oct 27 '17 at 10:19

score 0 · Answer 3 · answered Oct 27 '17 at 10:30

0

In Oracle , you can use below query.

select    count(*) from    dba_tables  where    OWNER = 'XXXX' and num_rows =0;

answered Oct 27 '17 at 10:30

shivam

489
2
8
22

query to find number of empty tables in my schema name HR

3 Answers3