Re: drop and re-add schema

On 13 nov, 23:09, "Terry Dykstra" <tddyks..._at_forestoil.ca> wrote:
> "Frank van Bortel" <frank.van.bor..._at_gmail.com> wrote in messagenews:fhd3s0$p7a$1_at_news1.zwoll1.ov.home.nl...
>
>
>
> > fitzjarr..._at_cox.net wrote:
> >> On Nov 12, 8:44 pm, Geoff Muldoon <geoff.muld..._at_trap.gmail.com>
> >> wrote:
> >>> In article <1194920706.612932.209..._at_22g2000hsm.googlegroups.com>, ddog
> >>> says...
>
> >>>> What are the commands to drop a schema you are logged in to (or is
> >>>> this even possible?) and recreate it? All of the tables need to be
> >>>> dropped and recreated. Rather than drop 100+ tables one at a time, I
> >>>> was hoping there would be a reasonable way to accomplish this.
> >>> Assuming that you can only operate within the schema (don't have DBA
> >>> rights where you could EXPort the schema and IMPort it with the
> >>> appropriate "with no data" flags), then I guess all you really want to
> >>> do
> >>> is just to TRUNCATE the tables (completely empty the data in them all)
> >>> rather than drop and then recreate them ...
>
> >>> ... create a PL/SQL procedure which has a cursor returning all the table
> >>> names (select TABLE_NAME from USER_TABLES) and loop through this cursor
> >>> using EXECUTE IMMEDIATE dynamic SQL to truncate them.
>
> >>> GM
>
> >> A bit of overkill, really, as a simple SQL*Plus script and a spool
> >> operation would do the same thing without context switching:
>
> >> set head off pagesize 0 linesize 200 trimspool on verify off feedback
> >> off termout off
>
> >> select 'truncate table '||table_name||';'
> >> from user_tables
>
> >> spool trunc_usr_tbls.sql
> >> /
> >> spool off
>
> >> set feedback on termout on head on pagesize 60
>
> >> @trunc_usr_tbls
>
> >> David Fitzjarrell
>
> > Hm, not really:
>
> > begin
> > for i in (select table_name from user_tables) loop
> > execute immediate 'truncate table '||i.table_name;
> > end loop;
> > end;
> > /
>
> > --
> > Regards,
> > Frank van Bortel
>
> > Top-posting is one way to shut me up...
>
> The truncate will fail if you have referential constraints. You have to
> disable those constraints first.
>
> --
> Terry Dykstra

Eehhh - yes.

And one should add error trapping, too:

begin
 for i in (select table_name from user_tables) loop  begin
   execute immediate 'truncate table '||i.table_name;  exception
  when others then
  null;
  end;
 end loop;
end;
/

Maybe David is having a point after all (although he's running into the same issue) Received on Wed Nov 14 2007 - 06:03:33 CST