Re: Remove everything from a schema

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 29 Nov 2008 07:49:05 +0100
Message-ID: <4930e5e2$0$6828$426a74cc@news.free.fr>

"Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news: 5531a5e5-995c-4926-b4ba-77edd63e02ed_at_h5g2000yqh.googlegroups.com... On Nov 28, 11:38 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> Yes, it is an alternative.
> The flaw of DROP USER is that it implies that you regrant each privilege
> you previously had after recreating the user.
>
> Regards
> Michel

Michel,

I am fairly certain that when the data is imported using imp (or impdp on 10g and above), that the grants which were previously assigned in the database from which the export was created will be recreated during the import. If I remember correctly, there is a problem with third party grants not being retained after an import (UserA gives permissions on UserB's objects so that UserC may access those objects).

That said, the method that you provided is a great (impressive) alternative to the method which I provided. Just a couple comments: * The OP is running Oracle version 9.2.0.1.0, so he is able to skip the step to purge the recycle bin - I assume that you intentionally separated the purge operation from the drop operation so that the same code could easily be adapted to work with versions prior to Oracle 10g.
* If there are foreign key constraints between objects, it is possible that some of the drops of the parent tables will fail. Maybe it would be possible to modify the script to disable the foreign key constraints before the drops?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.


Charles,

I was talking about the privileges the account you delete the objects has on other schema and system privileges. With my test user that uses this script, I can't drop grant myself any privilege and of course can't drop and recreate my account. These are the points for me to create this script.

Yes, I separate "purge recyclebin" to be able to execute the script from any version. It could be enhanced to prevent from the error raising in case of version lower than 10g but for myself I don't care having a "SP2-0042: unknown command "recycle bin"" or "ORA-00900: invalid SQL statement" error in my script.

The script handles all cases I thought about with the "decode" part. For instance, for foreign keys, the table and cluster parts add the clause "cascade constraints".
Maybe I missed some. I will be grateful if you find and mention them.

Regards
Michel Received on Sat Nov 29 2008 - 00:49:05 CST

Original text of this message