For instance if a table has a date/time column then yes of course we can know when the records changed assuming the standard "last updated by" and "last updated when" columns like we see in applications such as EBS.
If we have auditing enabled on the table we are interested in and it captures the right events then yes, we can see when a record changed or was added or deleted in that table.
If the database is in archivelog mode and the archive logs are available then we can use the redo/archive logs and log miner to retrieve changes from the database that affect the table we are interested in.
BUT, inevitably there will be no column, audit or redo available so is there any other way we can find out what happened?
A good example would be system grants in the database. So for instance
SQL> grant create session to sec_auditor;
So, we can see grants in the DBA_SYS_PRIVS view and this has the following structure:
C:\>sqlplus sys/oracle@//192.168.56.34:1521/freepdb1 as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 7 09:28:16 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0
SQL> desc dba_sys_privs
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE VARCHAR2(128)
PRIVILEGE VARCHAR2(40)
ADMIN_OPTION VARCHAR2(3)
COMMON VARCHAR2(3)
INHERITED VARCHAR2(3)
SQL>
As we can see there is no date/timestamp for system privilege grants. I would like to be able to map them if possible as part of an Oracle database security audit opr as part of a forensic investigation of the database.
What is the base tables:
SQL> alter session set container=cdb$root;
Session altered.
SQL>
SQL> set long 1000000
SQL> select text from dba_views where view_name='DBA_SYS_PRIVS';
TEXT
--------------------------------------------------------------------------------
select u.name,spm.name,decode(min(mod(option$, 2)),1,'YES','NO'),
'NO', 'NO'
from sys.system_privilege_map spm, sys.sysauth$ sa, user$ u
where sa.grantee#=u.user# and sa.privilege#=spm.privilege
and bitand(nvl(option$, 0), 4) = 0
group by u.name,spm.name
union all
/* Commonly granted Privileges */
select u.name,spm.name,decode(min(bitand(option$, 16)),16,'YES','NO'),
'YES', decode(SYS_CONTEXT('USERENV', 'CON_ID'), 1, 'NO', 'YES')
from sys.system_privilege_map spm, sys.sysauth$ sa, user$ u
TEXT
--------------------------------------------------------------------------------
where sa.grantee#=u.user# and sa.privilege#=spm.privilege
and bitand(option$,8) = 8
group by u.name,spm.name
union all
/* Federationally granted Privileges */
select u.name,spm.name,decode(min(bitand(option$, 128)),128,'YES','NO'),
'YES',
decode(SYS_CONTEXT('USERENV', 'IS_APPLICATION_PDB'), 'YES', 'YES', 'NO')
from sys.system_privilege_map spm, sys.sysauth$ sa, user$ u
where sa.grantee#=u.user# and sa.privilege#=spm.privilege
and bitand(option$,64) = 64
TEXT
--------------------------------------------------------------------------------
group by u.name,spm.name
SQL>
Quite a complex union of 3 SQLs mapping the user names for grantee and also linking to the system_privilege_map and mainly sys.sysauth$. All the privilege records are basically stored in sysauth$. What is the structure of this table:
SQL> desc sysauth$
Name Null? Type
----------------------------------------- -------- ----------------------------
GRANTEE# NOT NULL NUMBER
PRIVILEGE# NOT NULL NUMBER
SEQUENCE# NOT NULL NUMBER
OPTION$ NUMBER
SQL>
No timestamp is available on this table so we do not have any native columns. The Oracle database uses SCNs (System Change Number) and whilst these are not a timestamp they are useful as they order records. There is a table in the database that is managed by SMON_SCN_TIME table that maintains a short window of mappings between SCN numbers and timestamps. So if the SCN is recent then if we have an SCN we can map to a timestamp. Sounds like it could be useful but there is a window of accuracy with the mapping of 3 seconds so the timestamp can be out by 3 seconds.
So, can we get the SCN from the SYSAUTH$ table for all records / rows?
SQL> select ora_rowscn,grantee#,privilege#,sequence#,option$ from sysauth$ order by sequence#;
ORA_ROWSCN GRANTEE# PRIVILEGE# SEQUENCE# OPTION$
---------- ---------- ---------- ---------- ----------
501880 0 -352 1 12
501880 0 2 2 28
501880 2 -5 3 12
501880 0 3 4 28
501880 3 -40 5 12
501880 3 -60 6 12
501880 3 -80 7 12
501880 3 -90 8 12
501880 3 -105 9 12
501880 3 -140 10 12
501880 3 -151 11 12
ORA_ROWSCN GRANTEE# PRIVILEGE# SEQUENCE# OPTION$
---------- ---------- ---------- ---------- ----------
501880 3 -172 12 12
501880 3 -180 13 12
...
4605568 237 -265 3826
4605568 237 -80 3827
4605568 237 -44 3828
4605568 237 -71 3829
4605568 237 -73 3830
4605568 241 -165 3835
4605568 243 241 3836
4605568 244 242 3837
4605568 243 240 3838
4605568 244 240 3839
4605568 237 239 3840
ORA_ROWSCN GRANTEE# PRIVILEGE# SEQUENCE# OPTION$
---------- ---------- ---------- ---------- ----------
4605568 237 240 3841
1332 rows selected.
SQL>
A few interesting points pop up. 1) There are 1332 rows in this table so you would assume currently there are 1332 system privilege grants:
SQL> select count(*) from dba_sys_privs;
COUNT(*)
----------
1089
SQL>
No, 1089 grants not 1332. We could analyse the difference but that is moving off topic for this discussion. 2) the last row sequence# is 3841 so the assumption here is that there are a very large number of rows missing if we assume one sequence per row and its incremented every time a row is added. Also a quick look shows that there are a lot of gaps in the sequence numbers so that supports the theory of deletions or more correctly revokes of grants.
The biggest issue for our investigation is that the SCN is stored at the block level not the row level. We can see from the output that there are few actual SCNs per row:
SQL> select count(*),ora_rowscn from sysauth$ group by ora_rowscn;
COUNT(*) ORA_ROWSCN
---------- ----------
379 501880
382 617063
368 995672
203 4605568
SQL>
As we can see this table currently uses 4 blocks in my database and from the numbers assigned to each SCN we can see the relative similar number of records for the full blocks as 368 - 382 and clearly the last block with just 203 records is not full yet. We can also see the block numbers in this SQL:
SQL> select count(*),dbms_rowid.rowid_relative_fno(rowid) AS file_no,dbms_rowid.rowid_block_number(rowid) AS block_no from sysauth$ group by dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid);
COUNT(*) FILE_NO BLOCK_NO
---------- ---------- ----------
382 0 1490
379 0 1489
368 0 1491
203 0 1492
SQL>
This proves the SCN is per block and each block has between 203 (latest and filling up) and 382 rows
Lets see the timestamp per block
SQL> select distinct scn_to_timestamp(ora_rowscn) from sysauth$;
select distinct scn_to_timestamp(ora_rowscn) from sysauth$
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL>
The SCN to timestamp mapping is stated to have a precision of 3 seconds, and up to 120 hours of mapping if automatic undo management is used.- SCN to Timestamp Mapping.
The ORA-08181 message means that the mapping is not stored anymore as the database holds limited mapping
The mapping is in the table sys.smon_scn_time
SQL> desc smon_scn_time
Name Null? Type
----------------------------------------- -------- ----------------------------
THREAD NUMBER
TIME_MP NUMBER
TIME_DP DATE
SCN_WRP NUMBER
SCN_BAS NUMBER
NUM_MAPPINGS NUMBER
TIM_SCN_MAP RAW(1200)
SCN NUMBER
ORIG_THREAD NUMBER
SQL>
We can check the max time range in the table with:
SQL> select to_char(min(time_dp),'DD-MON-YYYY HH24:MI:SS'),to_char(max(time_dp),'DD-MON-YYYY HH24:MI:SS') from smon_scn_time;
TO_CHAR(MIN(TIME_DP),'DD-MON- TO_CHAR(MAX(TIME_DP),'DD-MON-
----------------------------- -----------------------------
20-OCT-2025 15:18:02 07-JUN-2026 08:54:39
SQL>
The range in my database is not realistic as this is a 26ai OVA and I installed it two weeks ago so the date range is in reality smaller
So, can we at least get a time for each block
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1492;
TIM ROD
---------------------------------------- -----
05-JUN-26 02.44.52.000000000 PM 1492
SQL>
The other 3 older blocks give ORA-08181 error that the SCN to time map is too old.
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1491;
select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1491
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1490;
select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1490
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL> select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1489;
select distinct scn_to_timestamp(ora_rowscn) tim,dbms_rowid.rowid_block_number(rowid) rod from sysauth$ where dbms_rowid.rowid_block_number(rowid)=1489
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1
SQL>
We can also get the min and max timestamps and SCNs and also work out on average how many SCNs per second:
SQL> select to_char(min(time_dp),'DD-MON-YYYY HH24:MI:SS'),to_char(max(time_dp),'DD-MON-YYYY HH24:MI:SS'),min(scn),max(scn),max(scn)-min(scn) diff,(max(time_dp)-min(time_dp))*86400 sec, ((max(time_dp)-min(time_dp))*86400)/(max(scn)-min(scn)) from smon_scn_time where to_char(time_dp,'MM-YYYY')='06-2026';
TO_CHAR(MIN(TIME_DP),'DD-MON- TO_CHAR(MAX(TIME_DP),'DD-MON- MIN(SCN)
----------------------------- ----------------------------- ----------
MAX(SCN) DIFF SEC
---------- ---------- ----------
((MAX(TIME_DP)-MIN(TIME_DP))*86400)/(MAX(SCN)-MIN(SCN))
-------------------------------------------------------
01-JUN-2026 06:12:37 07-JUN-2026 09:24:41 4390900
4755799 364899 529924
1.45224843
SQL>
So 1.45 SCN per second are generated.
The previous blocks are not in the same scn range but we could calculate fir fun
1 - 4390900 - 995672 = 3395228 SCN / 1.45 = 2,341,536 seconds =
SQL> SELECT to_char(TO_DATE('01-JUN-2026 06:12:37','DD-MON-YYYY HH24:MI:SS')-(2341536/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('01-
--------------------
05-MAY-2026 03:47:01
SQL>
2 - 4390900 - 617063 = 3,773,837/1.45 = 2,602,646.2 =
SQL> SELECT to_char(TO_DATE('01-JUN-2026 06:12:37','DD-MON-YYYY HH24:MI:SS')-(2602646/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('01-
--------------------
02-MAY-2026 03:15:11
SQL>
3 - 4390900 - 501880 = 3889020/1.45=2682082 =
SQL> SELECT to_char(TO_DATE('01-JUN-2026 06:12:37','DD-MON-YYYY HH24:MI:SS')-(2682082/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('01-
--------------------
01-MAY-2026 05:11:15
SQL>
This is clearly wrong as the min time in the SCN map was October 2025 BUT this is a pre-built 26ai OVA and it was not run until 01-JUN-2026 by me. BUT the calculations show how you may get a very rough ball park for a system that is not an OVA that has been stopped and not used until someone starts to install and use it
We can get the last change at a block level - i.e. a date time for the last grant of a SYSTEM privilege but we cannot get timestamps for each grant done since I started to use the database unless we have an audit trail
BUT, often when an attack occurs we want to try and understand what happened and when and part of that is system grants
We can also repeat the calculation not from current SCN map but also for the older blocks with the last SCN map:
SQL> SELECT to_char(TO_DATE('20-OCT-2025 15:18:02','DD-MON-YYYY HH24:MI:SS')-(2341536/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('20-
--------------------
23-SEP-2025 12:52:26
SQL> SELECT to_char(TO_DATE('20-OCT-2025 15:18:02','DD-MON-YYYY HH24:MI:SS')-(2602646/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('20-
--------------------
20-SEP-2025 12:20:36
SQL> SELECT to_char(TO_DATE('20-OCT-2025 15:18:02','DD-MON-YYYY HH24:MI:SS')-(2682082/86400),'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(TO_DATE('20-
--------------------
19-SEP-2025 14:16:40
SQL>
This is probably more realistic
What is database created date?
SQL> select to_char(created,'DD-MON-YYYY HH24:MI:SS') from v$database;
TO_CHAR(CREATED,'DD-MON-YYYYH
-----------------------------
20-OCT-2025 15:04:47
SQL>
And the date time of SYS
SQL> select to_char(ctime,'DD-MON-YYYY HH24:MI:SS'),ptime,exptime,ltime,spare6 from user$ where name='SYS';
TO_CHAR(CTIME,'DD-MON-YYYYHH2 PTIME EXPTIME LTIME SPARE6
----------------------------- --------- --------- --------- ---------
08-OCT-2025 22:00:58
SQL>
And all users
SQL> set lines 220
SQL> col name for a30
SQL> col ctime for a20
SQL> col ptime for a20
SQL> col exptime for a20
SQL> col ltime for a20
SQL> col spare6 for a20
SQL> select name,to_char(ctime,'DD-MON-YYYY HH24:MI:SS') ctime,to_char(ptime,'DD-MON-YYYY HH24:MI:SS') ptime,to_char(exptime,'DD-MON-YYYY HH24:MI:SS') exptime,to_char(ltime,'DD-MON-YYYY HH24:MI:SS') ltime,to_char(spare6,'DD-MON-YYYY HH24:MI:SS') spare6 from user$;
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
SYS 08-OCT-2025 22:00:58
PUBLIC 08-OCT-2025 22:00:58
CONNECT 08-OCT-2025 22:00:58
RESOURCE 08-OCT-2025 22:00:58
DBA 08-OCT-2025 22:00:58
PDB_DBA 08-OCT-2025 22:00:58
AUDIT_ADMIN 08-OCT-2025 22:00:58
AUDIT_VIEWER 08-OCT-2025 22:00:58
AUDSYS 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSTEM 08-OCT-2025 22:00:59 20-OCT-2025 15:54:39
SELECT_CATALOG_ROLE 08-OCT-2025 22:00:59
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
EXECUTE_CATALOG_ROLE 08-OCT-2025 22:00:59
CAPTURE_ADMIN 08-OCT-2025 22:00:59
SYSBACKUP 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSDG 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSKM 08-OCT-2025 22:00:59 20-OCT-2025 15:13:12
SYSRAC 08-OCT-2025 22:00:59
OUTLN 08-OCT-2025 22:01:06 20-OCT-2025 15:13:12
EXP_FULL_DATABASE 08-OCT-2025 22:01:22
IMP_FULL_DATABASE 08-OCT-2025 22:01:22
AVTUNE_PKG_ROLE 08-OCT-2025 22:01:31
DATAPUMP_CLOUD_EXP 08-OCT-2025 22:33:17
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
VECSYS 08-OCT-2025 22:01:32 20-OCT-2025 15:13:12
BAASSYS 08-OCT-2025 22:01:33 20-OCT-2025 15:13:12
CDB_DBA 08-OCT-2025 22:31:55
APPLICATION_TRACE_VIEWER 08-OCT-2025 22:32:18
ACCHK_READ 08-OCT-2025 22:32:18
LOGSTDBY_ADMINISTRATOR 08-OCT-2025 22:32:50
DBFS_ROLE 08-OCT-2025 22:32:56
GSMUSER_ROLE 08-OCT-2025 22:32:57
GSMROOTUSER_ROLE 08-OCT-2025 22:32:57
GSMADMIN_INTERNAL 08-OCT-2025 22:32:57 20-OCT-2025 15:13:12
GSMUSER 08-OCT-2025 22:32:57 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
GGSHAREDCAP 08-OCT-2025 22:32:59 20-OCT-2025 15:13:12
DIP 08-OCT-2025 22:33:07 08-OCT-2025 22:33:07
SAGA_ADM_ROLE 08-OCT-2025 22:33:12
SAGA_PARTICIPANT_ROLE 08-OCT-2025 22:33:12
SAGA_CONNECT_ROLE 08-OCT-2025 22:33:12
AQ_ADMINISTRATOR_ROLE 08-OCT-2025 22:33:14
AQ_USER_ROLE 08-OCT-2025 22:33:14
DATAPUMP_EXP_FULL_DATABASE 08-OCT-2025 22:33:17
DATAPUMP_IMP_FULL_DATABASE 08-OCT-2025 22:33:17
XS$NULL 08-OCT-2025 22:33:29 08-OCT-2025 22:33:29
DATAPUMP_CLOUD_IMP 08-OCT-2025 22:33:17
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
ADM_PARALLEL_EXECUTE_TASK 08-OCT-2025 22:33:27
PROVISIONER 08-OCT-2025 22:33:29
XS_SESSION_ADMIN 08-OCT-2025 22:33:29
XS_NAMESPACE_ADMIN 08-OCT-2025 22:33:29
XS_CACHE_ADMIN 08-OCT-2025 22:33:29
XS_CONNECT 08-OCT-2025 22:33:29
RECOVERY_CATALOG_USER 08-OCT-2025 22:36:16
REMOTE_SCHEDULER_AGENT 08-OCT-2025 22:33:34 20-OCT-2025 15:13:12
DBSFWUSER 08-OCT-2025 22:33:36 20-OCT-2025 15:13:12
SQL_FIREWALL_ADMIN 08-OCT-2025 22:33:38
SQL_FIREWALL_VIEWER 08-OCT-2025 22:33:38
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
OSAK_ADMIN_ROLE 08-OCT-2025 22:33:39
GATHER_SYSTEM_STATISTICS 08-OCT-2025 22:34:30
OPTIMIZER_PROCESSING_RATE 08-OCT-2025 22:34:30
DBMS_MDX_INTERNAL 08-OCT-2025 22:34:46
BDSQL_ADMIN 08-OCT-2025 22:35:37
BDSQL_USER 08-OCT-2025 22:35:37
RECOVERY_CATALOG_OWNER 08-OCT-2025 22:36:16
RECOVERY_CATALOG_OWNER_VPD 08-OCT-2025 22:36:16
OEM_MONITOR 08-OCT-2025 22:49:11
SYSUMF_ROLE 08-OCT-2025 22:44:53
SYS$UMF 08-OCT-2025 22:44:53 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
MAINTPLAN_APP 08-OCT-2025 22:44:53
NOTIFICATIONS_USER 08-OCT-2025 22:44:55
NOTIFICATIONS_ADMIN 08-OCT-2025 22:44:55
SCHEDULER_ADMIN 08-OCT-2025 22:46:26
PPLB_ROLE 08-OCT-2025 22:46:37
DGPDB_ROLE 08-OCT-2025 22:46:37
DGPDB_INT 08-OCT-2025 22:46:37 20-OCT-2025 15:13:12
HS_ADMIN_SELECT_ROLE 08-OCT-2025 22:46:53
HS_ADMIN_EXECUTE_ROLE 08-OCT-2025 22:46:53
HS_ADMIN_ROLE 08-OCT-2025 22:46:53
GLOBAL_AQ_USER_ROLE 08-OCT-2025 22:46:56
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
OGG_CAPTURE 08-OCT-2025 22:49:10
OGG_APPLY 08-OCT-2025 22:49:10
OGG_APPLY_PROCREP 08-OCT-2025 22:49:10
OGG_SHARED_CAPTURE 08-OCT-2025 22:49:10
XSTREAM_CAPTURE 08-OCT-2025 22:49:10
XSTREAM_APPLY 08-OCT-2025 22:49:10
OEM_ADVISOR 08-OCT-2025 22:49:11
PGX_SESSION_GET_PUBLISHED_GRAP 08-OCT-2025 22:49:31
H
DBSNMP 08-OCT-2025 22:49:12 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
APPQOSSYS 08-OCT-2025 22:49:12 20-OCT-2025 15:13:12
GSMCATUSER_ROLE 08-OCT-2025 22:49:14
GSMADMIN_ROLE 08-OCT-2025 22:49:14
GSM_POOLADMIN_ROLE 08-OCT-2025 22:49:14
GDS_CATALOG_SELECT 08-OCT-2025 22:49:14
SHARDED_SCHEMA_OWNER 08-OCT-2025 22:49:14
GSMCATUSER 08-OCT-2025 22:49:14 20-OCT-2025 15:13:12
GGSYS 08-OCT-2025 22:49:23 20-OCT-2025 15:13:12
GGSYS_ROLE 08-OCT-2025 22:49:23
DB_DEVELOPER_ROLE 08-OCT-2025 22:49:30
GRAPH_DEVELOPER 08-OCT-2025 22:49:31
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
GRAPH_ADMINISTRATOR 08-OCT-2025 22:49:31
GRAPH_USER 08-OCT-2025 22:49:31
PGX_SESSION_CREATE 08-OCT-2025 22:49:31
PGX_SERVER_GET_INFO 08-OCT-2025 22:49:31
PGX_SERVER_MANAGE 08-OCT-2025 22:49:31
PGX_SESSION_READ_MODEL 08-OCT-2025 22:49:31
PGX_SESSION_MODIFY_MODEL 08-OCT-2025 22:49:31
PGX_SESSION_NEW_GRAPH 08-OCT-2025 22:49:31
OJVMSYS 08-OCT-2025 23:00:15 20-OCT-2025 15:13:12
PGX_SESSION_COMPILE_ALGORITHM 08-OCT-2025 22:49:31
PGX_SESSION_ADD_PUBLISHED_GRAP 08-OCT-2025 22:49:31
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
H
PGX_SESSION_SET_IDLE_TIMEOUT 08-OCT-2025 22:49:31
XDB 08-OCT-2025 22:50:49 20-OCT-2025 15:13:12
ANONYMOUS 08-OCT-2025 22:50:49 20-OCT-2025 15:13:12
XDBADMIN 08-OCT-2025 22:50:49
XDB_SET_INVOKER 08-OCT-2025 22:51:06
AUTHENTICATEDUSER 08-OCT-2025 22:51:08
XDB_WEBSERVICES 08-OCT-2025 22:51:08
XDB_WEBSERVICES_WITH_PUBLIC 08-OCT-2025 22:51:08
XDB_WEBSERVICES_OVER_HTTP 08-OCT-2025 22:51:08
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
SODA_APP 08-OCT-2025 22:56:28
WMSYS 08-OCT-2025 22:58:20 20-OCT-2025 15:13:12
WM_ADMIN_ROLE 08-OCT-2025 22:58:24
JAVAUSERPRIV 08-OCT-2025 23:00:11
JAVAIDPRIV 08-OCT-2025 23:00:11
JAVASYSPRIV 08-OCT-2025 23:00:11
JAVADEBUGPRIV 08-OCT-2025 23:00:11
EJBCLIENT 08-OCT-2025 23:00:11
JMXSERVER 08-OCT-2025 23:00:11
DV_SECANALYST 08-OCT-2025 23:12:52
DVSYS 08-OCT-2025 23:12:46 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
JAVA_ADMIN 08-OCT-2025 23:00:27
CTXSYS 08-OCT-2025 23:03:06 20-OCT-2025 15:13:12
CTXAPP 08-OCT-2025 23:03:08
OLAP_XS_ADMIN 08-OCT-2025 23:04:38
OLAPSYS 08-OCT-2025 23:04:44 20-OCT-2025 15:13:12
OLAP_DBA 08-OCT-2025 23:04:45
OLAP_USER 08-OCT-2025 23:04:45
MDSYS 08-OCT-2025 23:08:39 20-OCT-2025 15:13:12
MDDATA 08-OCT-2025 23:08:39 20-OCT-2025 15:13:12
RDFCTX_ADMIN 08-OCT-2025 23:11:11
LBACSYS 08-OCT-2025 23:12:07 20-OCT-2025 15:13:12
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
LBAC_DBA 08-OCT-2025 23:12:07
DVF 08-OCT-2025 23:12:46 20-OCT-2025 15:13:12
DV_MONITOR 08-OCT-2025 23:12:52
DV_ADMIN 08-OCT-2025 23:12:52
DV_OWNER 08-OCT-2025 23:12:52
DV_ACCTMGR 08-OCT-2025 23:12:52
DV_PATCH_ADMIN 08-OCT-2025 23:12:52
PDBADMIN 20-OCT-2025 15:13:10 20-OCT-2025 15:13:10 18-APR-2026 15:13:10
DV_STREAMS_ADMIN 08-OCT-2025 23:12:52
DV_GOLDENGATE_ADMIN 08-OCT-2025 23:12:52
DV_XSTREAM_ADMIN 08-OCT-2025 23:12:52
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
DV_GOLDENGATE_REDO_ACCESS 08-OCT-2025 23:12:52
DV_AUDIT_CLEANUP 08-OCT-2025 23:12:52
DV_DATAPUMP_NETWORK_LINK 08-OCT-2025 23:12:52
DV_POLICY_OWNER 08-OCT-2025 23:12:52
HR 20-OCT-2025 15:41:48 20-OCT-2025 15:41:48 20-OCT-2025 15:54:42
APEX_240200 20-OCT-2025 15:14:39 20-OCT-2025 15:14:39
FLOWS_FILES 20-OCT-2025 15:14:39 20-OCT-2025 15:14:39
APEX_PUBLIC_USER 20-OCT-2025 15:14:39 20-OCT-2025 15:39:31
APEX_PUBLIC_ROUTER 20-OCT-2025 15:14:40
APEX_ADMINISTRATOR_READ_ROLE 20-OCT-2025 15:14:56
APEX_ADMINISTRATOR_ROLE 20-OCT-2025 15:14:56
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
APEX_GRANTS_FOR_NEW_USERS_ROLE 20-OCT-2025 15:14:56
ORDS_PUBLIC_USER 20-OCT-2025 15:40:05 20-OCT-2025 15:40:05 01-JUN-2026 07:45:12
ORDS_METADATA 20-OCT-2025 15:40:06
ORDS_ADMINISTRATOR_ROLE 20-OCT-2025 15:40:46
ORDS_RUNTIME_ROLE 20-OCT-2025 15:40:46
_NEXT_USER 08-OCT-2025 22:00:58
SH 20-OCT-2025 15:41:50 20-OCT-2025 15:41:50 20-OCT-2025 15:54:40
CO 20-OCT-2025 15:41:52 20-OCT-2025 15:41:52 20-OCT-2025 15:54:42
HRREST 20-OCT-2025 15:48:33 20-OCT-2025 15:48:33 20-OCT-2025 15:48:39
AV 20-OCT-2025 15:48:35 20-OCT-2025 15:48:35 20-OCT-2025 15:48:35
AOS_GGADMIN 05-JUN-2026 14:22:00 05-JUN-2026 14:22:00
NAME CTIME PTIME EXPTIME LTIME SPARE6
------------------------------ -------------------- -------------------- -------------------- -------------------- --------------------
AOS_DATA 05-JUN-2026 14:43:36 05-JUN-2026 14:43:36 05-JUN-2026 14:43:40 05-JUN-2026 14:43:37
AOS_CODE 05-JUN-2026 14:43:36 05-JUN-2026 14:43:36 05-JUN-2026 14:44:54
AOS_BUILD_DATA 05-JUN-2026 14:43:36
AOS_BUILD_CODE 05-JUN-2026 14:43:36
AOS_CONNECT 05-JUN-2026 14:43:36
AOS_RUNTIME_ADMIN 05-JUN-2026 14:43:40
AOS_RUNTIME_VIEWER 05-JUN-2026 14:43:40
AOS_ADMIN 05-JUN-2026 14:43:40 05-JUN-2026 14:43:40 05-JUN-2026 14:45:30
AOS_VIEWER 05-JUN-2026 14:43:40 05-JUN-2026 14:43:40
181 rows selected.
SQL>
We can also check data files:
SQL> col dt for a20
SQL> col file_name for a50
SQL> set lines 220
SQL> select to_char(creation_time,'DD-MON-YYYY HH24:MI:SS') dt,file_name from v$datafile,dba_data_files where file#=file_id;
DT FILE_NAME
-------------------- --------------------------------------------------
20-OCT-2025 15:13:06 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
20-OCT-2025 15:13:54 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
20-OCT-2025 15:13:06 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
20-OCT-2025 15:13:07 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
SQL>
And the filenames:
SQL> col dt for 9999999999
SQL> col file_name for a50
SQL> set lines 220
SQL> select checkpoint_change# dt,file_name from v$datafile_header,dba_data_files where file#=file_id;
DT FILE_NAME
----------- --------------------------------------------------
4736767 /opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf
4736767 /opt/oracle/oradata/FREE/FREEPDB1/users01.dbf
4736767 /opt/oracle/oradata/FREE/FREEPDB1/system01.dbf
4736767 /opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf
SQL>
In summary, we cannot get a timestamp from the database for a row in a table that does not itself have a timestamp column unless we also have audit or we can use redo logs. We can use SCN but only at the block level and we could also do ID analysis to see what rows are missing but not what is in those rows. We could use off line data block analysis but unless a row was held in the ITL (Interested Transaction List) in the data block we would not get much futher.
SCNs are useful and can create an outline / boundary for instance if the last record of the 3rd block has an SCN and we can estimate a timestamp for the closed block we can estimate the time the block has existed and whether it falls into the range of the attack
#oracleace #sym_42 #oracle #forensics #database #security #scn #hacking #databreach
