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: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
