"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message
news:fhd3s0$p7a$1_at_news1.zwoll1.ov.home.nl...
> 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...
The truncate will fail if you have referential constraints. You have to
disable those constraints first.
--
Terry Dykstra
Received on Tue Nov 13 2007 - 16:09:17 CST
