RE: Deleting from SYS tables

From: Daniel Fink <daniel.fink_at_optimaldba.com>
Date: Fri, 25 Feb 2011 18:05:24 +0000
Message-ID: <20110225180524.29028.qmail_at_optimaldba.com>

I don't know of any specific documentation, though an SR w/Oracle Support would be pretty definitive. I've worked on issues where they had me directly update sys tables, but it was for specific bugs with specific instructions.

I recall (not sure where I came across this bit of stored information...though the name Tom Kyte is somehow attached to it) that sys tables, such as job$, are not read into the buffer cache for recursive sql, but are read into the dictionary cache and modified there. No read consistency, no option to rollback, etc. If there were two sessions updating jobs, one directly modifying the table (read into buffer cache) and one using the proper method (dbms_job, recursive sql and the dictionary cache), you would end up with a missing modification.

Regards,
Daniel Fink

• Original Message ------- On 2/25/2011 4:53 PM Jay Hostetter wrote: I had a user that deleted records from DBA_JOBS. Unfortunately, the application gives the user DBA rights so this is beyond my control. *I* know it is bad practice to directly mess with SYS tables, but is there any documentation from Oracle that spells this out? Or do they assume that most apps aren't crazy enough to handout DBA rights? In any case, I need to steer some users towards APIs (like DBMS_JOB) instead of using a sledge hammer. I'd like a little documentation to back me up.

Thank you,
Jay
--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 25 2011 - 12:05:24 CST