fitzjarrell_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...
Received on Tue Nov 13 2007 - 15:10:27 CST
