Re: Quick way to drop all objects in a schema

From: chet justice <chet.justice_at_gmail.com>
Date: Thu, 19 Nov 2009 16:53:44 -0500
Message-ID: <8311a5b60911191353y78ee7067qc0b51bb5094de3cc_at_mail.gmail.com>



I too prefer a DROP USER CASCADE, but for sandbox purposes...I typically use this:

http://www.oraclenerd.com/2009/09/how-to-clean-your-schema.html

The only object types I haven't taken account for (yet) are Queues.

My script will remove scheduled jobs (as well as programs and chains) and regular old DBMS_JOBs.

On Thu, Nov 19, 2009 at 3:49 PM, Michael Fontana < michael.fontana_at_enkitec.com> wrote:

>
> It's not often a problem, but dropping objects in this manner can be
> inefficient ( I can recall waiting on numerous fet$ deletes) and could be
> difficult to recover from should it fail.
>
> Dropping a user "cascade" utilizes Oracle code and will be consistent AND
> complete from release to release. Writing a script such as this may not
> work when new object types or behaviors come into existence in Oracle 12, 13
> etc.
>
> I also don't like the idea of passively leaving links or other objects
> assumed to be valid in a schema; why not just recreate needed objects
> properly, and exclude the rest. This avoids the chance that a new one was
> created in the source, but unwanted in the target. It's also more secure.
>
> I would certainly be in favor of first cloning an existing user as a new
> one, and then dropping the old, unless resources are unavailable to take
> such action.
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 19 2009 - 15:53:44 CST

Original text of this message