Re: Remove everything from a schema

On Nov 29, 2:49 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> 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

Michel,

I now understand your original comment regarding object permissions. I was primarily concerned (as indicated in my posts) with the ability of the other users being able to access the objects owned by the dropped user once those objects are recreated through an import, while you are concerned with the recreated user (if permissions even exist to recreate the user) not being able to access objects owned by other users which could have been accessed prior to the drop user and create user sequence, You made excellent points for the reason not to use drop user cascade in this particular case.

Regarding the script, the order in which the table objects are dropped may make a difference. In June/July 2008 there was an interesting thread on the Oracle forums titled "Larger vs. Small data block", where I posted a couple test scripts as well as some analysis unrelated to this thread. Unfortunately, that thread is currently unavailable on Oracle's forum, but is still found in the Google cache: http://64.233.169.132/search?q=cache:Xfeg3l5GATcJ:forums.oracle.com/forums/message.jspa%3FmessageID%3D2591215

The script that I posted which appears in the above link creates a set of tables with primary and foreign keys which closely resemble a couple table definitions in an ERP system. Take a look at the constraints on the PARTS table. I don't believe that the LOCATIONS table may be dropped before the PARTS table due to the FKEY_INSP foreign key, but maybe the "cascade constraints" takes care of this problem?

I thought that you might need something like this before executing the SQL statement which generates the drop object statements: SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DISABLE CONSTRAINT '|| CONSTRAINT_NAME||';' DIS FROM DBA_CONSTRAINTS WHERE OWNER='user' AND R_CONSTRAINT_NAME IS NOT NULL; The above SQL statement would likely need to be modified to use USER_CONSTRAINTS, rather than DBA_CONSTRAINTS, as this user likely does not have DBA permissions.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sat Nov 29 2008 - 08:11:34 CST