Re: drop and re-add schema

On Nov 14, 6:03 am, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> 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)- Hide quoted text -
>
> - Show quoted text -

I did not take into account any constraints and, yes, the truncates will fail on parent tables when the child tables have data. Presumably a second run of the script would truncate the parents after the children have been successfully truncated.

My issue was with the original respondent blindly recommending PL/SQL to do such a job when, in many cases, a simple SQL*Plus script would suffice and probably be a bit more performant.

David Fitzjarrell Received on Wed Nov 14 2007 - 08:50:30 CST