This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Mar 9, 2013 7:43 AM by Peter Gjelstrup RSS

Query for Empty Tables.

bhatt t Newbie
Currently Being Moderated
Hi All,

Can someone tell me a Query to find out "EMPTY TABLES i.e. Tables having no Rows" in Oracle 8i ?

I tried on Google, but results are not satisfactory, may be due to 8i version.

Please guide me.

Regards.
  • 1. Re: Query for Empty Tables.
    Hoek Guru
    Currently Being Moderated
    If your 8i version is using CBO and you have (fresh) table statistics gathered, then you can simply query the datadictionary view ALL_TABLES. Just add the predicate WHERE NUM_ROWS IS NULL.

    Consider upgrading anyway, your version is ancient and not supported.
  • 2. Re: Query for Empty Tables.
    bhatt t Newbie
    Currently Being Moderated
    Hi Hoek,

    I never used CBO and don't know about it and how can I gather (fresh) table statistics so as to run the query?

    I m actually in developer track and not in DBA track.

    So please guide me in details.

    Regards.
  • 3. Re: Query for Empty Tables.
    367852 Explorer
    Currently Being Moderated
    See whether this is of help :

    The below will generate a script of sql statements for all the tables for the user SCOTT
    select 'select ''EMPTY TABLE'', ''' ||  table_name ||   '''' ||
           ' from dual where not exists (select 1 from ' ||
           owner || '.' || table_name || ' );'
    from all_tables where owner = 'SCOTT'
    The above sql generates the below sql statements which can then be executed as a script
    select 'EMPTY TABLE', 'DEPT' from dual where not exists (select 1 from SCOTT.DEPT );
    select 'EMPTY TABLE', 'EMP' from dual where not exists (select 1 from SCOTT.EMP );
    The original sql or the above output can be suitably modified to include 'Union All'. A quick fix may be to do a find-replace 'union all' for the semi-colon, except for the last line which will need a semi-colon(;).
  • 4. Re: Query for Empty Tables.
    Hoek Guru
    Currently Being Moderated
    CBO is nowadays the way to go.
    RBO is dead.
    Do you have a DBA around to assist you?
    Why are you still using 8i?

    Anyway, read this article and explore its links for starters:
    http://www.oracle-base.com/articles/misc/cost-based-optimizer-and-database-statistics.php
    Documentation is here: http://www.oracle.com/pls/tahiti/homepage
  • 5. Re: Query for Empty Tables.
    APC Oracle ACE
    Currently Being Moderated
    Hoek wrote:
    CBO is nowadays the way to go.
    That is certainly true of modern versions of the database. However, the OP is running on an antique - 8i - so the message is cloudier. The CBO in that version was pretty good but Oracle didn't deprecate the RBO until the 9i.

    Cheers, APC
  • 6. Re: Query for Empty Tables.
    Hoek Guru
    Currently Being Moderated
    True, but I'm not sure whether OP is using 8i professionally or for studying purposes.
    If the latter (which I suspect) OP should use a (free downloadable) recent database version imho.

    Only my $0.02 ;)
  • 7. Re: Query for Empty Tables.
    bhatt t Newbie
    Currently Being Moderated
    Sorry sir,

    It doesn't work.

    Any other Idea.
  • 8. Re: Query for Empty Tables.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    982164 wrote:
    Hi Hoek,

    I never used CBO and don't know about it and how can I gather (fresh) table statistics so as to run the query?
    You may be using the CBO and not know about it.
    If the package dbms_stats exists on your system, then use it to gather statistics. If not, use the ANALYZE command.

    Using the num_rows column of all_tables (or user_tables) is the best way to do find the tables with 0 rows.
    If you can't do that for some reason, then use dynamic SQL. For example:
    SET     SERVEROUTPUT     ON
    
    
    DECLARE
        n_rows     NUMBER;
        sql_txt     VARCHAR2 (200);
    BEGIN
        FOR  t  IN  (
                         SELECT    owner, table_name
                 FROM          all_tables
                 WHERE     owner  NOT IN ('OUTLN', 'SYS', 'SYSTEM')  -- If wanted
                 ORDER BY  owner, table_name
                      )
        LOOP
            sql_txt := 'SELECT COUNT (*) FROM "'
              || t.owner
              ||  '"."'
              || t.table_name
              || '"';
    --     dbms_output.put_line (sql_txt || ' = sql_txt');     -- For debugging
         EXECUTE IMMEDIATE  sql_txt
             INTO        n_rows;
         IF  n_rows = 0
         THEN
             dbms_output.put_line (  RPAD (t.owner, 30)
                             || t.table_name
                         );
         END IF;
        END LOOP;
    END;
    /
  • 9. Re: Query for Empty Tables.
    rp0428 Guru
    Currently Being Moderated
    >
    It doesn't work.
    >
    Oracle NEVER responds to a query with "It doesn't work".

    Post a copy of the EXACT response you are getting from running the code.

    And provide answers to the other questions you were ask about why you are still using 8i and what you are using it for.
  • 10. Re: Query for Empty Tables.
    bhatt t Newbie
    Currently Being Moderated
    Hi Hoek,

    Actually we upgraded our database to 11g.

    We imported our data from 8i to 11gr2 by the utility IMP successfully.

    It imported our data 100% i.e. when we import 8i dmp data into 11g, there is never problem.

    Problem is arises now when we further use IMP/EXP utility in 11g.

    When we run EXP utlity in 11g, it doesn't export empty tables, which result errors in constraints posting.

    Someone told me to insert atleast 1 row in the empty tables, then they will be exported.

    That's why I m searching of the Empty Tables.

    Am I going right way ?
  • 11. Re: Query for Empty Tables.
    Justin Cave Oracle ACE
    Currently Being Moderated
    982164 wrote:
    Problem is arises now when we further use IMP/EXP utility in 11g.

    When we run EXP utlity in 11g, it doesn't export empty tables, which result errors in constraints posting.
    That seems highly unlikely. The export utility couldn't care less whether a table has rows in it. It will happily export empty tables. What is the exact command-line you use to generate the export? Why do you believe that there are tables that should be exported that are not being exported
    Someone told me to insert atleast 1 row in the empty tables, then they will be exported.
    That seems highly, highly unlikely.
    Am I going right way ?
    Almost certainly not.

    Justin
  • 12. Re: Query for Empty Tables.
    Hoek Guru
    Currently Being Moderated
    When we run EXP utlity in 11g, it doesn't export empty tables.
    Thanks for pointing all out.
    11g uses CBO by default, so I'll assume you could query ALL_TABLES, did you try that already.
    However, besides that, you might want to read about DataPump as well, a 'modern exp and imp':
    http://www.oracle-base.com/articles/10g/oracle-data-pump-10g.php
    http://docs.oracle.com/cd/E11882_01/server.112/e23090/whatsnew.htm#DVADM71140
  • 13. Re: Query for Empty Tables.
    sb92075 Guru
    Currently Being Moderated
    http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables002.htm#ADMIN13319
  • 14. Re: Query for Empty Tables.
    Frank Kulash Guru
    Currently Being Moderated
    982164 wrote:
    Hi Hoek,

    Actually we upgraded our database to 11g.

    We imported our data from 8i to 11gr2 by the utility IMP successfully.

    It imported our data 100% i.e. when we import 8i dmp data into 11g, there is never problem.

    Problem is arises now when we further use IMP/EXP utility in 11g.
    So everything in Oracle 8 is working the way you expect. The fact that you also have an Oracle 8 database has nothing to do with this problem. Is that what you're saying?
    When we run EXP utlity in 11g, it doesn't export empty tables.
    If the problem is entirely in Oracle 11, then you should be able to use something like XMLQUERY to find the 0-row tables, if you really need to. (I think you don't.)

    EXP can export tables with 0 rows. I just tried it in Oracle 11, and part of the feedback I got was:
    . . exporting table                       ZIPCODES          0 rows exported
    . . exporting table                  ZONEORDER_TBL          5 rows exported
    . . exporting table                       ZONE_TBL          6 rows exported
    . . exporting table                            ZOO          2 rows exported
    . . exporting table                    Z_HIERARCHY          3 rows exported
    . . exporting table                         Z_TEST          3 rows exported
    . . exporting table                        Z_TEST4          0 rows exported
    If it's not exporting 0-row tables for you, find out why, and fix that problem. Post your exp control file.
    Someone told me to insert atleast 1 row in the empty tables, then they will be exported.
    And then, after importing them, TRUNCATE those tables? That's a lot of work.
    That's why I m searching of the Empty Tables.

    Am I going right way ?
    I don't think so.
    Find and fix the problem with exp.
    If you can't get exp to work, then look into data pump.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points