Common misconception about how Oracle handles deadlocks

Introduction

In today's blog post, I'd like to uncover a common misconception about how Oracle handles deadlocks, but first let's talk about what deadlock is. Deadlock is a locking scenario that occurs when two or more sessions are blocked as they wait because of a lock that is being held by the other session. Oracle Database automatically detects this scenario and handles this because otherwise, they will wait forever as both of them are blocked and waiting to each other’s locked resources. But the question is how does Oracle handle this? some would say that Oracle handles these scenarios by terminating one of the sessions which is known to be as the "deadlock victim" - well, that's wrong. Others would say that by rolling-back the entire transaction of one of the sessions - well, that's also wrong. So how Oracle really handles deadlock scenarios?

Deadlock Illustration

Let's see a quick example of how deadlock occurs

Step 1

Session #1 performs an update of a row (employee #151) and acquires a lock on that row

SQL> UPDATE employee
          SET first_name = 'David'
          WHERE employee_id = 151;

1 row updated.

Step 2

Session #2 performs an update of a row (employee #39) and acquires a lock on that row

SQL> UPDATE employee
          SET first_name = 'Greg'
          WHERE employee_id = 39;

1 row updated. 

Step 3

Session #1 performs an update of a row (employee #39) and is now waiting since a lock has been acquired on the same row which has not been released yet as session #2 transaction is still active

UPDATE employee
          SET first_name = 'Mark'
          WHERE employee_id = 39;

Step 4

Session #2 performs an update of a row (employee #151) and is now waiting since a lock has been acquired on the same row which has not been released yet as session #1 transaction is still active.  

SQL> UPDATE employee
          SET first_name = 'John'
          WHERE employee_id = 151;

At this stage both sessions (session #1 and session #2) are blocked  and waiting to each other’s locked resources - that's exactly what deadlock is.

So how Oracle handles deadlock scenarios?

The way Oracle handles deadlock scenarios is not by terminating one of the sessions or performing a transaction-level rollback, it's actually just by performing a statement-level rollback to one of the sessions.  The session that its statement is being rolled back, will encounter an “ORA-00060: Deadlock detected while waiting for resource.” error message (that will also be recorded in the alert log file).

Getting free space ORA errors even though tablespace has enough free space

Introduction

Recently I've seen an interesting question in the OTN discussions forums. 

The question was - Why do I get free space errors (such as ORA-01654) even though tablespace has enough free space. Here is the link to the thread: https://community.oracle.com/message/13869346#13869346

While this may sounds like a weird and complex scenario -  it is actually a pretty common issue and simple to explain.

Background

When Oracle needs to allocate more space for a segment (such as index segment, table segment, etc.) it doesn't allocate a single Oracle Block for the extra space, but rather an extent which is a logical unit of database storage space allocation made up of a number of contiguous data blocks.

When there is no more enough free space in the database blocks, and Oracle needs to allocate, it has to allocate an extent. The size of the extent depends on the tablespace storage configuration which could have either uniform extents sizes where each new extent has the same size, or system-managed extents sizes where Oracle determines the optimal size of additional extents and the extent sizes may vary (Read more about this here: http://docs.oracle.com/cd/B28359_01/server.111/b28318/logical.htm#i19599)

Solving The Mystery

The total free space in a tablespace might be large enough for allocating additional extent; however, in some cases where the tablespace is fragmented, the largest contiguous extent might be smaller than the next extent size. 

In order to obtain the next extent size, it is possible to query the NEXT column in the DBA_SEGMENTS dictionary view, as follows:

SQL> CREATE TABLE large_extent_tbl
(
   id   NUMBER (20)
)
STORAGE (NEXT 10M)
TABLESPACE TEST;  
Table created.

SQL> insert into large_extent_tbl values (1);

1 row created.

SQL> SELECT next_extent/1024/1024
  FROM dba_segments
 WHERE segment_name = 'LARGE_EXTENT_TBL';

NEXT_EXTENT/1024/1024
---------------------
                   10

As you can see I created the table with a NEXT EXTENT definition of 10 which is larger than the largest contiguous extent in the tablespace. In order to obtain the largest contiguous extent in the tablespace we can query DBA_FREE_SPACE as follows:

SQL> SELECT ROUND(SUM (bytes) / 1024 / 1024) "total_free_space (MB)",
       ROUND(MAX (bytes) / 1024 / 1024) "largest_contiguous_extent (MB)"
  FROM dba_free_space
 WHERE tablespace_name = 'TEST' ; 
total_free_space (MB) largest_contiguous_extent (MB)
--------------------- ------------------------------
                  128                              6

SQL>

As you can see, the largest contiguous extent is 6MB which is smaller than the NEXT extent for the segment (which is 10MB).

Additional Resources

• AskTom - How to Find tablespace Fragmentation
• MOS DOC ID 162994.1 - Script to Report Extents and Contiguous Free Space

My article was published in the OTN!

Hi everyone,

I'm honored and grateful that my new article "Verifying I/O Activity Balance Across Disks in ASM" was published in the OTN. 

The article describes Oracle Automatic Storage Management's mirroring and striping capabilities, focusing on how to identify unbalanced I/O operations.

Click here to read the full atricle: https://community.oracle.com/docs/DOC-995178

Oracle Multiprocess and Multithreaded Architecture

Introduction

The Oracle process architecture of Windows operating system is a different process architecture comparing to Linux/Unix operating systems. On Windows, each Oracle instance has one single process that is multithreded, i.e. each background process is a thread within the single "father" process, therefore the "processes" tab in the the task manager will display only one "oracle.exe" process per each instance, as you can see in the following screenshot (taken from one of our Oracle environments):

On Linux/Unix on the other hand, each background process is a different OS process so by looking for all the processes you should expect to get a long list of Oracle processes, as you can see the following screenshot (taken from one of our Oracle on Linux environments):

Oracle 12c Multiprocess and Multithreaded Architecture

By default, the pre-12c Oracle process architecture remains the same in Oracle 12c. However, in Oracle 12c you can enable the multithreaded architecture on Unix/Linux that will cause Oracle processes on Linux/Unix to run as threads - similar to the process architecture on Windows. In order to do so, simply set the THREADED_EXECUTION initialization parameter to TRUE (default value is FALSE). This is a static parameter (SCOPE=SPFILE), therefore requires restarting the instance in order for the changes to take effect.

Demonstration

First, I will connect to one of our 12c Oracle Linux environments and set the THREADED_EXECUTION to be TRUE:

SQL> alter system set THREADED_EXECUTION=true scope=spfile;
System altered.

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
Total System Global Area 1073741824 bytes
Fixed Size                  2932632 bytes
Variable Size             692060264 bytes
Database Buffers          373293056 bytes
Redo Buffers                5455872 bytes
Database mounted.
Database opened.

Now I will check how many Oracle processes I have on the Linux environment:

As you can see, now there are much less processes than before. Most of the background processes now run as threads instead of processes but some of them might still run as operating system processes (like the PMON and the DB Writer processes).

Important things to keep in mind

• OS authentication is not allowed with the multithreaded architecture. If you will try to connect via an OS authentication (for example, by connecting with "/ AS SYSDBA") you will encounter "ORA-01017: invalid username/password; logon denied", therefore you must use a password file for connecting with a SYSDBA/SYSOPER user.
• When the THREADED_EXECUTION parameter is set to TRUE, you must set the DEDICATED_THROUGH_BROKER_LISTENER parameter to ON in your listener.​ora file. When that parameter is set, the listener knows that it should not spawn an OS process when a connect request is received, instead it passes the request to the database so that a database thread is spawned and answers the connection.​ 
• In RAC environments all nodes must have the same value for the THREADED_EXECUTION parameter.

Summary

In this post I've reviewed the process architecture in Linux/Unix vs. Windows and demonstrated the new 12c Multithreaded option. This feature can be useful in order to reduce the number of OS processes in scenarios where you have many Oracle instances on the same machine which may cause a higher overhead as a result of a very high number of OS processes.

Useful Links

• Oracle Documentation Reference - THREADED_EXECUTION 
• Oracle Documentation 12c - Process Architecture

Review of Oracle 12c Unified Auditing

Introduction

Prior to Oracle 12c there were several tables in the Database that stored audit trails:

1. SYS.AUD$ (also accessible through DBA_AUDIT_TRAIL view) which is the main database audit trail 
2. SYS.FGA_LOG$ (also accessible through DBA_FGA_AUDIT_TRAIL view)  for the Fine-Grained auditing records
3. DVSYS.AUDIT_TRAIL$ for the Label security and DB Vault

In Oracle Database version 12c, Oracle introduced a new feature named "Unified Auditing" which basically consolidates all the audit trails into one single view - UNIFIED_AUDIT_TRAIL. There is no option to write audit records to the operating system.

This feature is also introducing a new set of predefined audit policies and you can also create your own audit policies - they could be either simple or more complex depends on what you need. 

For example, you can create audit policy that audits delete statements on specific tables (ORDERS and ORDER_LINE tables for example) by a specific user (user SALES for example) on a specific PDB (PDB named PROD for example).

You can choose whether you want to have one policy that will contain all the audit settings for your database or several audit policies. Personally, I believe that having less policies and even one single policy enabled for your database is better because it has lower overhead on your Oracle instance.

You can view all the audit policies that are configured in your database using AUDIT_UNIFIED_POLICIES dictionary view as you can see in the following example:

SQL> select distinct policy_name
     from audit_unified_policies;

POLICY_NAME
-----------------------------------
ORA_CIS_RECOMMENDATIONS
ORA_LOGON_FAILURES
ORA_RAS_POLICY_MGMT
ORA_DATABASE_PARAMETER
ORA_RAS_SESSION_MGMT
ORA_ACCOUNT_MGMT
ORA_DV_AUDPOL
ORA_SECURECONFIG

8 rows selected.

Each policy contains one or more auditing options. Each auditing option has its type. Let's view all the auditing option types that the "ORA_SECURECONFIG" policy contains:

SQL> select distinct audit_option_type
     from audit_unified_policies
     where policy_name ='ORA_SECURECONFIG';

AUDIT_OPTION_TYPE
------------------
SYSTEM PRIVILEGE
STANDARD ACTION
OBJECT ACTION

Now, let's view all the audit options that defined in the "ORA_SECURECONFIG" policy and their type is "SYSTEM PRIVILEGE".

SQL> select policy_name, audit_option
     from audit_unified_policies
     where policy_name = 'ORA_SECURECONFIG' AND audit_option_type = 'SYSTEM PRIVILEGE';

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     LOGMINING
ORA_SECURECONFIG     TRANSLATE ANY SQL
ORA_SECURECONFIG     EXEMPT REDACTION POLICY
ORA_SECURECONFIG     PURGE DBA_RECYCLEBIN
ORA_SECURECONFIG     ADMINISTER KEY MANAGEMENT
ORA_SECURECONFIG     DROP ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     ALTER ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE ANY SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE SQL TRANSLATION PROFILE
ORA_SECURECONFIG     CREATE EXTERNAL JOB
ORA_SECURECONFIG     CREATE ANY JOB

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     GRANT ANY OBJECT PRIVILEGE
ORA_SECURECONFIG     EXEMPT ACCESS POLICY
ORA_SECURECONFIG     CREATE ANY LIBRARY
ORA_SECURECONFIG     GRANT ANY PRIVILEGE
ORA_SECURECONFIG     DROP ANY PROCEDURE
ORA_SECURECONFIG     ALTER ANY PROCEDURE
ORA_SECURECONFIG     CREATE ANY PROCEDURE
ORA_SECURECONFIG     ALTER DATABASE
ORA_SECURECONFIG     GRANT ANY ROLE
ORA_SECURECONFIG     DROP PUBLIC SYNONYM
ORA_SECURECONFIG     CREATE PUBLIC SYNONYM

POLICY_NAME          AUDIT_OPTION
-------------------- ----------------------------------------
ORA_SECURECONFIG     DROP ANY TABLE
ORA_SECURECONFIG     ALTER ANY TABLE
ORA_SECURECONFIG     CREATE ANY TABLE
ORA_SECURECONFIG     DROP USER
ORA_SECURECONFIG     CREATE USER
ORA_SECURECONFIG     AUDIT SYSTEM
ORA_SECURECONFIG     ALTER SYSTEM

29 rows selected.

Is this feature enabled by default?

I have a new Oracle 12c instance so let's check if this feature is enabled using V$OPTION :

SQL> select value
  2  from V$OPTION
  3  where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
FALSE

Does this mean that the feature is disabled?

Well, not Exactly. 

When you install a new Oracle Database 12c or upgrade to Oracle Database 12c then by default it's configured to work in a "Mixed Mode". Mixed Mode allows you to run the traditional auditing (like you used prior to Oracle 12c) and also benefit the new 12c audit policies. It even has the ORA_SECURECONFIG and ORA_LOGON_FAILURES audit policies enabled by default (so don't be surprised that you see some records in the new UNIFIED_AUDIT_TRAIL view). You can verify which audit policies are enabled in your database using AUDIT_UNIFIED_ENABLED_POLICIES dictionary view. See the following screenshot as an example:

SQL> select *
     FROM audit_unified_enabled_policies;

USER_NAME            POLICY_NAME          ENABLED_ SUCCESS    FAILURE
-------------------- -------------------- -------- ---------- ----------
ALL USERS            ORA_SECURECONFIG     BY       YES        YES
ALL USERS            ORA_LOGON_FAILURES   BY       NO         YES

As I've mentioned, in this configuration Oracle will support both the traditional auditing and the new 12c audit policies so you may find new records written into the pre-12c audit trail - DBA_AUDIT_TRAIL dictionary view and also into the new UNIFIED_AUDIT_TRAIL dictionary view.

Once you will decide that you want to enable the "Pure" unified auditing, Oracle will ignore the "old" AUDIT_TRAIL parameter and will also stop writing records to the old audit trails and will write trail records only to the new UNIFIED_AUDIT_TRAIL dictionary view.

What other benefits the Unified Auditing feature provides?

Performance

Prior to Oracle 12c, if you configures auditing per execution (per "Action"), for example, let's say you configured auditing for every INSERT command by users SALES; in this case, every insert statement by user SALES will insert a row to the SYS.AUD$ table as an autonomous transaction which will obviously generate redo, undo, and perform a commit at the end of the transaction. Until this autonomous transaction is completed, the session will have to wait. In Oracle 12c Unified Auditing, the audit records will be written into an SGA queue and periodically, a dedicated background process will write the records to the AUDSYS schema in the SYSAUX tablespace in order to ensure that the data is persistent.

Note:
The SGA queue for the unified auditing default size is 1 MB, but can be adjusted to a value of [1,30] via the UNIFIED_AUDIT_SGA_QUEUE_SIZE initialization parameter.

You may now ask yourself - does that mean that in a crash scenario soe audits records may be lost? The answer is yes; some audit records may be lost. There's always a trade-off between protection and performance. However, if you prefer to have 100% protection for the audit records in the cost of impacting your database performance you can change this default behavior and tell Oracle to write the audit trails records into the AUDSYS schema immediately (see the "Managing the Unified Audit Trail" section).

Security

Prior to Oracle 12c, if you chose to write the audit trail to the DB (audit_tail='db') then all the records will be written to SYS.AUD$ table, which means that the DBA can modify this table. In Oracle 12c Unified Auditing the DBA can't modify the audited information because the base X$ table behind the UNIFIED_AUDIT_TRAIL dictionary view is decrypted.

How to enable the Pure Unified Auditing

In order to enable to Pure Unified Auditing you will need to perform the following steps:

1. Shutdown your Oracle Databases and listeners that are associated to the Oracle Home
2. Relink the Oracle executable to support the Unified Auditing
3. Start your Oracle instances and listener

In order to relink the Oracle executable you will need to execute the "make" command from the $ORACLE_HOME/rdbms/lib directory:

make -f ins_rdbms.mk uniaud_on ioracle

Now, after the relink has been completed, let's query V$OPTION again to verify that the Unified Auditing feature is enabled:

SQL> select value
     from V$OPTION
     where parameter = 'Unified Auditing';

VALUE
-------------------------------------------
TRUE

Changing the default audit trail write mode 

As I've mentioned earlier in this article, this feature offers a great performance improvement by not writing the audit trails immediately, but rather writing into a dedicated SGA queue and periodically writing the audit trails persistenly.

You can change the default write mode (Queued-write mode) to be Immediate-write mode using the DBMS_AUDIT_MGMT package, as follows:

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_WRITE_MODE, 
  DBMS_AUDIT_MGMT.AUDIT_TRAIL_IMMEDIATE_WRITE);
END;
/

Summary

The Unified Auditing is a great security feature that was introduced in Oracle 12c. It offers a simple management for unified (consolidated) auditing that can be accessed via a single dictionary view - UNIFIED_AUDIT_TRAIL. It also provides major security and performance enhancements.

Useful Links

More information and details are available in the Oracle documentation:

• Oracle 12c Documentation - Introduction to Auditing
• Oracle 12c Documentation - Administering the Audit Trail

Oracle Automatic Maintenance Tasks

Introduction

The Oracle automatic maintenance tasks were introduced in version 11g in order to automate tasks that Oracle DBAs used to do manually in the past (i.e. in earlier versions such as 9i, 10g).
This includes the following predefined automated maintenance tasks:

• Automatic Optimizer Statistics Collection - Collecting optimizer statistics for objects with stale or missing statistics
• Automatics Segment Advisor - Collecting storage-related information for segments in order to provide Segment Recommendations on those segments
• Automatic SQL Tuning Advisor - Collecting performance-related information for SQL Statements in order to provide SQL Tuning recommendations

In Oracle 12c the following maintenance task has been added:

• SQL Plan Management (SPM) Evolve Advisor - Collecting information about SQL execution plans in order to automatically evolve execution plans as part of the SQL Plan Management feature

Demonstration

You can query DBA_AUTOTASK_OERATION in order to see all the automatic maintenance tasks along with their status (whether it's enabled or disabled):

You may ask yourself when these jobs are actually running?
The answer is during the Maintenance Windows 

You can check the maintenance windows that are defined for each automatic task using DBA_AUTOTASK_WINDOW_CLIENTS

Following is an example:

The above are 7 predefined maintenance windows that are enabled by default when you install Oracle. If you'd like to see more information for each window you can query DBA_SCHEDULER_WINDOWS and it will display the exact time and duration for each window as well as the associated resource plan. You can modify the maintanence window settings using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package. Read more here.

Please note that during the time window Oracle will create a scheduler job for the automatic task and once the job is completed Oracle will drop it, so don't be surprised that you can find the jobs in DBA_SCHEDULER_JOBS. You can find historical automated tasks information using DBA_AUTOTASK_JOB_HISTORY below is an example:

You can manually disable (or enable) all the maintenance tasks using the DBMS_AUTO_TASK_ADMIN package, for example:
EXECUTE DBMS_AUTO_TASK_ADMIN.DISABLE;

You can also disable or enable a particular maintenance task. Following is an example of how to disable the sql tuning advisor maintenance task
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

If you want to disable to enable a particular maintenance task only for a particular maintenance window, you can use the "window_name" parameter. Following is an example of how to disable the sql tuning advisor maintenance task only for the SATURDAY_WINDOW:
BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => 'SATURDAY_WINDOW');
END;
/

Summary

The goal of this post was to shed light on the automatic maintenance tasks feature which is very useful but you need to aware of its behavior. In this post, I've covered the following items:

• Pre-defined automatic maintenance tasks in 11g & 12c
• Maintanence windows and how to determine their start time and duration
• Viewing historical automated tasks information (time, status, etc.)
• Disabling/enabling automated tasks

UNDO_RETENTION behaviour in autoextend undo tabelspace vs. fixed-size undo tablespace

Introduction

In this post I'd like to clarify a common misconception regarding the UNDO_RETENTION initialization parameter but first, let's start with a short introduction.
The Undo Tablespace is a key component in Oracle Databases. It consists of undo segments which hold the "before" images of the data that has been changed by users running transactions. The undo is essential for Rollback operations, Data Concurrency, Read Consistency and is being used by several flashback features (Flashback Query, Flashback Table, Flashback Version Query, Flashback Transaction Query).
As you probably know, the UNDO_RETENTION initialization parameter specifies the low threshold (in seconds) for the undo retention period. Obviously, this parameter is important and you should bear in mind the different behaviour of this parameter in fixed-size undo tablespace and in autoextend undo tablespace.

Undo Extent Types - Active/Expired/Unexpired

Active undo extents are used by running transactions. These extents will never be overwritten as they are needed in order to perform ROLLBACK operations. 

Expired Undo Extents hold committed information that is older than the UNDO_RETENTION period.

Unexpired undo Extents hold committed information that its age is less than the UNDO_RETENTION period.

You can use the following query in order to calculate the distribution of the different extent types:

select status,
       ROUND (sum_bytes / (1024 * 1024), 0)  MB,
       ROUND ((sum_bytes / undo_size) * 100, 0)  "%"
  from (  select status, SUM (bytes) sum_bytes
            from dba_undo_extents
        group by status),
       (select SUM (a.bytes) undo_size
          from dba_tablespaces c
               join v$tablespace b
                  on (b.name = c.tablespace_name)
               join v$datafile a
                  using (ts#)
         where c.contents = 'UNDO')

Sample Output:

STATUS                                 MB        %
------------------------------    ----------  ----------
ACTIVE                               1040         70
EXPIRED                                27          3
UNEXPIRED                             418         27

If the ACTIVE percent is 100 it means that all the undo extents contain undo images of running transaction and needed for performing a ROLLBACK operation and none of them can be overwritten therefore you'll get "ORA-30036: unable to extend segment by string in undo tablespace" so you need to make sure that the ACTIVE percent is not close to 100.

Also, if the UNEXPIRED pecent is close to 100 it means that almost all of the undo extents hold data of committed information that its age is less than the UNDO_RETENTION period so these undo extents may be overwritten and then the chances of encountering "ORA-01555: Snapshot Too Old" will increase. Other option is that you just set you UNDO_RETENTION to be too high and you can verify it by comparing the UNDO_RETENTION to the longest running query (MAXQUERYLEN column in V$UNDOSTAT).

Fixed-Size Undo Tablespace

For fixed-size undo tablespaces this parameter is being ignored (unless retention guarantee is enabled) and Oracle will automatically tune for the maximum possible undo retention period, based on the undo tablespace size and undo usage history.

Autoextend Undo Tablespace

For autoextend undo tablespace this parameter specifies the minimum retention period the Oracle will attempt to honor. When space in the undo tablespace becomes low (due to running transactions which generate undo records) Oracle will increase the tablespace size (up to the MAXSIZE limit) and once it will reach to the upper limit of the MAXSIZE it will begin to overwrite unexpired undo information therefore the retention period defined in the UNDO_RETENTION period is not guaranteed. This is why the actual period might be lower or even higher. The actual undo retention period can be obtained by querying the TUNED_UNDORETENTION column in V$UNDOSTAT dynamic performance view.

Note: You can specify RETENTION GUARANTEE (in the CREATE UNDO TABLESPACE or ALTER TABLESPACE commands) and then Oracle never overwrites unexpired undo data even if it means that transactions fail due to lack of space in the undo tablespace.

Below is an example of a database that I have configured with UNDO_RETENTION of 15 minutes (i.e. UNDO_RETENTION = 900) but because there are queries with higher elapsed time than 900 (in my case the longest query duration is ~23 minutes (=1358 seconds) and therefore V$UNDOSTAT will report on a TUNED_UNDORETENTION which is higher than 900 in many cases (558 to be more specific). Following is a screenshot of my example:

Useful Links:

• Oracle Documentation Reference - UNDO_RETENTION
• Oracle Documentation Reference - V$UNDOSTAT
• Oracle Documentation - Managing the UNDO Tablespace

Can you use ORA_ROWSCN to determine when was the last modification of a row?

Introduction:
ORA_ROWSCN is a pseudo column that was introduced in Oracle 10g.
This pseudo column will display the SCN of the last time that a row or a the block (in which the row resides) was modified.
You can later use to SCN_TO_TIMESTAMP function to translate the SCN to a timestamp.

Let's see an exmaple:

SQL> create table test (id number, name varchar2(20));
Table created.

SQL> insert into test values (1, 'ABC');
1 row created.

SQL> insert into test values (2, 'XYZ');
1 row created.

SQL> commit;
Commit complete

SQL> update test set name='DEF' where id=1;

SQL> commit;
Commit complete

SQL> select ora_rowscn,scn_to_timestamp(ora_rowscn),id,name from test;

ORA_ROWSCN     TO_TIMESTAMP(ORA_ROWSCN)                   ID           NAME
-----------------   ---------------------------------------  --       --------
  95752977               24-AUG-15 06.45.33.000000000 PM                   1            DEF
  95752977               24-AUG-15 06.45.33.000000000 PM                   2            XYZ

As you can see, the scn of both rows is the same. This is because the table was created without the ROWDEPENDENCIES clause, which ensures that every row in a table has its own SCN, therefore the ORA_ROWSCN displays the scn of the last time the the block was modified.

Let's create the table but this time with the ROWDEPENDENCIES clause:


SQL> drop table test;
Table dropped.

SQL> create table test (id number, name varchar2(20)) ROWDEPENDENCIES;

Table created.

SQL> insert into test values (1, 'ABC');
1 row created.

SQL> insert into test values (2, 'XYZ');
1 row created.

SQL> commit;
Commit complete

SQL> update test set name='DEF' where id=1;

SQL> commit;
Commit complete

SQL> select ora_rowscn,scn_to_timestamp(ora_rowscn),id,name from test;

ORA_ROWSCN     TO_TIMESTAMP(ORA_ROWSCN)                   ID           NAME
-----------------   ---------------------------------------  ---       --------
  95755953               24-AUG-15 07.01.46.000000000 PM                   1            DEF
  95755923               24-AUG-15 07.00.14.000000000 PM                   2            XYZ


In this case, because the table was created with the ROWDEPENDENCIES clause therefore each row has its own SCN and not the same SCN of the last time that the block was modified.
Does it mean that you can use ORA_ROWSCN to track the times of modifications for the rows in your tables? 

The answer is no, because the SCN_TO_TIMESTAMP information is limited to SCN of the last 5 days, and if you will try to translate SCN which is older than the last 5 days you'll get an error:
SQL> select scn_to_timestamp(78199991) from dual;
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

select scn_to_timestamp(78199991) from dual
       *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number

Conclusions:
ORA_ROWSCN is a nice pseudo-column but you need to be aware of its limitations:

1. Unless you specify the ROWDEPENDENCIES clause in the creation of the table, it will report on the last SCN of the block modification and not the row modification.

1. SCN_TO_TIMESTAMP can translate SCN to Timestamps of the last 5 days only.

The title of this article is "Can you use ORA_ROWSCN to determine the last modification of a row?" 
The answer is: no
You'll have to track the change date yourself. I would recommend using one of the following approaches:

• LogMiner
• Auditing