Re: sql script delete all tables

"teen" <s4012051_at_student.uq.edu.au> wrote in message news:<b5mced$af0$1_at_bunyip.cc.uq.edu.au>...
> >Tim X <timx_at_spamto.devnul.com> wrote in message news:<87of43lptd.fsf_at_tiger.rapttech.com.au>...
> >> Untested and off the top of my head - but it might give you the
> >> idea. ASsuming oracle 8i or better
> >>
> >> set serveroutput on size 1000000
> >> declare
> >> cursor tab_list is
> >> select table_name
> >> from user_tables;
> >> tname tab_list_at_ROWTYPE;
> >> sql_str varchar2(256);
> >> begin
> >> for tname in tab_list loop
> >> sql_str := 'drop table '||tname;
> >> execute immediate sql_str;
> >> end loop;
> >> exception
> >> when others then
> >> dbms_output.put_line(SQLERRM);
> >> end;
> >> /
> >
> >That will almost work, but has 1 bug and some redundant variable
> >declarations, redundant exception handler etc. This is enough:
> >
> >begin
> > for trec in (select table_name from user_tables) loop
> > execute immediate 'drop table '||trec.table_name;
> > end loop;
> >end;
> >/
> >

> 
> OMFG I LOVE YOU 
> You are the winner (I appended cascade stuff):
> 
> begin
> for trec in (select table_name from user_tables) loop
> execute immediate 'drop table '||trec.table_name||' cascade constraint';
> end loop;
> end;
> /
> 
> but yeah, it works sweet
> propz

Some people will call me crazy for saying this, but if you have a lot of tables, sometimes Oracle will hiccup and miss one or two in this kind of loop. I don't know why, I suspect the DBWR just gets overloaded or some internal bug gets tripped over or...? Anyways, be sure and check that they are all gone when you are done.

Personally, I do the drop user thing, but then sometimes it doesn't _add_ all the tables in a script... sigh. I've even seen this in the admin/cat* scripts.

jg

--
@home is bogus.
Be a DBA long enough and you will morph to Murphy.