Recovery after DROP USER SYSTEM? [message #56179] |
Tue, 11 March 2003 04:26 |
Uwe Küchler
Messages: 3 Registered: March 2003
|
Junior Member |
|
|
Hi y'all,
it was a quiet morning today, so I've decided to destroy something beautiful... ;-)
In the FAQ on this site ( http://www.orafaq.org/faqdbase.htm#DEFUSERS ) I read that the user SYSTEM cannot be dropped. This is definitely wrong. "DROP USER system CASCADE;" worked fine on my little 8i production database. Anyone knowing a new job opportunity for me? ;-)))
Seriously, though: If this happens, is there any way of recovering the database with a script (not from a full backup, of course)?
Regards, Uwe.
|
|
|
Re: Recovery after DROP USER SYSTEM? [message #56181 is a reply to message #56179] |
Tue, 11 March 2003 04:49 |
psmyth
Messages: 81 Registered: October 2002
|
Member |
|
|
i haven't tried it myself,but I suspect you're in a 'chicken and egg' scenario... you need 'system' to recover the database (from a script/export etc), but you don't have SYSTEM... I'd delegate this one ;-)
|
|
|
|
|
Re: Recovery after DROP USER SYSTEM? [message #56196 is a reply to message #56183] |
Wed, 12 March 2003 03:06 |
Uwe Küchler
Messages: 3 Registered: March 2003
|
Junior Member |
|
|
Nice one, thanks for your efforts. I suppose there's no better chance to recover than that.
Let me add just two more statements to the script:
----------------------------------------------------------------------
-- Find grants on objects to other users
-- Grants on tables/views/procedures
select 'grant '||privilege||' on '||table_name||' to '||grantee||
decode(GRANTABLE, 'YES', ' WITH GRANT OPTION')||';'
from sys.dba_tab_privs
where owner = upper('&oldname');
-- Grants on columns
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to '||grantee||
decode(GRANTABLE, 'YES', ' WITH GRANT OPTION')||';'
from sys.dba_col_privs
where owner = upper('&&oldname');
----------------------------------------------------------------------
You could also add the grant option line to the rest of your script.
Kind regards,
Uwe
|
|
|