How to write a query or procedure or function to retrieve all the tables from database where the table dont have any data (no rows).

Database has 100 tables and some of the tables dont have any data. I want to pullout those table names from database in Oracle.

Interview Candidate
Aug 7th, 2007
9
2505

PL/SQL

Answer

Showing Answers 1 - 9 of 9 Answers

hemangi.savaliya

Sep 26th, 2007

DECLARE
vtname VARCHAR2(100);
CURSOR cur_tablename IS
SELECT tname
FROM tab;
BEGIN
OPEN cur_tablename;

FOR i IN 1 .. 100
LOOP
FETCH cur_tablename
INTO vtname;
EXIT WHEN cur_tablename%NOTFOUND;
dbms_output.put_line(' table name' || vtname);
BEGIN
EXECUTE IMMEDIATE ' select 1 from ' || vtname; --||' where rownum<2';
EXCEPTION
WHEN no_data_found THEN

dbms_output.put_line(' doesn''t have data' || vtname);
END;
END LOOP;

CLOSE cur_tablename;

END;

ravgopal
Profile Answers by ravgopal

Oct 10th, 2007

Analyze and perform statistics on whole schema. This will populate the statistics details about each table. You can pull NUM_ROWS column from all_tables to check whether it contains zero.

step1: exec dbms_stats.gather_database_stats;

Step2 : select table_name from all_tables where num_rows=0;