Re: drop and re-add schema

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 Received on Tue Nov 13 2007 - 07:35:47 CST