Thanks for the question, Shiva.
Asked: January 12, 2018 - 3:43 pm UTC
Last updated: January 14, 2018 - 9:54 am UTC
Version: 11.2
Viewed 1000+ times
You Asked
Hello,
I have observed truncate statement (command_type = 85) doesn't appear in V$SQL. However, it does in V$SQLTEXT and V$SQLTEXT_WITH_NEWLINES.
My intention is to extract the time of the truncate statement. How can I achieve this task without going through ALL_TAB_MODIFICATIONS?
For other command_types (like Insert, Update, Delete, Select, Create), I can get the transaction time using LAST_ACTIVE_TIME in V$SQL
Thanks,
and Connor said...
I don't you'll get it from the dynamic views.
Easy way to track it (and to make sure you *always* see it) would be to audit it
SQL> audit table;
Audit succeeded.
SQL> truncate table t;
Table truncated.
SQL> select * from dba_audit_trail
2 @pr
==============================
OS_USERNAME : XPS13\hamcdc
USERNAME : MCDONAC
USERHOST : WORKGROUP\XPS13
TERMINAL : XPS13
TIMESTAMP : 14-JAN-18
OWNER : MCDONAC
OBJ_NAME : T
ACTION : 85
ACTION_NAME : TRUNCATE TABLE
NEW_OWNER :
NEW_NAME :
OBJ_PRIVILEGE :
SYS_PRIVILEGE :
ADMIN_OPTION :
GRANTEE :
AUDIT_OPTION :
SES_ACTIONS :
LOGOFF_TIME :
LOGOFF_LREAD :
LOGOFF_PREAD :
LOGOFF_LWRITE :
LOGOFF_DLOCK :
COMMENT_TEXT :
SESSIONID : 1145647
ENTRYID : 1
STATEMENTID : 13
RETURNCODE : 0
PRIV_USED :
CLIENT_ID :
ECONTEXT_ID :
SESSION_CPU :
EXTENDED_TIMESTAMP : 14-JAN-18 05.52.36.598000 PM +08:00
PROXY_SESSIONID :
GLOBAL_UID :
INSTANCE_NUMBER : 0
OS_PROCESS : 8584:18188
TRANSACTIONID : 0000000000000000
SCN : 14815851824465
SQL_BIND :
SQL_TEXT :
OBJ_EDITION_NAME :
DBID : 872342268
RLS_INFO :
CURRENT_USER : MCDONAC
PL/SQL procedure successfully completed.
Is this answer out of date? If it is, please let us know via a Comment