Monthly Archives: November 2013

Virtual Indexes

The process of tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Creating additional indexes is not without its problems. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn’t affect the normal running of your system. This article presents a simple example of how virtual indexes are used.

First, we create and populate a table.

CREATE TABLE objects_tab AS SELECT * FROM all_objects;

ALTER TABLE objects_tab ADD (
  CONSTRAINT objects_tab_pk PRIMARY KEY (object_id)

EXEC DBMS_STATS.gather_table_stats(USER, 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan
Plan hash value: 2097082964

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT            |                |     1 |    92 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB    |     1 |    92 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | OBJECTS_TAB_PK |     1 |       |     1   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   2 - access("OBJECT_ID"=10)


If we query the table using a non-indexed column, we see a full table scan.

SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan
Plan hash value: 821620785

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |             |     2 |   184 |   207   (5)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |

Predicate Information (identified by operation id):

   1 - filter("OBJECT_NAME"='USER_TABLES')


To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created


If we repeat the previous query we can see the virtual index is not visible to the optimizer.

SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan
Plan hash value: 821620785

| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT  |             |     2 |   184 |   207   (5)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| OBJECTS_TAB |     2 |   184 |   207   (5)| 00:00:03 |

Predicate Information (identified by operation id):

   1 - filter("OBJECT_NAME"='USER_TABLES')


To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered


If we repeat the query we can see that the virtual index is now used.

SQL> SELECT * FROM objects_tab WHERE object_name = 'USER_TABLES';

Execution Plan
Plan hash value: 4006507992

| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT            |                            |     2 |   184 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB                |     2 |   184 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | OBJECTS_TAB_OBJECT_NAME_VI |     2 |       |     1   (0)| 00:00:01 |

Predicate Information (identified by operation id):

   2 - access("OBJECT_NAME"='USER_TABLES')


The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SELECT index_name FROM user_indexes;


1 row selected.

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX';


2 rows selected.


Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.

SQL> EXEC DBMS_STATS.gather_index_stats(USER, 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.


Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;
CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
ERROR at line 1:
ORA-01408: such column list already indexed

SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.


Application Design is the only Reason for Deadlocks?

Have you ever seen a message “ORA-00060: Deadlock detected” and automatically assumed that it was an application coding issue? Well, it may not be. There are DBA-related issues and you may be surprised to find out that INSERTs may cause deadlock. Learn all the conditions that precipitate this error, how to read the “deadlock graph” to determine the cause, and most important: how to avoid it.


I often get a lot of questions in some form or the other like the following:

  • What’s a Deadlock
  • How can I prevent it
  • Why would an INSERT cause deadlock
  • Why would I need to index FK columns
  • Is ON DELETE CASCADE FK constraint a good idea?

Deadlock is one of those little understood and often misinterpreted concepts in the Oracle Database. The word rhymes with locking, so most people assume that it is some form of row locking. Broadly speaking, it’s accurate; but not entirely. There could be causes other than row level locking. This is also often confused by people new to Oracle technology since the term deadlock may have a different meaning in other databases. To add to the confusion, Oracle’s standard response to the problem is that it’s an application design issue and therefore should be solved through application redesign. Well, in a majority of cases application design is a problem; but not in all cases. In this post, I will describe:

  1. Why Deadlocks Occur
  2. Primer on Oracle Latching, Locking
  3. How to Interpret Deadlock Traces
  4. Various Cases of Deadlocks
  5. Some Unusual Cases from My Experience

Deadlocks Explained

With two Oracle sessions each locking the resource requested by the other, there will never be a resolution because both will be hanging denying them the opportunity to commit ot rollback and therefore releasing the lock. Oracle automatically detects this deadly embrace and breaks it by forcing one statement to roll back abruptly (and releasing the lock) and letting the other transaction to continue.

Here is how a deadlock occurs. Two sessions are involved, doing updates on different rows, as shown below:

Step Session 1           Session 2
---- ------------------- -----------------
1.   Update Row1
     (Does not Commit)
2.                       Update Row2
                        (Does not Commit)
3.   Update Row2
4.   Waits on TX Enqueue
5.                       Update Row1

At the step 5 above since Row1 is locked by session1, session2 will wait; but this wait will be forever, since session1 is also waiting and can’t perform a commit or rollback until that wait is over. But session 1’s wait will continue to exist until session 2 commits or rollback – a Catch 22 situation. This situation is a cause of deadlock and Oracle triggers the ststement at Step 3 to be rolled back (since it detected that deadlock). Note that only the statement that detected the deadlock is rolled; the previous statements stay. For instance, update row1 in Step 1 stays.

This is the most common cause of deadlocks and is purely driven by application design and can only be solved by reducing the possibility of occurence of that scenario. Now that you understand how a deadlock occurs, we will explore some other causes of deadlocks. But before that, we will explore different types of locks in Oracle.

Types of Locks

Database locks are queue-based, i.e. the session first waiting for the lock will get it first, before another session which started waiting for the same resource after the first session. The requesters are placed in a queue, hence locks are also called Enqueues. There are several types of enqueues; but we will focus on row locking, and specifically only two type of them:

  • TM – this is related to database structural changes. Suppose someone is executing some query against a table, such as SELECTing from it. The table structure should remain the same in that period. TM locks protect the table structure so that someone does not add a column during that query. TM locks allow multiple queries and DMLs, but not DDL against the table.
  • TX – this is the row level locking. When a row is locked by a session, this type of lock is acquired.

Anatomy of a Deadlock Trace

When a deadlock occurs and one of the statements gets rolled back, Oracle records the incident in the alert log. Here is an example entry:

 ORA-00060: Deadlock detected. More info in file

Along with the alert log entry, the incident creates a tracefile (as shown above). The trace file shows valuable information on the deadlock and should be your first stop in diagnosis. Let’s see the various sections of the tracefile:

Deadlock Graph

The first section is important; it shows the deadlock graph. Here are the various pieces of information on the graph. Deadlock graph tells you which sessions are involved, what types of locks are being sought after, etc. Let’s examine the deadlock graph, shown in the figure below:

Row Information

The next critical section shows the information on the rows locked during the activities of the two sessions. From the tracefile you can see the object ID. Using that, you can get the object owner and the name from the DBA_OBJECTS view. The information in on rowID is also available here. You can get primary key information from the object using that rowID.

Process Information

The tracefile also shows the Oracle process information which displays the calling user. That information is critical since the schema owner may not be the one that issued the statement.

With the information collected from various sections of the deadlock graph, you now know the following:

  • The session that caused it
  • The session that was the victim
  • The Oracle SID and process ID of the sessions
  • The object (the table, materialized view, etc.) whose row was in the deadlock
  • The exact row that was so popular to cause the deadlock.
  • The SQL statement that caused the deadlock.
  • The machine the session came from with the module, program (e.g. SQL*Plus) and userid information

Now it is a cinch to know the cause of that deadlock and which specific part of the application you need to address to fix it.

Other Causes

The case described above is just one type of locking scenario causing deadlocks; but this is not the only one. Other types of locks also cause deadlocks. These scenarios are usually difficult to identify and diagnose and are often misinterpreted. Well, not for you.You will learn how to diagnose these other causes in this post. These causes include:

  1. ITL Waits
  2. Bitmap Index Update
  3. Direct Path Load
  4. Overlapping PK Values

Deadlocks due to ITL Shortage

You can read how ITL works in another of my blogposts – How Oracle Locking Works. In summary, when a session locks a row, it does not go to a central lock repository and get a lock from there. Instead, the session puts the information on the lock in the header of the block, called Interested Transaction List (ITL). Each ITLslot takes up 24 bytes. Figure 1 below shows an empty block with just one ITL slot. When rows are inserted, from bottom of the block upwards, the free space gradually drops.

When a session – session1 – wants to lock the row1, it uses the slot#1 of the ITL, as shown in Figure 3 below. Later, another session – session2 – updates row2. Since there is no more ITL slot, Oracle creates a new slot – slot#2 – for this transaction. However, at this stage, the block is almost packed. If a third transaction comes in, there will be no more room for a third ITL slot to be created; causing the session to wait on ITL. Remember, this new session wants to lock row3, which is not locked by anyone and could have been locked by the session; but it’s artificially prevented from being locked due to the absence of an ITL slot.

Checking for ITL Shortage

You can check for ITL shortage by issuing this query:

select owner, object_name, value
from v$segment_statistics
where statistic_name = 'ITL waits'
and value > 0

Here is a sample output:

OWNER       OBJECT_NAME                    VALUE
----------- ------------------------- ----------
SYSMAN      MGMT_METRICS_1HOUR_PK             19
ARUP        DLT2                              23
ARUP        DLT1                             131

If you check the EVENT column of V$SESSION to see which sessions are experiencing it right now, you will see that the sessions are waiting with the event: enq: TX - allocate ITL entry.

Deadlock Scenario

Here is the scenario where two sessions cause a deadlock due to ITL shortage. Imagine two rows – row1 and row2 – are in the same block. The block is so tightly packed that only two ITL slots can be created.

Step Session1 Session2
1 Update Table1 Row1
(1 ITL slot is used; no more free ITL slots and no room in the block to create one)
2 Update Table2 Row1
(One ITL slot is gone. There are no more free ITL slots and no room to create one)
3 Update Table2 Row2
(Lack of ITL slots; so this will hang)
4 Update Table1 Row2
(Lack of ITL slots will make this hang as well. Deadlock!)

At Step 4 Session 2’s hang can’t be resolved until session 1 releases the lock, which is not possible since it itself is hanging. This never ending situation is handled by Oracle by detecting it as a deadlock and killing one of the sessions.

Deadlock Graph

To identify this scenario as the cause of deadlock, look at the deadlock graph. This is how a deadlock graph looks like when caused by ITL waits.

The absence of row information on one of the sessions is a dead giveaway that this is a block level issue; not related to specific rows. Here are the clues in this deadlock graph:

  • The lock type is TX (row lock) for both the sessions
  • The holders held the lock in “X” (exclusive) mode (this is expected for TX locks)
  • However, only one of the waiters is waiting in the “X” mode. The other is waiting with the “S” (shared) mode, indicating that it’s not really a row lock the session is waiting for.
  • One session has the row information; the other doesn’t.

These clues give you the confirmation that this is an ITL related deadlock; not because of the application design. Further down the tracefile we see:

As you can see, it’s not 100% clear from the tracefile that the deadlock was caused by ITL. However by examining the tracefile we see that the locks are of TX type and the wait is in the “S” (shared) mode. This usually indicates ITL wait deadlock. You can confirm that is the case by checking the ITL shortages on that segment from the view V$SEGMENT_STATISTICS as shown earlier.

Occasionally you may see two rows here as well, as a result of a previous wait (e.g. buffer busy wait) on the block which has not been cleaned out yet. In such a case you will see information on two rows; but there are some other clues that may point to this cause. The row portion of the rowid will be 0, meaning it was not a row but the block. The other clue might be that the row information points to a row that has nothing to do with the SQL statement. For instance, you may find the row information pointing to a row in table Table1 whereas the SQL statement is “update Table2 set col2 = ‘X’ where col1 = 2”.

The solution is very simple. Just increase the INITRANS value of the table. INITRANS determines the initial number of ITL slots. Please note, this value will affect only the new blocks; the old ones will still be left with the old values. To affect the old ones you can issueALTER TABLE TableName MOVE to move the tables to nw blocks and hence new structure.

Deadlock due to Foreign Key

This is a really tricky one; but not impossible to identify. When a key value in parent table is updatd or a row is deleted, Oracle attempts to takes TM lock on the entire child table. If an index is present on the foreign key column, then Oracle locates the corresponding child rows and locks only those rows. The documentation in some versions may not very clear on this. There is a documentation bug (MOS Bug# 2546492). In the absense of the index, a whole table TM lock may cause a deadlock. Let’s see the scenario when it happens.


Here is the scenario when this deadlock occurs.

1 Delete Chaild Row1
2 Delete Child Row2
3 Delete Parent Row1
(Waits on TM Enqueue)
4 Delete Parent Row2
(Waits on TM Enqueue)

Deadlock Graph

This is how the deadlock graph looks like when caused by unindexed foreign key. As you can see, the deadlock graph does not clearly say that the issue was to do with Foreign Key columns not being indexed.Instead, the clues here are:

  • TM locks for both the sessions, instead of TX. Remember: TM are metadata related, as opposed to TX, which is a row related lock.
  • The lock type of holders is Share Exclusive (SX) as opposed to Exclusive (X)
  • Sessions do not show any row information

These three clues together show that this deadlock is due to FK contention rather than the conventional row locks.

So, what do you do? Simple – create the indexes on those FKs and you will not see this again. As a general rule you should have indexes on FKs anyway; but there are exceptions, e.g. a table whose parent key is never updated or deleted infrequently (think a table with country codes, state codes or something pervasive like that). If you see a lot of deadlocks in those cases, perhaps you should create indexes on those tables anyway.

Deadlock due to Direct Load

Direct Load is the fastest way to load data into a table from another source such as a table or a text file. It can be effected in two ways – the APPEND hint in INSERT statement (insert /*+ append */ ) or by using DIRECT=Y option in SQL*Loader. When a table is loaded with Direct Path, the entire table is locked from further DMLs, until committed. This lock may cause deadlocks, when two sessions try to load into the same table, as shown by the scenario below.


Step Session1 Session2
1 Direct Path Load into Table1
2 Direct Path Load into Table2
3 Direct Path Load into Table2
(Hangs with TM Enqueue; since Session2 has the lock)
4 Direct Load into Table1
TM lock on Table1 prevents this operation

Deadlock Graph

As usual, the deadlock graph confirms this condition. Here is how the deadlock graph looks like:
 Both sessions do not show any row information; and subsequent parts of the tracefile do not show any other relevant information. The key to identify this deadlock as caused by Direct Path is to look for the type of lock mode – X. This type of lock mode exists for row level locking as well. However the deadlock graph shows row information in that case. So, the clues for this type of deadlock are:

  • Lock type is TM (as shown in the Resource Name)
  • Lock mode for both the holders and waiters is X (indicating a row lock)
  • No row information (since it is not really row-related)

Deadlock due to Bitmap Index Contention

Bitmap Index is a special type of index that stores bitmaps of actual values and compare bitmaps to bitmaps, e.g. instead of comparing literals such as “A” = “A”, Oracle converts the value to a bitmap and compares against the stored bitmap values. For instance “A” might be represented as “01011”; so the comparison will be “01011” = “01011”. Index searches are way faster compared to literal comparison.

However, there is a price to pay for this performance. Unlike a regular b*tree index, when a row is updated, the index piece of the bitmap index is locked until the transaction is committed. Therefore udates to any of the rows covered by that index piece hangs. When two sessions update two different rows covered by the same index piece, they wait for each other. Here is the scenario when this condition arises.


1 Update Row 1
(Bitmap index piece is locked)
2 Update Row2
(Hangs for TX Row Lock)
3 Update Row2
(Hangs as bitmap index piece is locked by session2 and can’t release until it commits)

Deadlock Graph

You can confirm this occurrence from readling the deadlock graph.

The clues that show this type of deadlock:

  • The lock type is TX (as shown in the Resource Name)
  • The lock wait mode is “S” (shared) but the type of lock is TX rather than TM.
  • The waiter waits with mode “S” instead of “X”
  • The row information is available but the object ID is not the ID of the table; but the bitmap index.

The solution to this deadlock is really simple – just alter the application logic in such a way that the two updates will not happen in sequence without commits in between. If that’s not possible, then you have to re-evaluate the need for a bitmap index. Bitmap indexes are usually for datawarehouse only; not for OLTP.

Deadlock due to Primary Key Overlap

This is a very special case of deadlock, which occurs during inserts; not updates or deletes. This is probably the only case where inserts cause deadlocks. When you insert a record into a table but not commit it, the record goes in but a further insert with the same primary key value waits. This lock is required for Oracle because the first insert may be rolled back, allowing the second one to pass through. If the first insert is committed, then the second insert fails with a PK violation. But in the meantime-before the commit or rollback is issued-the transaction causes the second insert to wait and that causes deadlock. Let’s examine the scenario:


1 Insert PK Col value = 1
(Doesn’t commit)
2 Insert PK Col value = 2
(Doesn’t commit)
3 Insert PK Col = 2
(Hangs, until Session2 commits)
4 Insert PK Col = 1
(Hangs and Deadlock)

Deadlock Graph

The deadlock graph looks like the following.

The key clues are:

  • The lock type is TX (row lock)
  • The holders are holding the lock in “X” (exclusive) mode
  • The waiters are waiting for locks in “S” mode, even when the locks type TX.
  • The subsequent parts of the tracefile don’t show any row information.

However, the latter parts of the tracefile shows the SQL statement, which should be able to point to the cause of the deadlock as the primary key deadlock. Remember, this may be difficult to diagnose first since there is no row information. But this is probably normal since the row is not formed yet (it’s INSERT, remember?).

Special Cases

I have encountered some very interesting cases of deadlocks which may be rather difficult to diagnose. Here are some of these special cases.

Autonomous Transactions

Autonomous transactions are ones that are kicked off form inside another transaction. The autonomous one follows its own commit, i.e. it can commit independently of the outer transaction. The autonomous transaction may lock some records the parent transaction might be interested in and vice versa – a perfect condition for deadlocks. Since the autonomous transactions is triggered by its parent, the deadlocks are usually difficult to catch.

Here is how the deadlock graph looks like (exceprted from the tracefile)

---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0005002d-00001a40 17 14 X 17 14 X
session 14: DID 0001-0011-00000077
session 14: DID 0001-0011-00000077
Rows waited on:
Session 14: obj - rowid = 000078D5 - AAAHjVAAHAAAACOAAA
(dictionary objn - 30933, file - 7, block - 142, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.

Here are the interesting things about this deadlock graph, which are clues to identifying this type of deadlock:

  • The lock type is TX (row lock) and the mode is “X”, which is exclusive. This indicates a simple row lock.
  • Remember, deadlocks are always as a result of two transactions; not one. However, the deadlock graph shows only one session. The other session information is not even there.The presence of only one session indicates that the other transaction originated from the same session – hence only one session was recorded. The only way two transactions could have originated from the same session is when the transaction is an autonomous one.
  • The row information is not there because the autonomous transaction acts independently of the parent.

If you see a deadlock graph like this, you can be pretty much assured that autonomous transactions are to blame.

The above cause is not limited to TX locks; it could happen in TM locks as well. The diagnosis remains the same.

Deadlocks among the PQ slaves

Consider a procedural logic like this:


This code locks the rows selected by the parallel query slaves. Since the select is done in parallel, the PQ slaves distribute the rows to be selected. Therefore the locking is also distributed among the PQ slaves. Since no two rows are updated by the same PQ slave (and hence the same session), there is no cause for deadlocks.

However, assume the code is kicked off more than once concurrently. This kicks off several PQ slaves and many query coordinators. In this case there is no guarantee that two slaves (from different coordinators) will not pick up the same row. In that case, you may run into deadlocks.

Triggers firing Autonomous Transactions

If you have triggers firing Autonomous Transactions, they may cause deadlocks, in the same line described in the section on autonomous transactions.


In case of tablespaces defined with manual segment space management, if too many process freelists are defined, it’s possible to run out of transaction freelists, causing deadlocks.

In Conclusion

The most common cause of deadlocks is the normal row level locking, which is relatively easy to find. But that’s not the only reason. ITL Shortage, Bitmap Index Locking, Lack of FK Index, Direct Path Load, PK Overlap are also some of the potential causes. You must check the tracefile and interpret the deadlock graph to come to a definite conclusion on the cause of the deadlock. Some of the causes, e.g. ITL shortage, are to do with the schema design; not application design and are quite easy to solve. Some causes, as in the case of the PK overlap case, INSERTs cause deadlocks.

I hope you found it useful in diagnosing the deadlock conditions in your system. As always, your feedback is very much appreciated.

Compound Triggers(Oracle DB 11g New Feature )

In previous version of database you can control the execution sequence of triggers using FOLLOWS key word when creating triggers.


Oracle database 11g support new feature called compound triggers which can do the same purpose of FOLLOWS but in different manner.

Trigger Timing 

Before dig into compound triggers let’s write about trigger timing first.
The trigger timing is the time when trigger is executed in table i.e (BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, and AFTER EACH ROW)

Compound trigger can do all previous ti mining in only one compound trigger.

Guidelines for Compound Triggers 
a- Compound triggers combine all triggers timing in one trigger body.

b- Compound triggers is only for DML operations and it doesn’t support DDL and system operation.

c- You can use :OLD and :NEW variable identifiers only in ROW level blocks( BEFORE EACH ROW, AFTER EACH ROW )


e- You use WHEN clause to improve the performance of triggers. but no support of WHEN clause in compound triggers.

f- There is one declaration section for all trigger timing which can share variables until finish transaction.

g- Duplicate of trigger timing are permitted in compound trigger.

h- INSERTING, UPDATING and DELETING predicates still available in compound trigger

i- Resolve mutating table error (ORA-04091)

Compound Trigger Syntax





Note that BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW and AFTER EACH ROW are optional selection, so you can use what you want regarding your requirements.

INSTEAD OF ROW is used with database views only.

If you describe the structure of USER_TRIGGERS data dictionary view you will find new columns(BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW) related to compound trigger

Compound triggers provide a lot of benefits
1- One location to implement the transaction
2- Whole triggers can share the declaration section
3- Unit of maintenance
4- Resolve mutating table error (ORA-04091)


The Siebel Workflow Instance Monitor is awesome: it allows run time debugging of Workflow processes it both Production and non-Production environments. In the absence of a script like debugger, it’s the next best thing.

A word of warning, however – remember to turn it OFF!

The Workflow Instance monitor stores a lot of information in the database: for every time a monitored process is run, each step will generate a child record with grandchild records for every process property and it’s value. If you monitor a particularly active Workflow process, data volumes can build up very quickly indeed.

There are a number of ways to address this:

  1. Turn monitoring OFF the moment you’re finished with it. Leaving monitoring running is so easy to do and can signinifcant impact very quickly. You can see what Workflows are currently monitored from the Workflow Deployment screen. Just click the ‘Active Workflows’ view tab and query, in the bottom applet, for ‘Deployment Status = Active’ and ‘Monitoring Level <> ’0 – None’. You can set these back to ’0 – None’ from here too, effecitvely turning off montoring.
  2. Use the ‘Purge’ button on the ‘Process Instances’ view. This will invoke a number of DELETE statements against the corresponding tables, allowing you to clear out logs from before a specified date.
  3. As per My Oracle Support articule ID 478169.1, you can use ‘database vendor tools’ to remove the data. Oracle don’t want to mention it, as it goes against their ‘no database statements directly against the database’ rule, but they really mean TRUNCATE. As such, it is an ‘approved’, but not supported, solution to use TRUNCATE as follows:


We actually run a script to do this on a regular basis across our environments, in order to keep things nice and tidy.

How to Perform Unattended Server to Server File Transfers: Unix to Unix

Use SSH (Secure Shell) keys and SCP (Secure Copy) to perform secure, unattended, server to server file transfers between Unix servers.


Setting Up ID Accounts

ID Types and Audits

The ID types used in secure, unattended file transfers are complex in order to create an audit trail. Holding IDs and File Transfer IDs cannot be traced back to a single user so each type of ID is given limited functions.

  • (Regular) User ID: Is traceable to a single user. Can log in, run processes, send files. Do not use for unattended file transfers.
  • Holding ID: Is not traceable to a single user. Can run processes. Cannot log in or send files.
  • File Transfer ID: Is not traceable to a single user. Can send files. Cannot log in or run processes.

Setting Up SSH Keys for File Transfer ID

Secure Shell (SSH) is a program used to provide encryption during file transfers using SCP or SFTP (Secure File Transfer Protocol). SSH can use public/private key pairs (with or without a password) to authenticate file transfers.

How does it work? First, you set up a public/private key pair. See the following procedure for steps. Then, when you are ready to transfer files, the File Transfer ID on the receiving server sends the public key to the source server. Using the public key, the source server sends an encrypted challenge back to the receiving server. The receiving server uses the private key to decrypt the challenge and respond to it.

Note: If one or more of the servers does not use OpenSSH, this won’t work. For more information, see the Open SSH site.

Note: This procedure works only when using the default key file name .ssh/id_dsa. If necessary, change the file name.

  1. Run the procedure using the Holding ID for which the keys are being generated. On the source server, as the Holding ID, generate the key pair. Enter:
    • For Solaris: /opt/common/bin/ssh-keygen -t dsa
    • For Linux: /usr/bin/ssh-keygen -t dsa

    This generates a key pair in ~/.ssh. You will be prompted for a password. Click Enter to indicate you don’t want to use a password. (Don’t set up a password because in an unattended file transfer, no one will be available to enter the password. However, because the private key is not password-protected, be sure to take extra care to protect it. For example, don’t copy the private key to other directories.)

  2. Using the password for the File Transfer ID, copy the public key to the File Transfer ID account on the receiving server. Enter:cd ~/.ssh
    scp filetransferid@receiving server:.ssh/authorized_keys
  3. Note: This overwrites any keys already in the authorized keys file. If you need to transfer files from more than one Holding ID to this File Transfer ID, use your own UserID to transfer the keys and concatenate them together.

    You can now scp from the source server to the receiving server without answering a password request.

Transferring Files

  1. Use the Holding ID’s cron job process to transfer the file from the source server to the receiving server.
  2. You can use this sample script:

    30 1 * * * /opt/common/bin/scp source_file filetransferid@receivingserver:/path/receiving_file > /path/transfer_error_log 2>&1

    This cron entry in the Holding ID account will attempt to copy the source_file every night at 1:30 AM and record any errors that occur in the file /path/transfer_error_log. (This error file is on the source server.) You can add to the sample script if you need to record the transfer in a log file, rename the file with the date once the transfer is done, archive and remove old files, etc.