Skip to Main Content
  • Questions
  • Truncate statement in data dictionary,

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.