Note
This view is introduced in V2.2.77.
Purpose
The USER_AUDIT_TRAIL view displays standard audit trail entries related to the current user.
Applicability
This view applies only to the Oracle-compatible mode of OceanBase Database.
Columns
| Column | Type | Nullable? | Description |
|---|---|---|---|
| OS_USERNAME | VARCHAR2(255) | NO | This column is not supported. The default value is NULL. |
| USERNAME | VARCHAR2(64) | NO | The name of the user that initiates the audited operation. |
| USERHOST | VARCHAR2(32) | NO | This column is not supported. The default value is NULL. |
| TERMINAL | VARCHAR2(255) | NO | This column is not supported. The default value is NULL. |
| TIMESTAMP | DATE | NO | The date and time when the audit record is created in the local database session time zone. |
| OWNER | VARCHAR2(64) | YES | The creator of the object affected by the action. |
| OBJ_NAME | VARCHAR2(256) | YES | The name of the object affected by the operation. |
| ACTION | NUMBER(38) | NO | The numeric code of the operation type. |
| ACTION_NAME | VARCHAR2(128) | NO | The name of the operation type corresponding to the numeric code in the ACTION column. |
| NEW_OWNER | VARCHAR2(64) | YES | The owner of the object specified by the NEW_NAME column. |
| NEW_NAME | VARCHAR2(256) | YES | The new name of the object after a RENAME operation or the new name of a base object. |
| OBJ_PRIVILEGE | VARCHAR2(255) | NO | The object privilege granted or revoked by a GRANT or REVOKE statement. |
| SYS_PRIVILEGE | VARCHAR2(255) | NO | The system privilege granted or revoked by a GRANT or REVOKE statement. |
| ADMIN_OPTION | VARCHAR2(1) | NO | Indicates whether the ADMIN option was used to grant a role or system privilege. |
| GRANTEE | VARCHAR2(128) | NO | The name specified in the GRANT or REVOKE statement. |
| AUDIT_OPTION | VARCHAR2(40) | NO | The audit option specified in the AUDIT statement. |
| SES_ACTIONS | VARCHAR2(19) | NO | This column is not supported. The default value is NULL. |
| LOGOFF_TIME | DATE | NO | The date and time when the user logged off. |
| LOGOFF_LREAD | NUMBER(38) | NO | The number of logical reads in the session. |
| LOGOFF_PREAD | NUMBER | NO | This column is not supported. The default value is NULL. |
| LOGOFF_LWRITE | NUMBER | NO | This column is not supported. The default value is NULL. |
| LOGOFF_DLOCK | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| COMMENT_TEXT | VARCHAR2(65536) | YES | The text comment on an audit record, providing more information about the audited statement. |
| SESSIONID | NUMBER(38) | NO | For V4.3.x:
|
| ENTRYID | NUMBER(38) | NO | The numeric ID of each audit record. |
| STATEMENTID | NUMBER(38) | NO | The numeric ID of each statement. |
| RETURNCODE | NUMBER(38) | NO | The error code generated by the operation. |
| PRIV_USED | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| CLIENT_ID | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| ECONTEXT_ID | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| SESSION_CPU | NUMBER(38) | NO | The CPU time used by the session. |
| EXTENDED_TIMESTAMP | TIMESTAMP(6) WITH TIME ZONE | NO | The timestamp when the audit record is created in the UTC time zone. |
| PROXY_SESSIONID | NUMBER(38) | NO | The proxy session sequence number. |
| GLOBAL_UID | NUMBER(38) | NO | The global user identifier of the user. |
| INSTANCE_NUMBER | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| OS_PROCESS | VARCHAR2(40) | NO | This column is not supported. The default value is NULL. |
| TRANSACTIONID | VARCHAR2(128) | NO | The transaction identifier that accesses or modifies the object. |
| SCN | NUMBER(38) | NO | This field is not supported. The default value is 0. |
| SQL_BIND | VARCHAR2(2000) | YES | This column is not supported. The default value is NULL. |
| SQL_TEXT | VARCHAR2(2000) | YES | The SQL text of the query. |
| OBJ_EDITION_NAME | VARCHAR2(128) | NO | This column is not supported. The default value is NULL. |
| DBID | NUMBER(38) | NO | The database identifier of the audited database. |
Sample query
View all audit trail entries related to the current user.
obclient [SYS]> SELECT USERNAME, USERHOST, TIMESTAMP, ACTION, SQL_TEXT FROM SYS.USER_AUDIT_TRAIL;
The query result is as follows:
+----------+----------------+-----------+--------+----------+
| USERNAME | USERHOST | TIMESTAMP | ACTION | SQL_TEXT |
+----------+----------------+-----------+--------+----------+
| SYS | 100.xx.xxx.xxx | 08-APR-25 | 100 | CONNECT |
| SYS | 100.xx.xxx.xxx | 08-APR-25 | 100 | CONNECT |
| SYS | 100.xx.xxx.xxx | 08-APR-25 | 100 | CONNECT |
+----------+----------------+-----------+--------+----------+
3 rows in set
References
View all audit trail entries in the current tenant: DBA_AUDIT_TRAIL
View the action ID and the corresponding name in the audit records: AUDIT_ACTIONS
View the audit trail entries generated by
AUDIT EXISTSandAUDIT NOT EXISTS: DBA_AUDIT_EXISTSView the audit trail entries for all objects:
View the audit trail entries for
CONNECTandDISCONNECT:View the audit trail entries for the
GRANT,REVOKE,AUDIT,NOAUDIT, andALTER SYSTEMstatements:For more information about security audit, see the following topics: