SQL & PL/SQL :: Trigger All DML At All Table Events?
May 4, 2011
i want to write, just ONE trigger that handles all DMS events at schema level and writes the field changes to a specific log table. i wanna to write like 'FOR EACH FIELD' as for each row (& also each table). it turns in the fields section and if the 'old' and 'new' values isnt same, it triggers this function & writes changes to log.
ex. is like:
FOR EACH ROW
FOR EACH FIELD
if (old.value != new.value)
insert into LOG(..., old.name,new.name) values (..., old.value, new.value)
NEXT // FIELD
NEXT // ROW
I have a table with around half a million rows. I would like to determine how many sequential events occur per device per unique group of events. The requirement is that the events continue to occur within 1 second of each other, every second, with no gaps. For example, in the sample below there is a sequence of 2 events for device 2737, and another sequence of 5 events for device 2737. If there was a 1 second interval with no event (Signal_time) then that would be the end of one group of events, this is also shown below.
Using Oracle 11.2.0.2.0
DEVICE_ID SIGNAL_TIME 4401 17-JUN-12 12.59.59 PM 2332 02-MAY-12 12.59.59 PM 1086 30-MAY-12 12.59.58 PM 2737 17-JUN-12 12.59.58 PM -- start of one group / sequence of events 2737 17-JUN-12 12.59.58 PM -- end of one group / sequence of events
[Code]...
-- start of one group / sequence of events
2737 16-JUN-12 11.59.59 PM 2737 16-JUN-12 11.59.59 PM 2737 17-JUN-12 12.00.00 AM 2737 17-JUN-12 12.00.01 AM
-- end of one group / sequence of events
2737 17-JUN-12 12.00.03 AM
-- this is excluded from the above group of 5 events, but this may be the start of another group
In my awr reports I have CPU time(in seconds) as one of top 5 timed events.Does that mean oracle is waiting for my cpu to execute the statements.But my cpu usage is 0-10% always
registration forms for events (like Technology days and so on) with Apex? I want to build up something like that where customers can register for several events.
What is needed:
registration form with a limited registration (e. g. 80 customers) e. g. create a trigger?
Can I disable tracing just on deadlocks events on 11gR2?One of our applications provoke several deadlocks. I've warned the developers team several times, but it's taking time to solve it. However, these deadlocks generate trace files of several sizes (from 300M to 3G ..)The Deadlock Graph on them are very useful but the PROCESS STATE section are too long, and until the developers team fix the problem they are just filling up my user dump dest. Can I disable them or disable the PROCESS STATE section from them?
I am using 11.2.0.3.0 version of oracle. As told by the DBAs that two days back the system realised high wait_class i.e 'Application' wait, and its a lot high than regular value as per our system and we need to dig it down, to avoid any future issue. now using below query , i found that the high wait time due to wait class 'Application' is actually belongs to particular event 'SQL*Net break/reset to client', and the sample time was '9 AM' morning.
select time, round(max(case when event = 'SQL*Net break/reset to client' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end) ,2) SQL_break_reset_client, round(max(case when event = 'Wait for Table Lock' then time_waited_delta/1e3/decode(total_waits_delta,0,1,nvl(total_waits_delta,1)) end),2) Wait_for_Table_Lock , round(max(case when event = 'enq: KO - fast object checkpoint' then [code]....
Now i want to track it down further to the 'session/sql query/application' level resulting into such high value of wait time. so i queried, dba_hist_ active_ session_history, for the same sample duration (8.30 to 9 am) having event 'SQL*Net break/reset to client', and got two sessions(123,154) and their serial# , but there i got one sql_id(3ahgrey10ogh i.e a 'SELECT' query) specific to one session(123) but for other(125) no sqlid, and also SUM (wait_time + time_waited) is showing '0'.
Then i just removed the sampletime filter from the below query and observed that the same 'same session(123)+session serial#' was activated since 4 days back and was experiencing same waitevent 'SQL*Net break/reset to client', (it was having normal wait event 'db file sequential read' for sometime then after it went for this event ).
We have been using XLA to capture events on TimesTen for a while now without any issues. We were on TimesTen 7.0.5 and then moved to 11.2.2.3.0 and now 11.2.2.4.0. The XLA processes used Java 6 64 bit and works well with TimesTen versions 11.2.2.4.0 - 64 bit and 11.2.2.3.0 - 64 bit, without any issues.
However, we recently upgraded to Java 7 64 bit both during build time of our XLA processes as well during runtime. The problem we see now is that XLA process upon startup processes events for a while and then after that doesnt receive any events. The process doesn't throw any errors/exceptions. If we restart the XLA process, all the unprocessed events are received and then it behaves normally for a while after which it fails to receive any further events.
Is there any issue with Java 7 64 bit and the TimesTen XLA API? I read the TimesTen 11.2.2.4.0 manual and it says that Java 7 and the ttjdbc7.jar have been certified to work well. Was just wondering if there were any other issues.
In addition, to debug the problem, we ran XLA on a single node and on a two safe setup. The same observations are noted. On Java 6, the process runs just fine capturing all events without any issues, but with Java 7, the issues persist.
In addition, we are only performing updates/inserts on to TimesTen, no delete actions. The OS on which TimesTen's and XLA runs is Red Hat Enterprise Linux Server release 5.7 (Tikanga).
I have enabled the below trace event before running query on my session.
ALTER SESSION SET events = '19027 trace name context forever, level 0x1000';ALTER SESSION SET tracefile_identifier = store_trace;
Then I ran my query, and upon the finishing of the query, I ran the below to disable trace.
ALTER SESSION SET events = '19027 trace name context off, level 0x1000';
Now I checked back on the trace folder in my Oracle DB directory structure, but cannot find any file created by using the above set tracefile_identifier. I had previously run the query with "sql_trace" ON with tracefile_identifier, and successfully got the tracefile generated by using the identifier.
11.2.0.3 EVENTS: Null(blank) or called a 'null event' See link. Checked the docs. I don't see anythingI think a null (blank) is for an idle wait since per the V$ACTIVE_SESSION_HISTORY spec these are not record. Am I correct?What is a 'null event'. I see a paper from Oracle magician dating to 9i that says this is an 'oracle goof'. Descriptions of Wait Events
I have a partitioned table with degree for parallelism defined as 10.
I am getting maximum wait events on PX Deq: Execute Reply.For 2 hours trace the wait event is almost 1.5 hour.
I have done some seraching and i found this.
Quote: In principle:A parallel query against a partitioned table will use one slave per partition if the query is thought to span multiple partitions, and it can use all slaves on a single partition if the query is thought to target just one partition. Unfortunately, this is NOT strictly true.
It is possible for the optimizer to decide to use parallelism at degree M when accessing N partitions. Sometimes this can lead to very inefficient, brute-force, processing when a more efficient path is available. This can be a particular problem with multi-table joins that should be partition-wise joins. You may be better off leaving the tables defined as non-parallel and adding explicit parallel hints to the code for critical queries.
So i have following questions 1) What is the meaning of PX Deq: Execute Reply. 2) Is this not recommended to use DEGREE clause in Partitioning. 3) Defining DEGREE clause in partioning of table will automatically executes DML on table PARALLELY same a PARALLEL hint clause
I ran an AWR report. The database looks fine, but a data load that loaded 1 Million rows an hour is now doing 500K per hour.
Wait Class Waits %Time -outs Total Wait Time (s) Avg wait (ms) %DB time DB CPU 224 80.70 Other 2,668 0 28 10 9.99 System I/O 4,753 0 9 2 3.23 Administrative 1 0 6 5543 2.00 Commit 357 0 4 11 1.46
[code]....
The network value for wait: 630,601. What does this mean? Anything I should look at? When it was 1million per hour, the value was 4,563,000.
Top 5 Timed Foreground Events
Event Waits Time(s) Avg wait (ms) % DB time Wait Class DB CPU 224 80.70 unspecified wait event 2,666 28 10 9.99 Other control file sequential read 4,753 9 2 3.23 System I/O switch logfile command 1 6 5543 2.00 Administrative log file sync 357 4 11 1.46 Commit
We are using 11.2.0.3.0 on solaris 10 facing slow performance, following are the Wait Events in AWR report, Also if any specific document to analyze AWR report and to pin point the performance bottleneck.
Foreground Wait Events ********************** Avg %Time Total Wait wait Waits % DB Event Waits -outs Time (s) (ms) /txn time -------------------------- ------------ ----- ---------- ------- -------- ------ direct path read 308,729 0 21,191 69 58.0 39.5 db file sequential read 208,754 0 3,742 18 39.2 7.0 cursor: pin S 19,541,899 0 2,561 0 3,668.5 4.8 [code]....
I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .
If their is any problem in insert in to table t2 , the truncate command should not work .
Truncate command should work only after successfully insert command .
Oracle 11.2 - The goal is to create a trigger on table and anytime an update, delete or insert is done on the table, write values to a second table. I have the trigger and it works except it is not loading my col1/PK values. I understand I need to do a new/old value. Col1 is my PK on Table that I want to load anytime there is an update/delete/insert on the table. How do I code the old/new variable?
My CREATE OR REPLACE TRIGGER TRIGGER_NAME AFTER INSERT OR UPDATE OR DELETE ON TABLE_NAME FOR EACH ROW DECLARE v_col1 TABLE_NAME.COLUMN%TYPE; BEGIN [code]...
I am ceating a trigger to...when I update or insert a record in table and if salary of that record is less than 1600 than it should be updated to 2000. My trigger is
CREATE or REPLACE TRIGGER myTrigger BEFORE UPDATE OR INSERT ON newemp FOR EACH ROW BEGIN
[code]...
There are no errors with trigger while compiling, but when I update or insert a record, it does not update the table. It looks like the trigger is being ignored.
CREATE OR REPLACE TRIGGER tri1 AFTER UPDATE ON dept FOR EACH ROW declare pragma AUTONOMOUS_TRANSACTION; BEGIN execute immediate 'create table dept_dum as select * from dept'; END; /
SQL> update dept set deptno=23 where deptno=10; update dept set deptno=23 where deptno=10 * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SCOTT.TRI1", line 6 ORA-04088: error during execution of trigger 'SCOTT.TRI1'
I am running this as scott user
My requirement is to
create table as select * from table_name where flag=1
This has to be done parallel for all the tables for which this flag is enabled and by that trigger delete all those rows which were backed up as table.
Having trouble creating a trigger to populate another table.
The SQL:
CREATE OR REPLACE TRIGGER "P_M_YES" AFTER INSERT OR UPDATE ON DOMAIN REFERENCING NEW AS NEW.P_M AND OLD AS OLD.P_M FOR EACH ROW WHEN (NEW.P_M = YES) BEGIN INSERT INTO PAGE_MAKER VALUES(:NEW.D_NAME, :NEW.USER_ID); END P_M_YES;
I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..
CREATE OR REPLACE PACKAGE BODY trigger_api AS PROCEDURE tab1_row_change (p_numass IN varchar2, p_datcre IN date) IS BEGIN INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre); END tab1_row_change; [code]....
Doing this process from code is not an option and MUST happen automatically via triggers.The mutating trigger error can sometimes be avoided: URL....
I am trying to create a trigger which does the following : A flag in the initial able is set to Y. When this happens, the record needs to be inserted into a history table and then DELETED from the calling table.
It must happen in triggers, but I keep getting the mutating error.I have tried to use a Compound trigger, but with no luck and just dont really understand how to get this to work.
Doing this process from code is not an option and MUST happen automatically via triggers.
We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.
I can via SQL add a part to the bill of materials and reserve the part. But then the part needs to be issued to make it disappear from the stock.
I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesn't disappear from the stock. So my guess is that there is a trigger or function somewhere that I need for this.
I have to write a trigger on a table which contain lot of parameters.But i need to pick a specific row and check that without disturbing other stuffs.Is there a way to write Before update trigger on a particular rows filtering the unneccasary rows.
The requirement is when user update the date from front end (Java application) the trigger should check the date and validate that it should be month end date. For example.
1)04/21/2012 wrong date 2)04/30/2012 correct date 3)03/29/2012 Wrong date 4)03/31/2012 correct date
