Re: drop and re-add schema

From: Geoff Muldoon <geoff.muldoon_at_trap.gmail.com>
Date: Tue, 13 Nov 2007 13:44:26 +1100
Message-ID: <MPG.21a3ba989cdb829f989a26@news.readfreenews.net>

In article <1194920706.612932.209790_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 Received on Mon Nov 12 2007 - 20:44:26 CST