DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: SQL Script

  1. #1
    Join Date
    Jan 2002
    Posts
    113
    I am looking for a script that will find all tables with zero records within a specific tablespace. Any help is appreciated.
    Arm yourself with knowledge

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Assuming your statistics are up to date, check dba_tables.num_rows and dba_segments.tablespace_name
    Jeff Hunter

  3. #3
    Join Date
    Jan 2002
    Posts
    113
    Statistics are not up to date and I have too many large tables to compute statistics for. Many tables have millions of records. I was looking for a way to use count(*) on all the tables in the tablespace and return the tables that are empty.
    Arm yourself with knowledge

  4. #4
    Join Date
    Mar 2001
    Posts
    314
    Use the following (modify as necessary):

    declare
    cursor_name integer;
    rows_processed integer;
    cursor c1 is select table_name from user_tables where tablespace_name='TABLESPACE_NAME_IN_CAPITALS';
    stmt varchar2(1024);
    begin
    cursor_name := dbms_sql.open_cursor;
    for mc1 in c1 loop
    stmt := 'select 9 from '||mc1.table_name||' where rownum < 2';
    dbms_sql.parse(cursor_name,stmt,dbms_sql.native);
    rows_processed := dbms_sql.execute_and_fetch(cursor_name);
    if rows_processed = 0 then
    dbms_output.put_line(mc1.table_name||' has '||to_char(rows_processed)||' rows');
    end if;
    end loop;
    dbms_sql.close_cursor(cursor_name);
    end;


    -amar


  5. #5
    Join Date
    Nov 2001
    Posts
    335

    amar,

    do you have your script working? What needs to be changed except tablespace name? It did not work for me.

    One, who thinks that the other one who thinks that know and does not know, does not know either!

  6. #6
    Join Date
    Jan 2002
    Posts
    113
    THANKS AMAR


    I set serveroutput on and run the procedure. It runs successfully but there is no output. What am I missing?

    Arm yourself with knowledge

  7. #7
    Join Date
    Mar 2001
    Posts
    635
    Hi

    Use the following code

    set serveroutput on size 1000000

    DECLARE
    t_c1_tname user_tables.table_name%TYPE;
    t_command varchar2(200);
    t_cid integer;
    t_total_records number(10);
    stat integer;
    row_count integer;
    t_limit integer := 0; -- Only show tables with 0 rows
    cursor c1 is select table_name from user_tables order by table_name where tablespace_name = 'TABLESPACE_NAME';
    BEGIN
    t_limit := 0;
    open c1;
    loop
    fetch c1 into t_c1_tname;
    exit when c1%NOTFOUND;
    t_command := 'SELECT COUNT(0) FROM '||t_c1_tname;
    t_cid := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(t_cid,t_command,dbms_sql.native);
    DBMS_SQL.DEFINE_COLUMN(t_cid,1,t_total_records);
    stat := DBMS_SQL.EXECUTE(t_cid);
    row_count := DBMS_SQL.FETCH_ROWS(t_cid);
    DBMS_SQL.COLUMN_VALUE(t_cid,1,t_total_records);
    if t_total_records = t_limit then
    DBMS_OUTPUT.PUT_LINE(rpad(t_c1_tname,55,' ')||
    to_char(t_total_records,'99999999')||' record(s)');

    end if;
    DBMS_SQL.CLOSE_CURSOR(t_cid);
    end loop;
    close c1;
    END;
    /


    Make sure the tablespace name is CAPS

    Regards,

    Santosh

  8. #8
    Join Date
    Mar 2001
    Posts
    314
    Originally posted by BV1963

    amar,

    do you have your script working? What needs to be changed except tablespace name? It did not work for me.

    Yes - I do have the script working - it works fine for me (just now double checked - cut/paste from here to sqlplus - works!). You only need to substitute the tablespace name (in CAPS).

    ----------------- screen output

    SQL> ED
    Wrote file afiedt.buf

    1 declare
    2 cursor_name integer;
    3 rows_processed integer;
    4 cursor c1 is select table_name from user_tables where tablespace_name='COSTPOINT_DATA';
    5 stmt varchar2(1024);
    6 begin
    7 cursor_name := dbms_sql.open_cursor;
    8 for mc1 in c1 loop
    9 stmt := 'select 9 from '||mc1.table_name||' where rownum < 2';
    10 dbms_sql.parse(cursor_name,stmt,dbms_sql.native);
    11 rows_processed := dbms_sql.execute_and_fetch(cursor_name);
    12 if rows_processed = 0 then
    13 dbms_output.put_line(mc1.table_name||' has '||to_char(rows_processed)||' rows');
    14 end if;
    15 end loop;
    16 dbms_sql.close_cursor(cursor_name);
    17* end;
    18 /

    PL/SQL procedure successfully completed.

    SQL> SET SERVEROUTPUT ON
    SQL> /
    TT has 0 rows
    WESTAT_PSR_FINAL_DATA_LOCAL has 0 rows
    WESTAT_PSR_HDR_LOCAL has 0 rows

    PL/SQL procedure successfully completed.

    SQL>

    ------------ end of screen output


    -amar

  9. #9
    Join Date
    Jan 2002
    Posts
    113
    santoshym,


    This is what I get when running your script:


    DECLARE
    *
    ERROR at line 1:
    ORA-00942: table or view does not exist
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 824
    ORA-06512: at "SYS.DBMS_SQL", line 32
    ORA-06512: at line 18

    Also, the syntax of the cursor is wrong. you have the 'order by' before the 'where'.

    I appreciate the input. Thanks

    [Edited by psychofun on 06-11-2002 at 10:18 AM]

    Arm yourself with knowledge

  10. #10
    Join Date
    Mar 2001
    Posts
    314
    Originally posted by psychofun
    THANKS AMAR


    I set serveroutput on and run the procedure. It runs successfully but there is no output. What am I missing?

    Hi psychofun,

    Mind doing a simple test ?

    just create a new empty table (e.g.

    sql> create table junk ( c1 number);

    ) and then run the script - if you get "junk" in your output (no pun ) then maybe you don't have any other tables with 0 rows ???

    Just an afterthought, you did put the proper TABLESPACE name in place right?

    -amar

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