Have you ever needed to see the commit time for one or more records in a table that doesn’t have a physical time stamp field? There is an option that “may” work for you (please see my “gotcha” explanation below the example). The record’s system change number (SCN) is your next best option, i.e., the ORA_ROWSCN pseudocolumn. See the query example below for how to use the ORA_ROWSCN pseudocolumn.
SELECT ORA_ROWSCN AS SCN,
SCN_TO_TIMESTAMP(ORA_ROWSCN) AS SCN_TIME,
USERS AS CHANGE_USER,
USERS_SID AS CHANGE_SESSION,
TIME_STAMP AS CHANGE_TIME
FROM CODY.TEST3 T;
In the above example, the first two fields in the select statement are pseudocolumns related to the SCN. The SCN_TO_TIMESTAMP function converts the SCN to an “approximate” time. The last 4 columns are physical columns in the table. (The last 3 columns were updated by a default constraint to log: (1) the session user who updated the record (“CHANGE_USER”); (2) the user’s session ID at the time of the update (“CHANGE_SESSION”); (3) the system date at the time of the update (“CHANGE_TIME”). See table definition and query output below.)
Example Query Output
Example Table Script
CREATE TABLE CODY.TEST3
USERS VARCHAR2(10) DEFAULT USER,
USERS_SID NUMBER DEFAULT SYS_CONTEXT('USERENV','SID'),
TIME_STAMP DATE DEFAULT SYSDATE
The SCN for a given record is not always representative of a record’s last commit time. By default a table is created with the NOROWDEPENDENCIES option. In a table with this definition the SCN for a given record is actually the SCN of the last commit for the block of data in which that record resides. For example, let’s say the record you are researching is part of block ‘Z’ – we will call this record 1. Block ‘Z’ will most likely have several records in it and there is no guarantee those records will be logically related, i.e., don’t confuse “block” with “partition”. Assume record 1 was last committed a few days ago. Assume one of the records in block ‘Z’ was committed a few minutes ago – we will call this record 2. Assuming no other commits for records in block ‘Z’ have occurred since the last commit of record 2, all the records in block ‘Z’ will have a SCN related to record 2’s last commit. This is because record 2’s commit was the last commit that occurred for block ‘Z’.
In a table created with the ROWDEPENDENCIES option (such as the table in my example above), the SCN for a given row is representative for a commit or group of commits related to the given row. This doesn’t mean each row will have the SCN of the time it was actually written to the table. The SCN will be the time the records were actually committed. For example, if a database session updates 500 rows in one or more tables over the course of 1 hour and performs the commit event at the end of the session then all committed records part of that session will have the same SCN.
You can find out if a table is set up with ROWDEPENDENCIES or NOROWDEPENDENCIES by using the data dictionary. In the TABLES dictionary views (DBA_TABLES, ALL_TABLES, USER_TABLES), the DEPENDECNIES field will either show ‘ENABLED’ (indicating ROWDEPENDENCIES) or ‘DISABLED’ (indicating NOROWDEPENDENCIES). A sample SQL statement is below.Transact-SQL
123 SELECT TABLE_NAME,DEPENDENCIESFROM ALL_TABLES;
This was a recent curiosity research topic of mine and I would like to thank the expert’s on Oracle’s community page for helping me learn more about this issue. View the discussion by clicking here.