Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Adam.

Asked: December 19, 2002 - 2:18 pm UTC

Last updated: August 02, 2013 - 5:22 pm UTC

Version: 9.1.2

Viewed 1000+ times

You Asked

Quick question if you don't mind..

I keep an audit of when PL/SQL code was changed in the database (We have an amazing Mr Nobody who compiles/drops code). We have lines within the code which contains the CVS version and check in date and time etc. I already audit this information by looping around DBA_SOURCE once a day, and note this info and the compile time etc.

I however want to run this procedure whenever dba_source is modified. I have been advised that adding a trigger to DBA_SOURCE would be wrong and is just something which you shouldn't do (Touching a system table).

I personlly don't see the harm so, is it bad practice and if so why?

Have a good Christmas, and many thanks.



and Tom said...

(9.1.2 is not a version ;)

see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6020239969856 <code>

for a solution to this problem.



Now, as for putting triggers on SYS owned things, the very first thing that would happen is:


1 create or replace trigger x
2 instead of insert on dba_source
3 begin
4 null;
5* end;
sys@ORA817DEV.US.ORACLE.COM> /
create or replace trigger x
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS


it is 100% forbidden.

The second issue would be that we don't INSERT into dba_source (or update or delete it). It is a view. It is the base tables we work on - but then you are back to the 4089 error ;)


DDL or DML against the data dictionary is among the set of things that are always really bad ideas.

If you ever feel tempted to touch anything owned by SYS or SYSTEM stop, don't, do something else



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

oracle support instruction to create trigger on sys.aud$

Ken Hughes, July 26, 2013 - 12:15 am UTC

I created the trigger as instructed and when I create it on sys.aud$ I get this error message:

CREATE OR REPLACE TRIGGER aud_replace_credit_card_nmbr
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS
Tom Kyte
August 02, 2013 - 5:22 pm UTC

Oracle support told you to create a trigger on a sys owned object????

really?

you cannot create triggers on objects owned by sys.


please refer me to the note or SR where they told you to do this...
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library