Re: Remove everything from a schema
Date: Sat, 29 Nov 2008 17:36:30 +0100
Message-ID: <49316f8d$0$16001$426a74cc@news.free.fr>
"Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news:
49440570-c6e8-4337-987f-b38bc7a4ff62_at_j35g2000yqh.googlegroups.com...
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.
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
USER is "TEST"
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.
Regards
Michel
Received on Sat Nov 29 2008 - 10:36:30 CST