Results 1 to 8 of 8

Thread: Doubt in Oracle

  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Doubt in Oracle

    I have a doubt in Oracle.
    I am trying to Export a Database that is there in US to here in Bangalore.
    I have found out that there is 1433 Tables in the database.

    I want to find out how many tables out of these 1433 have Data in it.
    Is there a script or a query that i can run to find out this Information.

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    If you can ensure statistics are up to date on those tables, you can query the num_rows column of the dba_tables view to find out how many rows they have.

  3. #3
    Join Date
    Jan 2004
    Posts
    67
    unfortunately, the Statistics are not updated. Because of which i am not getting the correct data in num_rows.

    I checked this also. ITs going to take a lot of time for me to update the statistics...

    Thanks for your immediate response. Is there any other way out...:-(

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Something like this should work..
    Code:
    SQL@8i> conn scott/tiger
    Connected.
    SQL@8i> set serveroutput on
    SQL@8i> declare
      2   n number := 0;
      3  begin
      4    for i in ( select table_name from user_tables )
      5    loop
      6      execute immediate 'select count( * ) from ' || i.table_name || ' where rownum = 1' into n;
      7      if n > 0
      8      then
      9        dbms_output.put_line( 'table name = ' || i.table_name || ' has data on it.' );
     10      end if;
     11    end loop;
     12  end;
     13  /
    table name = ACCOUNT has data on it.
    table name = DEPT has data on it.
    table name = EMP has data on it.
    table name = RECEIPT has data on it.
    table name = SALGRADE has data on it.
    
    PL/SQL procedure successfully completed.
    
    SQL@8i>

  5. #5
    Join Date
    Jan 2004
    Posts
    67
    This works for that respecitve users.

    suppose i want to check for all the tables (using DBA privileges) how do i do it.

    We have 28 users and 1433 tables.

    Is there a way i can check for all this at one shot.

    Thanks for your replies.

  6. #6
    Join Date
    Feb 2004
    Location
    inida
    Posts
    62
    Login as SYS and run it

    declare
    n number := 0;
    begin
    for i in ( select table_name,owner from all_tables where owner in ('X','Y','Z' ) order by table_name)
    loop
    execute immediate 'select count( * ) from ' || i.owner||'.'||i.table_name || '' into n;
    if n > 0
    then
    dbms_output.put_line( 'table name = ' || i.table_name || ' has data on it.' );
    end if;
    end loop;
    end;


    Replace 'X', 'Y' and 'Z' with ur corresponding User names.

  7. #7
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36

    Cool

    I would think it will take as much time to select * from as it would to estimate statistics. I can not imagine why someone would have such a large database, and not be collecting statistics on a regular basis.

    My suggestion is to use DBMS_STATS to update the statistics. You can choose the % to sample, or let oracle do that for you. Then you can either select count(*) from dba_tables where num_rows >0, or select owner,table_name, num_rows from dba_tables where num_rows > 0 order by num_rows desc;

    HTH
    Peter

  8. #8
    Join Date
    Oct 2004
    Posts
    145
    Silly question.

    If you need to export out the data anyways, why not export the data out and rely on the logs to see if there is data in the tables or not ?

    It does not take much more time to export/import tables with no rows.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •