Instead of counting each and every table -- what I would recommend is using some builtin functions to find good CANDIDATE tables and then count their rows.

Counting every table would be pretty resource intensive. Instead, we can quickly find out how many blocks are on the free list for a table, how many blocks have never been used and how many blocks have been allocated (we don't need to analyze to get that). We can add the freelist blocks with the unused blocks and if that is 1 less then allocated -- that table is POTENTIALLY empty (we can have blocks on the freelist that contain data). Those are the tables we must count. We can use DBMS_SQL to count those tables. Here is a routine to do just that. Leave it as an ANONYMOUS block -- do not make it a procedure (roles are not active in procedures, you'll have issues with those privs not being active in doing this particular operation). If you run this as SYS, it'll show you the empty tables. Just change my message to be your truncate table and spool the output. It should do what you want. Run it first to get comfortable with what it does and how it does it and when you trust that it finds empty tables -- let it generate the truncate statements.


sys@ORA734.US.ORACLE.COM> declare
2 l_free_blks number;
3 l_total_blocks number;
4 l_total_bytes number;
5 l_unused_blocks number;
6 l_unused_bytes number;
7 l_LastUsedExtFileId number;
8 l_LastUsedExtBlockId number;
9 l_LAST_USED_BLOCK number;
10
10 procedure p( p_str in varchar2 )
11 is
12 begin
13 dbms_output.put_line( p_str );
14 end;
15
15 function get_row_cnts( p_tname in varchar2 )
16 return number
17 as
18 l_theCursor integer
19 default dbms_sql.open_cursor;
20 l_columnValue number default NULL;
21 l_status integer;
22 begin
23 dbms_sql.parse( l_theCursor,
24 'select count(*) from ' || p_tname,
25 dbms_sql.native );
26
26 dbms_sql.define_column
27 ( l_theCursor, 1, l_columnValue );
28 l_status := dbms_sql.execute(l_theCursor);
29 if ( dbms_sql.fetch_rows(l_theCursor) > 0 )
30 then
31 dbms_sql.column_value
32 ( l_theCursor, 1, l_columnValue );
33 end if;
34 dbms_sql.close_cursor( l_theCursor );
35 return l_columnValue;
36 end;
37 begin
38 for x in ( select owner, table_name
39 from dba_tables
40 where owner NOT LIKE 'SYS%' )
41 loop
42 dbms_space.free_blocks
43 ( segment_owner => x.owner,
44 segment_name => x.table_name,
45 segment_type => 'TABLE',
46 freelist_group_id => 0,
47 free_blks => l_free_blks );
48
48 dbms_space.unused_space
49 ( segment_owner => x.owner,
50 segment_name => x.table_name,
51 segment_type => 'TABLE',
52 total_blocks => l_total_blocks,
53 total_bytes => l_total_bytes,
54 unused_blocks => l_unused_blocks,
55 unused_bytes => l_unused_bytes,
56 LAST_USED_EXTENT_FILE_ID =>l_LastUsedExtFileId,
57 LAST_USED_EXTENT_BLOCK_ID =>l_LastUsedExtBlockId,
58 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
59
59 if ( l_free_blks + l_unused_blocks =
60 l_total_blocks-1 )
61 then
62 if ( get_row_cnts
63 ( x.owner || '.' || x.table_name ) = 0 )
64 then
65 p( x.owner || '.' || x.table_name ||
66 ' Empty Candidate' );
67 p( l_free_blks || ' on freelist, ' ||
68 l_unused_blocks || ' never used, ' ||
69 l_total_blocks || ' allocated' );
70 end if;
71 end if;
72 end loop;
73 end;
74 /
SCOTT.TKYTE_FT_TRANSFERS Empty Candidate
0 on freelist, 1 never used, 2 allocated
SCOTT.X1 Empty Candidate
1 on freelist, 0 never used, 2 allocated
SCOTT.TESTME Empty Candidate
1 on freelist, 0 never used, 2 allocated
SCOTT.P Empty Candidate
1 on freelist, 0 never used, 2 allocated
SCOTT.COMO Empty Candidate
0 on freelist, 1 never used, 2 allocated
SCOTT.MA_SODREC Empty Candidate
0 on freelist, 1 never used, 2 allocated
TKYTE.TEMP Empty Candidate
1 on freelist, 0 never used, 2 allocated
TKYTE.TEST1 Empty Candidate
0 on freelist, 1 never used, 2 allocated
TKYTE.TEST2 Empty Candidate
0 on freelist, 1 never used, 2 allocated
OPS$TKYTE.EMP Empty Candidate
0 on freelist, 1 never used, 2 allocated
OPS$TKYTE.T Empty Candidate
0 on freelist, 1 never used, 2 allocated
SCOTT.BONUS Empty Candidate
0 on freelist, 1 never used, 2 allocated
SCOTT.C Empty Candidate
1 on freelist, 0 never used, 2 allocated
TKYTE.RIATTC Empty Candidate
0 on freelist, 23299 never used, 23300 allocated

PL/SQL procedure successfully completed.