Re: Empty Table

On 01/24/2005 12:35:08 AM, J.Velikanovs_at_alise.lv wrote:
> I think he most effective way to archive your coal is something like:
>=20
> declare
> rid rowid;
> v_fname varchar2(60);
> begin
> for v_tab in (select table_name from user_tables) loop
> v_fname:=3Duser||'.'||v_tab.table_name;
> execute immediate 'select rowid from '||v_fname||' where rownum=3D1'
> into
> rid;
> end loop;
> exception
> when
> no_data_found
> then dbms_output.put_line(v_fname||' is empty');
> end;
> /
>

Oh, I can do one better. Here is a little script that will very quickly find all tables empty in a given schema.

declare
cmd varchar2(128):=3D'truncate table ';
cursor csr is select table_name from user_tables; begin
for c in csr loop
  execute immediate cmd||c.table_name;
end loop;
dbms_output.put_line('All tables are empty!'); end;
/
--=20
Mladen Gogala
Oracle DBA

--
http://www.freelists.org/webpage/oracle-l