Script to Generate Trace Enable Statment in Oracle

SQL: 

SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid ,

'EXECUTE dbms_system.set_sql_trace_in_session('|| s.SID||','||s.serial#||',true);' ENAB,

'EXECUTE dbms_system.set_sql_trace_in_session('|| s.SID||','||s.serial#||',true);' DISAB

FROM   v$session s,  

              v$process p 

WHERE p.addr = s.paddr   and s.username = 'SYS'  -- and s.Program='SQL Developer'

order by sid  

Continue Reading

Tracing the Oracle database

Tracing the Oracle database:-

First open the sql developer and login as a user with administrator previlage. eg:-system

1)  Enable Oracle database to gather statistics (on session or system level)

ALTER SYSTEM SET  timed_statistics = true; 

ALTER SESSION SET  timed_statistics = true;

2) Find the SID, SERIAL# for a specific session you want to monitor  

SELECT username, sid, serial#, program FROM v$session WHERE username = <User_Name>; 

3)Enable the tracing for this session (  must be logged as SYSDBA ) 

EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, true); 

Bind Variables with Value:

EXECUTE dbms_monitor.session_trace_enable(41, 2216, waits=>true, binds=>true) ;

Query to Generate trace command based on the current session ids:

 SELECT p.tracefile,s.username, s.SID, s.serial#, s.PROGRAM, p.spid ,
'EXECUTE dbms_monitor.session_trace_enable('|| s.SID||','||s.serial#||', waits=>true, binds=>true) ;' ENAB,
'EXECUTE dbms_monitor.session_trace_enable('|| s.SID||','||s.serial#||', waits=>false, binds=>false) ;' DISAB
FROM   v$session s,

              v$process p
WHERE p.addr = s.paddr   and s.username = 'SYS'    and s.Program not like '%SQL%'
and SID<>274

order by sid 

4)Identify the directory where the trace file is generated 

SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';

5) Identify the name of the trace file which is generated 

  

SELECT s.username, s.SID, s.serial#, s.PROGRAM, p.spid 

FROM   v$session s,  

              v$process p 

WHERE p.addr = s.paddr and s.username = <User_Name>; 

  

The name of the trace file is  <oracle_sid>_ora_<p.spid>.trc 

6) Disable the tracing for this session (  must be logged as SYSDBA ) 

EXECUTE dbms_system.set_sql_trace_in_session (<sid>, <serial#>, false); 

Continue Reading

Update Strategy - Session Settings in Informatica

Lets see the different settings that we can configure for update strategy at session level.

Single Operation of All Rows:

We can specify a single operation for all the rows using the "Treat Sources Rows As" setting in the session properties tab. The different values you can specify for this option are:

• Insert: The integration service treats all the rows for insert operation. If inserting a new row violates the primary key or foreign key constraint in the database, then the integration service rejects the row.
• Delete: The integration service treats all the rows for delete operation and deletes the corresponding row in the target table. You must define a primary key constraint in the target definition.
• Update: The integration service treats all the rows for update operation and updates the rows in the target table that matches the primary key value. You must define a primary key in the target definition.
• Data Driven: An update strategy transformation must be used in the mapping. The integration service either inserts or updates or deletes a row in the target table based on the logic coded in the update strategy transformation. If you do not specify the data driven option when you are using a update strategy in the mapping, then the workflow manager displays a warning. The integration service does not follow the instructions in the update strategy transformation.

Update Strategy Operations for each Target Table:

You can also specify the update strategy options for each target table individually. Specify the update strategy options for each target in the Transformations view on the Mapping tab of the session:

• Insert: Check this option to insert a row in the target table.
• Delete: Check this option to delete a row in the target table.
• Truncate Table: check this option to truncate the target table before loading the data.
• Update as Update: Update the row in the target table.
• Update as Insert: Insert the row which is flagged as update.
• Update else Insert: If the row exists in the target table, then update the row. Otherwise, insert the row.

The below table illustrates how the data in target table is inserted or updated or deleted for various combinations of "Row Flagging" and "Settings of Individual Target Table".

Row Flagging Type	Target Table Settings	Result
Insert	Insert is specified	Source row is inserted into the target.
Insert	Insert option is not specified	Source row is not inserted into the target
Delete	Delete option is specified	If the row exists in target, then it will be deleted.
Delete	Delete option is not specified	Even if the row exists in target, then it will not be deleted from the target.
Update	Update as Update	If the row exists in target, then it will be updated.
Update	Insert is specified Update as Insert is specified	Even if the row is flagged as udpate, it will not be updated in Target. Instead, the row will be inserted into the target.
Update	Insert is not specified Update as Insert is Specified.	Neither update nor insertion of row happens
Update	Insert is specified Update else Insert is specified	If the row exists in target, then it will be updated. Otherwise it will be inserted.
Update	Insert is not specified Update else Insert is Specified	If the row exists in target, then it will be updated. Row will not be inserted in case if it not exists in target.

Continue Reading