Identify Current Active Oracle Events in DB

I needed to find out if a certain event was set at the database level on my instance. The READ_EV procedure of DBMS_SYSTEM came to my help. Below is a quick way to find out what are the currently set events for your session (session level). It will report all instance wide events too as they are applicable to session also.

In the below example, event_level will be zero if event is not not set at all.

set serveroutput on

  event_level NUMBER;
  FOR i IN 10000..10999
    IF (event_level > 0) THEN
      dbms_output.put_line('Event '||TO_CHAR(i)||
                           ' set at level '|| TO_CHAR(event_level));
    END IF;

Monitoring the LongOperations in Oracle DB using V$SESSION_LONGOPS

Most of the long running SQL’s can be monitored by using the oracle view v$session_longops. This view is not to be queried in standalone. Rather, you should identify the session that you want to monitor using the v$session and the use session_id as a filter. I also add a condition time_remaining > 0 so that it lists only the active ones.

SQL to Monitor v$session_longops

       ROUND(TIME_REMAINING /60,2)"time remaining(min)",
       TOTALWORK-SOFAR "Work Remaining" ,

There are a number of criteria which makes a SQL to be listed in view V$SESSION_LONGOPS, and I am not sure of all of them. In case of Full Table Scans, the scan has to run for at least 6 seconds and the table scanned should occupy at least 10000 db_blocks. The view also lists long operations in hash_joins, sorts, sort outputs, gather table stats and gather tables index stats. The 6 second criteria appears to be applicable across all of it while the 10K block occupancy is limited to the Table Scan alone.

Note that the time_remaining column in the view should never be considered to predict the “actual” time remaining. In most cases, especially involving hash_joins the time increases rather exponentially than linear. It depends on the hash_area_size (AUTO by default in 11g) and the size of the blocks to be processed. And if it starts using TEMP to do the sorts and you know that there is a large amount of data, you should probably start tweaking the SQL 🙂


When executing DML (DELETE & UPDATE), in addition to the rows in the table, oracle has to update the indexes on the table. The time spent in doing the index modification is not reflected properly in the v$session_longops view as it looks at table blocks only. The estimate will also be based on table blocks and hence will be lower than the actual value required.

Custom Applications:

The DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS can be used to add this facility to custom applications. Once this is set, oracle will display the information about that session in the view. For it to work effectively, you must know the total amount of work you are planning to do and how much work you have done so far.


   rindex      IN OUT BINARY_INTEGER,
   slno        IN OUT BINARY_INTEGER,
   op_name     IN     VARCHAR2       DEFAULT NULL,
   target      IN     BINARY_INTEGER DEFAULT 0,
   context     IN     BINARY_INTEGER DEFAULT 0,
   sofar       IN     NUMBER         DEFAULT 0,
   totalwork   IN     NUMBER         DEFAULT 0,
   target_desc IN     VARCHAR2       DEFAULT 'unknown target',
   units       IN     VARCHAR2       DEFAULT NULL)  ;

set_session_longops_nohint constant BINARY_INTEGER := -1;

Syntax For the above referenced from Oracle Documentation

Why is there a 2 GB memory limit for Java.exe on Windows 32-bit Servers

What is Virtual Address Space?

Data readily accessible to the processor are identified by a memory address, which starts at zero and ranges to a max value depending on the processor. 32-bit processors use memory addresses that are 32 bits wide. The 32-bit wide address allows the processor to address 2^32 bytes of memory, which is exactly 4GB. And this is the 4GB memory limit.


Why is there a 2 GB address space limit?

The address space might contain things other than memory. When a program loads under Windows, it is managed as a process. One of the most important features of a process is its virtual address space, which holds all of the code and data the process will directly access. In the 32-bit versions of Windows, processes are given a default memory limit of two gigabytes. This limit means has the logical addresses that any process touches varying between 0x00000000 and 0x7FFFFFFF (this is the full range of non-negative numbers that can fit in 32 bit). To defend the system against poorly written applications and device drivers, Windows allocates memory for applications from 0x00000000 to 0x7FFFFFFF. It maps the operating system itself in the range 0x80000000 to 0xFFFFFFF; as much as it can, at any rate. This gives user processes two gigabytes of virtual address space, and gives the system two gigabytes of virtual address space.

Is there an option to work-around the 2 GB limit?
On a system with more than two gigabytes of memory, the system can be booted with the /3GB option to expose more than two gigabytes of address space to each process that’s marked with the large-address aware bit in its executable image. User processes receive three gigabytes of space, then, while the system can use a single gigabyte of address space. However, the benefit of using this extra space can be mitigated by the fact that the system has to compress itself into the remaining one gigabyte of address space.



Oracle EBS Platform Migration – A Quick note



I read around for strategies on platform migration of EBS R12 and 11g DB. There are paths defined by Oracle which guides such migrations. Given below is a high level description of an oracle endorsed approach of App-Tier migration process.  DB migration is a separate topic and should be dealt as such.


  1. Most of the application tier migration can be done in advance and a very minimal downtime is required. However DB tier migration will require downtime (you have to move the 2 TB data from one place to other!)
  2. The below steps are explained in detailed in the note (438086.1) which was my reference.


  • Pre-Requisites
  1.     OS system requirements in the new platform target machine.
  2.     Software requirements on source and target machine (zip, JDK, perl etc)
  3.     Apply the latest AD Patch (R12.AD.B.DELTA.3 for 12.1)
  4.     Upgrade to EBS 12.0.4 or higher
  5.     Apply the latest Autoconfig template patches
  6.     Apply the latest Rapidclone Patches
  7.     Apply Platform Migration Patches & Additional Patches (No Patched advised for 12.1.x at this point of time)
  8.     Run Autoconfig on Source system
  9.     Run adpreclone in source system
  10.     Run maintain snapshot (And this must complete successfully)
  11.     Identify Tech Stack Updates to be done to the Target machine after the migration. This is done by using technology Stack Inventory utility
  •     Migration tasks
  1.     Generate and upload the manifest of customer-specific files. This is used to generate a custom migration patch. The custom migration patch created is specific to specific upgrade only and contains all the files contained in the APPL_TOP that are binary specific to the new target platform. This patch is therefore applied in the new environment.
  2.     Copy the Source System to the Target System
  3.     Install JDK and InfoZip utilities on the Target System
  4.     Copy the Source System Context File to the Target System
  5.     Clone the Applications Context File on the Target System
  6.     Install the Application Tier Technology Stack
  7.     Run AutoConfig setup phase on the Target System
  8.     Download and apply the customer-specific update with AutoPatch
  9.     Review Component Versions and Technology Stack patch level
  10.     Regenerate File System Objects
  11.     Clean Nodes
  12.     Run AutoConfig to complete the Target System configuration
  • Finishing tasks
  1.     Update Third Party Extensions.
  2. Review and update Target System Application Tier Settings and Customizations
  3. Update printer settings
  4. Update Oracle Workflow configuration settings
  5. Verify the APPLCSF variable setting
  7. Start all services on the Target System



Application Tier Platform Migration with Oracle E-Business Suite Release 12 ID : 438086.1

ORA-02049 Timeout: Distributed Transaction Waiting for Lock

Key Facts:

  • This issue occurs when a session that is involved in a distributed transaction waits for another session’s TX enqueue (row lock) for longer than the distributed_lock_timeout value.
  • Any operation that uses a database link, even a query starts a distributed transaction.

In the below POC, if there was a COMMIT after the [select 1 from dual@database_link], the situation would become a normal lock situation – the second session will proceed once there is a COMMIT in the 1st  session. In a situation which were in a session was waiting on a row lock and involved in distributed transaction, it would timeout after the distributed_lock_timeout period. If the transaction was a normal one, sooner or later a DBA will notice the lock and could kill the holding session.


There are two ways to deal with this problem:

  • Increase the distributed_lock_timeout value hoping that the session holding the lock releases it before the timeout.
  • Code the application to capture this ORA error and then issue a TRY AGAIN directive.
  • Another workaround would be to add more COMMIT’s as they tend to reset the distributed transaction.

Identifying the Root Cause:

To identify the session holding the resources, what oracle suggests is to dump a system state trace BEFORE the ORA-02049 occurs.

However, I have devised another approach based on oracle’s original suggestion. The basic idea will be to monitor v$session for sessions waiting on the event ‘enq: TX – row lock contention’. If the seconds_in_wait value for such sessions approaches the distributed_lock_timeout value and the session is involved in a distributed transaction we can find the session holding the lock by joining v$lock and v$session.


Session 1

SQL> update dummy_table set table_name ='UPDATED_DUMMY';       -->A LOCAL UPDATE STATEMENT

24 rows updated.



Session 2

SQL> select 1 from dual@database_link; ------>THIS STARTS THE DISTRIBUTED TRANSACTION




SQL> update dummy_table set table_name ='UPDATED_DUMMY';  ---->THIS IS A LOCAL UPDATE, BUT ERRORS OUT WITH ORA-02049

update dummy_table set table_name ='UPDATED_DUMMY'


ERROR at line 1:

ORA-02049: timeout: distributed transaction waiting for lock