SQL Script

I checked and have several empty tables. I did change the tablespace name. I ran this on dba_tables instead of user_tables, is that a problem?

That was the problem. I tried it as system on dba_tables and it failed. I tried it as another user and ran it using user_tables and it worked. How can I use it on dba_tables so I can get a report on all the empty tables for all the users?

For all users. Run from a DBA a/c:


declare
cursor_name integer;
rows_processed integer;
cursor c1 is select table_name,owner from dba_tables
where owner not in ('SYS','SYSTEM');
stmt varchar2(1024);
begin
cursor_name := dbms_sql.open_cursor;
for mc1 in c1 loop
stmt := 'select 9 from '||mc1.owner||'.'||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.owner||'.'||mc1.table_name||' has '||to_char(rows_processed)||' rows');
end if;
end loop;
dbms_sql.close_cursor(cursor_name);
end;


-amar

I have created a table and ran a procedure.No output. What am I doing wrong?

PL/SQL procedure successfully completed.

ctlsst:SYSTEM> create table sysadm.ttt (g varchar (1));

Table created.

ctlsst:SYSTEM> edit
Wrote file afiedt.buf

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

PL/SQL procedure successfully completed.

WORKS GREAT!

Thank you for your help.

Dean

[Edited by psychofun on 06-11-2002 at 12:25 PM]

BV1963

Did you create the table as SYSTEM? If you did then it will not show up in the output because the cursor filters out tables owned by SYS and SYSTEM. Create a table by a different user and try it. Worked OK for me.

AMAR,
lets say that I want to find all tables with more than 5 rows. How can I tailor the script for this? I changed a couple values but couldn't get it to work.

The following should work :)

declare
ROWS_IN_TABLE integer := 10;
cursor_name integer;
rows_processed integer;
cursor c1 is select table_name,owner from dba_tables
where owner not in ('SYS','SYSTEM');
stmt varchar2(1024);
xrows number;
begin
cursor_name := dbms_sql.open_cursor;
for mc1 in c1 loop
xrows := 0;
stmt := 'select count(*) from '||mc1.owner||'.'||mc1.table_name||' where rownum <= 1+'||ROWS_IN_TABLE;
dbms_sql.parse(cursor_name,stmt,dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(cursor_name, 1, xrows);
rows_processed := dbms_sql.execute_and_fetch(cursor_name);
DBMS_SQL.COLUMN_VALUE(cursor_name, 1, xrows);
IF ROWS_IN_TABLE = 0 THEN
if xrows = 0 then
dbms_output.put_line(mc1.owner||'.'||mc1.table_name||' has '||ROWS_IN_TABLE||' rows');
end if;
else
if xrows > ROWS_IN_TABLE then
dbms_output.put_line(mc1.owner||'.'||mc1.table_name||' has more than '||ROWS_IN_TABLE||' rows');
end if ;
end if;
end loop;
dbms_sql.close_cursor(cursor_name);
end;


set the ROWS_IN_TABLE value to whatever you want (e.g. 5 as you stated).

-amar

THANKS AGAIN AMAR!

It works great. This is a good time saver.

All the best,
Dean

Hi

Your are right order by clause should have been after where clause that is the only error

Here is the code

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 where tablespace_name = 'TABLESPACE_NAME' order by table_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;
/

Rest of the code works fine. The mistake I made is did not test it after modifying it slightly.

Regards,

Santosh