SQL Script

[psychofun]

I am looking for a script that will find all tables with zero records within a specific tablespace. Any help is appreciated.

[marist89]

Assuming your statistics are up to date, check dba_tables.num_rows and dba_segments.tablespace_name

[psychofun]

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.

[amar]

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

[BV1963]

amar,

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

[psychofun]

THANKS AMAR

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

[santoshym]

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

[amar]

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

[psychofun]

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]

[amar]

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