Re: Empty Table

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: J.Velikanovs@xxxxxxxx
  • Date: Mon, 24 Jan 2005 05:52:47 +0000

On 01/24/2005 12:35:08 AM, J.Velikanovs@xxxxxxxx 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


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

Other related posts: