Re: Remove everything from a schema

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Sat, 29 Nov 2008 17:36:30 +0100
Message-ID: <49316f8d$0$16001$>

"Charles Hooper" <> a écrit dans le message de news: 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


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:

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.

The script well works with this test case:

TEST> select object_name, object_type from user_objects order by 1, 2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
IND_LOCATIONS_1                INDEX
IND_PARTS_1                    INDEX
IND_PARTS_2                    INDEX
IND_PARTS_3                    INDEX
IND_PARTS_4                    INDEX
IND_PARTS_5                    INDEX
IND_PARTS_6                    INDEX
IND_PARTS_7                    INDEX
IND_PO_HEADER_1                INDEX
IND_PO_HEADER_2                INDEX
IND_PO_HEADER_3                INDEX
IND_PO_HEADER_4                INDEX
IND_PO_LINE_1                  INDEX
IND_PO_LINE_2                  INDEX
IND_PO_LINE_3                  INDEX
IND_PO_LINE_4                  INDEX
LOCATIONS                      TABLE
NARROW                         TABLE
PARTS                          TABLE
PARTS_TEMP                     TABLE
PO_HEADER                      TABLE
PO_HEADER_TEMP                 TABLE
PO_LINE                        TABLE
PO_LINE_TEMP                   TABLE
SYS_C005758                    INDEX
SYS_C005762                    INDEX
SYS_C005768                    INDEX
SYS_C005808                    INDEX
SYS_C005863                    INDEX
SYS_C005897                    INDEX
UMS                            TABLE
VENDORS                        TABLE
VENDORS_TEMP                   TABLE

33 rows selected.

TEST> @delme
drop TABLE LOCATIONS cascade constraints; drop TABLE NARROW cascade constraints;
drop TABLE PARTS cascade constraints;
drop TABLE PARTS_TEMP cascade constraints;

drop TABLE PO_HEADER cascade constraints;
drop TABLE PO_HEADER_TEMP cascade constraints;
drop TABLE PO_LINE cascade constraints;
drop TABLE PO_LINE_TEMP cascade constraints;
drop TABLE UMS cascade constraints;
drop TABLE VENDORS cascade constraints;
drop TABLE VENDORS_TEMP cascade constraints; TEST> select object_name, object_type from user_objects order by 1, 2;

no rows selected

The only cases I see where it will not work is very unusual ones like you created index on tables you don't own:

TEST> sho user
TEST> create index t_i on michel.t (val);

Index created.

TEST> select object_name, object_type from user_objects order by 1, 2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
T_I                            INDEX

1 row selected.

TEST> @delme
TEST> select object_name, object_type from user_objects order by 1, 2;

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
T_I                            INDEX

1 row selected.

I will modify the script to handle this case but there may be other things like that.

Michel Received on Sat Nov 29 2008 - 10:36:30 CST

Original text of this message