| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> QDBA has been banished from my test server (was RE: 8.1.6 upgrade )
Well, that was gruesome.
I couldn't log in as QDBA, so forget about using dbms_job.
I ended up revoking CONNECT, RESOURCE, and every other role it had.
Then I did a shutdown abort, startup restrict.
QDBA's job was still listed.
I took the risk of issuing a DELETE statement against dba_jobs, and against the job$ view. I don't know if that did anything useful but at least there were no rows returned.
I granted connect, resource back to qdba.
I bounced the database again.
Then I managed to log in as QDBA, and as that user I manually (well, using SQL scripts) dropped all of this user's objects.
Then I logged in as SYSTEM and I quite enjoyed deleting this beast from my test machine.
Obviously when you issue a DROP USER command, Oracle doesn't do anything to clean up that user's jobs - it just hangs.
Live and learn, now I know what to do if this happens again - use a liberal does of REVOKE statements, GRANT again, then log on as that user. Drop everything that user owns manually, because DROP USER is too lazy to do it. Once you have done DROP USER's job for it, you can issue the DROP USER command.
Strangely it still took 8i at least ten seconds to drop the user, even though it didn't own anything.
Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)
Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO
E-Mail: boivinp_at_mar.dfo-mpo.gc.ca <mailto:boivinp_at_mar.dfo-mpo.gc.ca>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
INET: BoivinP_at_mar.dfo-mpo.gc.ca
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 28 2001 - 14:49:14 CST
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |