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