Re: display user tables that are not empty using PL SQL

From: <fitzjarrell_at_cox.net>
Date: Mon, 26 Nov 2007 09:10:50 -0800 (PST)
Message-ID: <f11422bb-ca33-43ad-ba31-1889e9b5164f@y43g2000hsy.googlegroups.com>

On Nov 26, 10:21 am, noelloen <noel...._at_gmail.com> wrote:
> Hi
>
> How can i display user tables that are not empty using PL SQL
> developer? or
> How can i display user tables that are empty using PL SQL developer?
>
> thanks

Depends. If statistics are current it's a simple matter to display populated tables and no PL/SQL is necessary:

select owner, table_name
from all_tables
where nvl(num_rows, 0) > 0;

This presumes that any table with a NULL num_rows column is empty, which may not be the case. You COULD generate a flat file of all the tables and the associated row counts, create an external table from that and query where the row_ct (for example) > 0. You could also use PL/SQL to return this information and this has been done before. Here is one example of mine:

declare

    i number:=0;
    sqlstr varchar2(500);
    cursor get_tname is
    select owner, table_name
    from dba_tables
    where table_name not like 'SYS%';
begin

    for trec in get_tname loop

            sqlstr := 'select /*+ first_rows */ count(*) ct from ';
            sqlstr := sqlstr || trec.owner||'."'||
trec.table_name||'"';
            sqlstr := sqlstr || ' where rownum = 1';
            execute immediate sqlstr into i;
            if i = 0 then
                    dbms_output.put_line(trec.table_name||' has no
rows');
            else
                    i := 0;
            end if;

    end loop;
end;
/

Of course you can change the code to report tables with a non-zero count; I'll leave that up to you.

There may be other ways to do what you want, however since you haven't seen fit to post your Oracle release it's difficult to say what will work and what won't. The above code will work on 10gR2, 10gR1, 9iR2 and 8iR3 (8.1.7.4).

David Fitzjarrell Received on Mon Nov 26 2007 - 11:10:50 CST