Listing tables that has no rows
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Listing tables that has no rows

  1. #1
    Join Date
    Mar 2002
    Posts
    2

    Exclamation

    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

  2. #2
    Join Date
    Jan 2002
    Posts
    148
    what is ur oracle version

    Jr.

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    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

  5. #5
    Join Date
    Sep 2001
    Posts
    3
    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;


  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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;
    /

  7. #7
    Join Date
    Feb 2002
    Posts
    3
    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.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select count(1) == select count(*)

    no difference at all

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  10. #10
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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
  •  



Click Here to Expand Forum to Full Width