-
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
-
Assuming your statistics are up to date, check dba_tables.num_rows and dba_segments.tablespace_name
Jeff Hunter
-
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
-
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
-
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!
-
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
-
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
-
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
-
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
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|