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> col spare11 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,to_char(spare11,'DD-MON-YYYY HH24:MI:SS') spare11 from sys.user$;
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
SYS 17-AUG-2021 23:05:41
PUBLIC 17-AUG-2021 23:05:41
CONNECT 17-AUG-2021 23:05:41
RESOURCE 17-AUG-2021 23:05:41
DBA 17-AUG-2021 23:05:41
PDB_DBA 17-AUG-2021 23:05:42
AUDIT_ADMIN 17-AUG-2021 23:05:42
AUDIT_VIEWER 17-AUG-2021 23:05:42
AUDSYS 17-AUG-2021 23:05:42 20-JAN-2022 19:26:01
SYSTEM 17-AUG-2021 23:05:42 28-APR-2025 08:33:47
SELECT_CATALOG_ROLE 17-AUG-2021 23:05:42
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
EXECUTE_CATALOG_ROLE 17-AUG-2021 23:05:42
CAPTURE_ADMIN 17-AUG-2021 23:05:42
SYSBACKUP 17-AUG-2021 23:05:42 20-JAN-2022 19:26:01
SYSDG 17-AUG-2021 23:05:42 20-JAN-2022 19:26:01
SYSKM 17-AUG-2021 23:05:42 20-JAN-2022 19:26:01
SYSRAC 17-AUG-2021 23:05:42
OUTLN 17-AUG-2021 23:05:47 20-JAN-2022 19:26:01
EXP_FULL_DATABASE 17-AUG-2021 23:06:09
IMP_FULL_DATABASE 17-AUG-2021 23:06:09
AVTUNE_PKG_ROLE 17-AUG-2021 23:06:20
REMOTE_SCHEDULER_AGENT 17-AUG-2021 23:42:36 20-JAN-2022 19:26:01
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
CDB_DBA 17-AUG-2021 23:40:22
APPLICATION_TRACE_VIEWER 17-AUG-2021 23:40:48
ACCHK_READ 17-AUG-2021 23:40:49
LOGSTDBY_ADMINISTRATOR 17-AUG-2021 23:41:39
DBFS_ROLE 17-AUG-2021 23:41:47
GSMUSER_ROLE 17-AUG-2021 23:41:50
GSMROOTUSER_ROLE 17-AUG-2021 23:41:50
GSMADMIN_INTERNAL 17-AUG-2021 23:41:50 20-JAN-2022 19:26:01
GSMUSER 17-AUG-2021 23:41:51 20-JAN-2022 19:26:01
DIP 17-AUG-2021 23:41:58 17-AUG-2021 23:41:58
AQ_ADMINISTRATOR_ROLE 17-AUG-2021 23:42:09
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
AQ_USER_ROLE 17-AUG-2021 23:42:10
DATAPUMP_EXP_FULL_DATABASE 17-AUG-2021 23:42:12
DATAPUMP_IMP_FULL_DATABASE 17-AUG-2021 23:42:12
ADM_PARALLEL_EXECUTE_TASK 17-AUG-2021 23:42:27
PROVISIONER 17-AUG-2021 23:42:30
XS_SESSION_ADMIN 17-AUG-2021 23:42:30
XS_NAMESPACE_ADMIN 17-AUG-2021 23:42:30
XS_CACHE_ADMIN 17-AUG-2021 23:42:30
XS_CONNECT 17-AUG-2021 23:42:30
XS$NULL 17-AUG-2021 23:42:31 17-AUG-2021 23:42:31
HS_ADMIN_EXECUTE_ROLE 17-AUG-2021 23:56:55
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
DBSFWUSER 17-AUG-2021 23:42:38 20-JAN-2022 19:26:01
GATHER_SYSTEM_STATISTICS 17-AUG-2021 23:44:25
OPTIMIZER_PROCESSING_RATE 17-AUG-2021 23:44:25
DBMS_MDX_INTERNAL 17-AUG-2021 23:44:38
ORACLE_OCM 17-AUG-2021 23:44:51 20-JAN-2022 19:26:01
BDSQL_ADMIN 17-AUG-2021 23:45:09
BDSQL_USER 17-AUG-2021 23:45:09
RECOVERY_CATALOG_OWNER 17-AUG-2021 23:45:24
RECOVERY_CATALOG_OWNER_VPD 17-AUG-2021 23:45:24
RECOVERY_CATALOG_USER 17-AUG-2021 23:45:24
EM_EXPRESS_BASIC 17-AUG-2021 23:51:01
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
EM_EXPRESS_ALL 17-AUG-2021 23:51:01
SYSUMF_ROLE 17-AUG-2021 23:54:11
SYS$UMF 17-AUG-2021 23:54:11 20-JAN-2022 19:26:01
MAINTPLAN_APP 17-AUG-2021 23:54:12
SCHEDULER_ADMIN 17-AUG-2021 23:56:14
PPLB_ROLE 17-AUG-2021 23:56:33
DGPDB_INT 17-AUG-2021 23:56:34 20-JAN-2022 19:26:01
HS_ADMIN_SELECT_ROLE 17-AUG-2021 23:56:55
SODA_APP 18-AUG-2021 00:08:17
HS_ADMIN_ROLE 17-AUG-2021 23:56:55
GLOBAL_AQ_USER_ROLE 17-AUG-2021 23:56:58
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
OEM_ADVISOR 17-AUG-2021 23:59:19
OEM_MONITOR 17-AUG-2021 23:59:19
DBSNMP 17-AUG-2021 23:59:19 20-JAN-2022 19:26:01
APPQOSSYS 17-AUG-2021 23:59:22 20-JAN-2022 19:26:01
GSMADMIN_ROLE 17-AUG-2021 23:59:23
GSM_POOLADMIN_ROLE 17-AUG-2021 23:59:23
GDS_CATALOG_SELECT 17-AUG-2021 23:59:24
GSMCATUSER 17-AUG-2021 23:59:24 20-JAN-2022 19:26:01
GGSYS 17-AUG-2021 23:59:33 20-JAN-2022 19:26:01
GGSYS_ROLE 17-AUG-2021 23:59:34
XDB 18-AUG-2021 00:01:47 20-JAN-2022 19:26:01
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
ANONYMOUS 18-AUG-2021 00:01:47 20-JAN-2022 19:26:01
XDBADMIN 18-AUG-2021 00:01:47
XDB_SET_INVOKER 18-AUG-2021 00:02:13
AUTHENTICATEDUSER 18-AUG-2021 00:02:16
XDB_WEBSERVICES 18-AUG-2021 00:02:16
XDB_WEBSERVICES_WITH_PUBLIC 18-AUG-2021 00:02:16
XDB_WEBSERVICES_OVER_HTTP 18-AUG-2021 00:02:16
OLAPSYS 18-AUG-2021 00:19:25 20-JAN-2022 19:26:01
DATAPATCH_ROLE 18-AUG-2021 00:08:26
WMSYS 18-AUG-2021 00:09:33 20-JAN-2022 19:26:01
WM_ADMIN_ROLE 18-AUG-2021 00:09:39
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
JAVAUSERPRIV 18-AUG-2021 00:11:53
JAVAIDPRIV 18-AUG-2021 00:11:53
JAVASYSPRIV 18-AUG-2021 00:11:53
JAVADEBUGPRIV 18-AUG-2021 00:11:53
EJBCLIENT 18-AUG-2021 00:11:53
JMXSERVER 18-AUG-2021 00:11:53
DBJAVASCRIPT 18-AUG-2021 00:11:53
OJVMSYS 18-AUG-2021 00:11:54 20-JAN-2022 19:26:01
JAVA_ADMIN 18-AUG-2021 00:12:19
CTXSYS 18-AUG-2021 00:15:16 20-JAN-2022 19:26:01
CTXAPP 18-AUG-2021 00:15:19
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
ORDSYS 18-AUG-2021 00:16:17 20-JAN-2022 19:26:01
ORDDATA 18-AUG-2021 00:16:17 20-JAN-2022 19:26:01
ORDPLUGINS 18-AUG-2021 00:16:17 20-JAN-2022 19:26:01
SI_INFORMTN_SCHEMA 18-AUG-2021 00:16:17 20-JAN-2022 19:26:01
ORDADMIN 18-AUG-2021 00:17:38
OLAP_XS_ADMIN 18-AUG-2021 00:19:20
DVSYS 18-AUG-2021 00:31:50 20-JAN-2022 19:26:01
DV_SECANALYST 18-AUG-2021 00:32:01
OLAP_DBA 18-AUG-2021 00:19:27
OLAP_USER 18-AUG-2021 00:19:27
MDSYS 18-AUG-2021 00:25:52 20-JAN-2022 19:26:01
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
MDDATA 18-AUG-2021 00:25:52 20-JAN-2022 19:26:01
RDFCTX_ADMIN 18-AUG-2021 00:29:43
LBACSYS 18-AUG-2021 00:30:51 20-JAN-2022 19:26:01
LBAC_DBA 18-AUG-2021 00:30:52
DVF 18-AUG-2021 00:31:50 20-JAN-2022 19:26:01
DV_MONITOR 18-AUG-2021 00:32:01
DV_ADMIN 18-AUG-2021 00:32:01
DV_OWNER 18-AUG-2021 00:32:01
DV_ACCTMGR 18-AUG-2021 00:32:01
DV_PATCH_ADMIN 18-AUG-2021 00:32:01
DV_STREAMS_ADMIN 18-AUG-2021 00:32:01
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
DV_GOLDENGATE_ADMIN 18-AUG-2021 00:32:01
DV_XSTREAM_ADMIN 18-AUG-2021 00:32:01
DV_GOLDENGATE_REDO_ACCESS 18-AUG-2021 00:32:01
DV_AUDIT_CLEANUP 18-AUG-2021 00:32:01
PDBADMIN 20-JAN-2022 19:26:00 20-JAN-2022 19:26:00 19-JUL-2022 19:26:00 27-JAN-2023 20:59:47
DV_DATAPUMP_NETWORK_LINK 18-AUG-2021 00:32:01
DV_POLICY_OWNER 18-AUG-2021 00:32:01
PFCL_VD 13-JAN-2023 10:11:06 13-JAN-2023 10:11:06 12-JUL-2023 10:11:06 13-MAR-2025 10:08:47 13-JAN-2023 10:11:07
PFCL_VP 13-JAN-2023 10:11:06 13-JAN-2023 10:11:06 12-JUL-2023 10:11:06 13-MAR-2025 10:08:47
APP_ROLE 27-JAN-2023 13:04:43
AA 27-JAN-2023 13:04:56 27-SEP-2023 16:10:39 18-MAR-2025 10:11:03 11-MAR-2025 10:11:03
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
BB 27-JAN-2023 13:05:15 27-JAN-2023 13:05:15 26-JUL-2023 13:05:15 13-MAR-2025 10:08:47
ORASCAN 27-JAN-2023 13:35:42 27-JAN-2023 13:35:42 17-MAR-2025 11:13:01 13-MAR-2025 16:29:11
PETE1 05-MAY-2023 09:13:00
PETE2 05-MAY-2023 09:13:05
PETE3 05-MAY-2023 09:13:10
TESTTEST 14-JUL-2023 09:59:52 14-JUL-2023 09:59:52 10-JAN-2024 09:59:52 13-MAR-2025 10:08:47
XXA 08-AUG-2023 15:06:52 08-AUG-2023 15:06:52 04-FEB-2024 15:06:52 13-MAR-2025 10:08:47 08-AUG-2023 14:12:16
XXB 08-AUG-2023 15:08:20 08-AUG-2023 15:08:20 04-FEB-2024 15:08:20 13-MAR-2025 10:08:47
USER03 06-MAR-2025 14:10:25 06-MAR-2025 14:10:25 02-SEP-2025 14:10:25
U1 12-SEP-2023 10:01:41 12-SEP-2023 10:02:44 10-MAR-2024 10:02:44 13-MAR-2025 10:08:47 12-SEP-2023 09:03:49
UU 27-SEP-2023 19:09:24 27-SEP-2023 19:09:24 25-MAR-2024 19:09:24 13-MAR-2025 10:08:47 27-SEP-2023 18:11:10
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
DEV2 27-SEP-2023 20:17:53 27-SEP-2023 20:17:53 25-MAR-2024 20:17:53 13-MAR-2025 10:08:47
ORABLOGDBA 27-SEP-2023 20:00:46 27-SEP-2023 20:00:46 25-MAR-2024 20:00:46 13-MAR-2025 10:08:47 27-SEP-2023 19:00:54
ERIC 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18
EMIL 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18
ZULIA 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18
PETE 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18
FRED 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18
BILL 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18
JIM 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18
IMPORTER 06-MAR-2025 14:10:18 06-MAR-2025 14:10:18 02-SEP-2025 14:10:18 06-MAR-2025 14:10:23
ORABLOG_ADMIN 06-MAR-2025 14:10:18
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
ORABLOG_READ 06-MAR-2025 14:10:24
ORABLOG_CREDIT 06-MAR-2025 14:10:24
ORABLOG_SUPPORT 06-MAR-2025 14:10:24
USER01 06-MAR-2025 14:10:24 06-MAR-2025 14:10:24 02-SEP-2025 14:10:24
USER02 06-MAR-2025 14:10:25 06-MAR-2025 14:10:25 02-SEP-2025 14:10:25
FACADM 21-JAN-2022 19:01:55 10-SEP-2023 13:40:08 08-MAR-2024 13:40:08 13-MAR-2025 10:08:47 27-SEP-2023 18:12:54
SCH 22-JAN-2022 23:15:50 22-JAN-2022 23:15:50 21-JUL-2022 23:15:50 27-JAN-2023 20:59:47 22-JAN-2022 23:17:21
USE 22-JAN-2022 23:20:18 22-JAN-2022 23:20:18 21-JUL-2022 23:20:18 27-JAN-2023 20:59:47 23-JAN-2022 01:33:18
_NEXT_USER 17-AUG-2021 23:05:41
DV_CONNECT2 24-JUN-2026 13:17:06 24-JUN-2026 13:17:06 21-DEC-2026 13:17:06 24-JUN-2026 12:21:07
C##DVO 24-JUN-2026 13:17:24 24-JUN-2026 14:26:10
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
C##DVO_BK 24-JUN-2026 13:17:24 24-JUN-2026 12:20:56
C##ACCO 24-JUN-2026 13:17:24 24-JUN-2026 14:25:51
C##ACCO_BK 24-JUN-2026 13:17:24 24-JUN-2026 12:20:56
SECURITY 24-JUN-2026 13:21:28 24-JUN-2026 13:21:28 21-DEC-2026 13:21:28
SEC_AUDITOR 24-JUN-2026 13:21:59 24-JUN-2026 13:21:59 21-DEC-2026 13:21:59 25-JUN-2026 06:59:44
ORABLOG 21-JAN-2022 17:48:28 24-JUN-2026 13:17:06 21-DEC-2026 13:17:06 24-JUN-2026 12:21:30
USER04 06-MAR-2025 14:10:25 06-MAR-2025 14:10:25 02-SEP-2025 14:10:25
USER05 06-MAR-2025 14:10:25 06-MAR-2025 14:10:25 02-SEP-2025 14:10:25
USER06 06-MAR-2025 14:10:26 06-MAR-2025 14:10:26 02-SEP-2025 14:10:26
USER07 06-MAR-2025 14:10:26 06-MAR-2025 14:10:26 02-SEP-2025 14:10:26
BACK01 06-MAR-2025 14:10:26 06-MAR-2025 14:10:26 02-SEP-2025 14:10:26
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
BATCH01 06-MAR-2025 14:10:26 06-MAR-2025 14:10:26 02-SEP-2025 14:10:26
FEED01 06-MAR-2025 14:10:26 06-MAR-2025 14:10:26 02-SEP-2025 14:10:26
DEV01 06-MAR-2025 14:10:26 06-MAR-2025 14:10:26 02-SEP-2025 14:10:26
DEV02 06-MAR-2025 14:10:26 06-MAR-2025 14:10:26 02-SEP-2025 14:10:26
DEV03 06-MAR-2025 14:10:27 06-MAR-2025 14:10:27 02-SEP-2025 14:10:27
RISK01 06-MAR-2025 14:10:27 06-MAR-2025 14:10:27 02-SEP-2025 14:10:27
DEV 12-MAR-2025 16:46:00 12-MAR-2025 16:46:00 08-SEP-2025 16:46:00 12-MAR-2025 16:46:07
DBAUSER 11-MAR-2025 10:04:09 11-MAR-2025 10:04:09 07-SEP-2025 10:04:09 11-MAR-2025 10:04:12
VU 13-MAR-2025 13:18:36 13-MAR-2025 13:18:36 09-SEP-2025 13:18:36 13-MAR-2025 13:20:31
VA 13-MAR-2025 13:18:42 13-MAR-2025 13:18:42 09-SEP-2025 13:18:42 13-MAR-2025 13:19:46
VB 13-MAR-2025 13:19:55 13-MAR-2025 13:19:55 09-SEP-2025 13:19:55 13-MAR-2025 13:19:59
NAME CTIME PTIME EXPTIME LTIME SPARE6 SPARE11
------------------------------ -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
CCKEY 13-MAR-2025 13:21:29 13-MAR-2025 13:21:29 09-SEP-2025 13:21:29 13-MAR-2025 13:22:28
PWDP 13-MAR-2025 13:25:47 13-MAR-2025 13:26:06 09-SEP-2025 13:26:06
DV_CONNECT 24-JUN-2026 13:17:06 24-JUN-2026 13:17:06 21-DEC-2026 13:17:06 24-JUN-2026 12:21:16
SCOTT 09-APR-2025 15:48:25 09-APR-2025 15:48:25 06-OCT-2025 15:48:25
191 rows selected.
SQL>
I was looking at all of the columns in USER$ covering the CTIME which records the timestamp that the user was created and PTIME which shows the timestamp of the last password change, EXPTIME which shows when the accounts password was expired and LTIME which shows when the account was locked. Of course apart from CTIME the other columns are not obvious.
Spare6 has some entries in my 21c database. But what is it used for:
SQL> alter session set container=cdb$root;
Session altered.
SQL> set long 1000000
SQL> select text from dba_views where view_name='DBA_USERS';
TEXT
--------------------------------------------------------------------------------
select u.name, u.user#,
decode(u.password, 'GLOBAL', u.password,
'EXTERNAL', u.password,
NULL),
m.status,
decode(mod(u.astatus, 16), 4, u.ltime,
5, u.ltime,
6, u.ltime,
8, u.ltime,
9, u.ltime,
10, u.ltime, to_date(NULL)),
TEXT
--------------------------------------------------------------------------------
decode(mod(u.astatus, 16),
1, u.exptime,
2, u.exptime,
5, u.exptime,
6, u.exptime,
9, u.exptime,
10, u.exptime,
decode(bitand(u.spare1,65536), 65536, to_date(NULL),
decode(u.password, 'GLOBAL', to_date(NULL),
'EXTERNAL', to_date(NULL),
decode(u.ptime, '', to_date(NULL),
TEXT
--------------------------------------------------------------------------------
decode(pr.limit#, 2147483647, to_date(NULL),
decode(pr.limit#, 0,
decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
dp.limit#/86400),
u.ptime + pr.limit#/86400)))))),
dts.name, tts.name, ltts.name,
u.ctime, p.name,
nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
u.ext_username,
decode(bitand(u.spare1, 65536), 65536, NULL, decode(
REGEXP_INSTR(
TEXT
--------------------------------------------------------------------------------
NVL2(u.password, u.password, ' '),
'^ $'
),
0,
decode(length(u.password), 16, '10G ', NULL),
''
) ||
decode(
REGEXP_INSTR(
REGEXP_REPLACE(
NVL2(u.spare4, u.spare4, ' '),
TEXT
--------------------------------------------------------------------------------
'S:000000000000000000000000000000000000000000000000000000000000',
'not_a_verifier'
),
'S:'
),
0, '', '11G '
) ||
decode(
REGEXP_INSTR(
NVL2(u.spare4, u.spare4, ' '),
'T:'
TEXT
--------------------------------------------------------------------------------
),
0, '', '12C '
) ||
decode(
REGEXP_INSTR(
REGEXP_REPLACE(
NVL2(u.spare4, u.spare4, ' '),
'H:00000000000000000000000000000000',
'not_a_verifier'
),
'H:'
TEXT
--------------------------------------------------------------------------------
),
0, '', 'HTTP '
)),
decode(bitand(u.spare1, 16),
16, 'Y',
'N'),
decode(bitand(u.spare1,65536), 65536, 'NONE',
decode(u.password, 'GLOBAL', 'GLOBAL',
'EXTERNAL', 'EXTERNAL',
'PASSWORD')),
decode(bitand(u.spare1, 10272),
TEXT
--------------------------------------------------------------------------------
32, 'Y', 2048, 'Y', 2080, 'Y',
8192, 'Y', 8224, 'Y', 10240, 'Y',
10272, 'Y',
'N'),
decode(bitand(u.spare1, 128), 0, 'NO', 'YES'),
from_tz(to_timestamp(to_char(u.spare6, 'DD-MON-YYYY HH24:MI:SS'),
'DD-MON-YYYY HH24:MI:SS'), '0:00')
at time zone sessiontimezone,
decode(bitand(u.spare1, 256), 256, 'Y', 'N'),
decode(bitand(u.spare1, 4224),
128, decode(SYS_CONTEXT('USERENV', 'CON_ID'), 1, 'NO', 'YES'),
TEXT
--------------------------------------------------------------------------------
4224, decode(SYS_CONTEXT('USERENV', 'IS_APPLICATION_PDB'),
'YES', 'YES', 'NO'),
'NO'),
nls_collation_name(nvl(u.spare3, 16382)),
-- IMPLICIT
decode(bitand(u.spare1, 32768), 32768, 'YES', 'NO'),
-- ALL_SHARD
decode(bitand(u.spare1, 16384), 16384, 'YES', 'NO'),
-- EXTERNAL_SHARD
decode(bitand(u.spare1, 262144), 262144, 'YES', 'NO'),
-- PASSWORD_CHANGE_DATE
TEXT
--------------------------------------------------------------------------------
u.ptime,
-- MANDATORY_PROFILE_VIOLATION
decode(bitand(u.astatus, 64), 64, 'YES', 'NO')
from sys.user$ u
left outer join sys.resource_group_mapping$ cgm
on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
cgm.value = u.name) left outer join sys.ts$ ltts
on (u.spare9 = ltts.ts#),
sys.ts$ dts, sys.ts$ tts, sys.profname$ p,
sys.user_astatus_map m, sys.profile$ pr, sys.profile$ dp
where u.datats# = dts.ts#
TEXT
--------------------------------------------------------------------------------
and u.resource$ = p.profile#
and u.tempts# = tts.ts#
and ((u.astatus = m.status#) or
(u.astatus = (m.status# + 16 - BITAND(m.status#, 16))) or
(u.astatus = (m.status# + 64 - BITAND(m.status#, 64))))
and u.type# = 1
and u.resource$ = pr.profile#
and dp.profile# = 0
and dp.type#=1
and dp.resource#=1
and pr.type# = 1
TEXT
--------------------------------------------------------------------------------
and pr.resource# = 1
SQL>
From the source code of the view we can work out that user$.spare6 is the LAST_LOGIN time and we can check this via the view:
SQL> col username for a30
SQL> col last_login for a40
SQL> select username,to_char(last_login,'DD-MON-YYYY HH24:MI:SS') last_login from dba_users where last_login is not null;
USERNAME LAST_LOGIN
------------------------------ ----------------------------------------
SYSTEM 28-APR-2025 09:33:47
AA 11-MAR-2025 11:11:03
XXA 08-AUG-2023 15:12:16
VU 13-MAR-2025 14:20:31
C##ACCO 24-JUN-2026 15:25:51
USE 23-JAN-2022 02:33:18
DBAUSER 11-MAR-2025 11:04:12
U1 12-SEP-2023 10:03:49
SCH 23-JAN-2022 00:17:21
IMPORTER 06-MAR-2025 15:10:23
C##DVO 24-JUN-2026 15:26:10
USERNAME LAST_LOGIN
------------------------------ ----------------------------------------
SEC_AUDITOR 25-JUN-2026 07:59:44
DV_CONNECT 24-JUN-2026 13:21:16
FACADM 27-SEP-2023 19:12:54
DEV 12-MAR-2025 17:46:07
VA 13-MAR-2025 14:19:46
DV_CONNECT2 24-JUN-2026 13:21:07
ORABLOGDBA 27-SEP-2023 20:00:54
ORABLOG 24-JUN-2026 13:21:30
VB 13-MAR-2025 14:19:59
PFCL_VD 13-JAN-2023 11:11:07
C##ACCO_BK 24-JUN-2026 13:20:56
USERNAME LAST_LOGIN
------------------------------ ----------------------------------------
ORASCAN 13-MAR-2025 17:29:11
UU 27-SEP-2023 19:11:10
CCKEY 13-MAR-2025 14:22:28
C##DVO_BK 24-JUN-2026 13:20:56
26 rows selected.
SQL>
That looks correct but the view does not use spare 11 so what is it used for? If we look at dcore.bsq where user$ is created we see:
/* also as base schema name for adjunct schemas */
spare1 number, /* used for schema level supp. logging: see ktscts.h */
/* 0x80 - 128 (CDB common users) */
/* 0x100 - 256 (Oracle maintained user) */
/* 0x1000 - 4096 (For Application common users, both
COMMON(128) and APPCMN(4096) are set) */
/* 0x8000 - 32768 (Implicit Application common users) */
/* 0x10000 - 65536 (NO authentication user) */
/* 0x80000 - 524288 (Protected user) */
/* 0x200000 - 2097152 (User was created as PDB Admin at the
time of PDB creation) */
/* spare2 is used to store */
/* - edition id for adjunct schemas (type# = 2) */
/* - base schema id for schema synonyms (type# = 3) */
spare2 number,
spare3 number, /* used for schema-level default collation */
spare4 varchar2(1000),
spare5 varchar2(1000),
spare6 date, /* used for Last Successful Logon Time */
spare7 varchar2(4000),
spare8 varchar2(4000),
spare9 number, /* default local temporaty tablespace */
spare10 number, /* Creation Application ID */
spare11 timestamp
)
SPARE11 does not have any comment against it and a grep of the .sql files does not show any use of the column. We have to assume that SPARE11 is not used at this point and in the version I am looking at here which is 21c.
Any date/timestamp is useful for forensics as it places any action on a timeline
#oracleace #sym_42 #oracle #database #security #forensics #timeline
