Home » RDBMS Server » Server Administration » Is it possible to write Trigers on Data Dictionary Tables & Views? (Oracle 10gR2 Oracle11gR2)
Is it possible to write Trigers on Data Dictionary Tables & Views? [message #525277] Fri, 30 September 2011 10:01 Go to next message
bhavinjparmar
Messages: 8
Registered: September 2011
Location: London
Junior Member
Hi All!

Is it possible to create triger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self? If it is possible how and if not why it is not?
Re: Is it possible to write Trigers on Data Dictionary Tables & Views? [message #525278 is a reply to message #525277] Fri, 30 September 2011 10:04 Go to previous messageGo to next message
BlackSwan
Messages: 22790
Registered: January 2009
Senior Member
please post reproducible test case.
Re: Is it possible to write Trigers on Data Dictionary Tables & Views? [message #525279 is a reply to message #525278] Fri, 30 September 2011 10:17 Go to previous messageGo to next message
bhavinjparmar
Messages: 8
Registered: September 2011
Location: London
Junior Member
I have particular criteria for each case (i.e. Sessions, File States, Progress of Job and Tasks, Locks, Backup Status, Memory Usgae and Allocation, System and Session Parameters, SQL Executions). Is it possible to fire triggers, for example if Any memory segment is used 80% of allocated memory then it should fire.

I hope I am able to make it clear.
Re: Is it possible to write Trigers on Data Dictionary Tables & Views? [message #525281 is a reply to message #525279] Fri, 30 September 2011 10:23 Go to previous messageGo to next message
Michel Cadot
Messages: 59143
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is it possible to create triger on the various tables and views exists (i.e. dynamic performance views) in data dictionary

No.

Quote:
for example if Any memory segment is used 80% of allocated memory then it should fire.

This is example is not a good one (or explain what you mean), anyway create a job that periodically checks what you want to check.

Regards
Michel
Re: Is it possible to write Trigers on Data Dictionary Tables & Views? [message #525408 is a reply to message #525281] Sun, 02 October 2011 11:07 Go to previous messageGo to next message
Berny
Messages: 3
Registered: June 2009
Location: Munich
Junior Member
If your goal is to avoid excessive resource usage you should use better use one of the following:

  • Resource Parameters of User Profile
  • Database Resource Manager

Re: Is it possible to write Trigers on Data Dictionary Tables & Views? [message #525710 is a reply to message #525408] Tue, 04 October 2011 13:11 Go to previous messageGo to next message
bhavinjparmar
Messages: 8
Registered: September 2011
Location: London
Junior Member
Hi!

I want to write a triger, when any new record is inserted to Outstanding alerts DBA_OUTSTANDING_ALERTS, i want to execute the trigger content. This is one of the example.

Regards,
Bhavin
Re: Is it possible to write Trigers on Data Dictionary Tables & Views? [message #525718 is a reply to message #525710] Tue, 04 October 2011 14:54 Go to previous message
John Watson
Messages: 4566
Registered: January 2010
Location: Global Village
Senior Member
Hi - I can answer your last question regarding dba_outstanding_alerts. The alert system is based on advanced queueing: mmon raises alerts by enqueuing a message to the sys.alert_que queue. You need to write an alert handler which will subscribe to the queue and do something when a message arrives. The PL/SQL isn't that difficult, though you do need to understand advanced queueing. Read up on it, and have a go.
Previous Topic: dba_free_space
Next Topic: Issue with logging in sql with /as sysdba
Goto Forum:
  


Current Time: Fri Sep 19 15:18:13 CDT 2014

Total time taken to generate the page: 0.27098 seconds