Re: Remove everything from a schema

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. Received on Fri Nov 28 2008 - 13:32:46 CST