确认oracle中表被truncate的具体时间

文档课题：确认oracle中表被truncate的具体时间.
数据库：oracle 11.2.0.4
1、场景准备
SYS@orcl> create table leo.tspitr_emp tablespace leo_ts as select * from scott.emp;

Table created.

SYS@orcl 10:19:45> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------- ------------ --------
         1          1         74   52428800        512          1 YES ACTIVE                 3234061 09:45:08      3235739 10:19:30
         2          1         72   52428800        512          1 YES INACTIVE               3229699 09:19:29      3234056 09:44:59
         3          1         73   52428800        512          1 YES INACTIVE               3234056 09:44:59      3234061 09:45:08
        11          1         75   52428800        512          2 NO  CURRENT                3235739 10:19:30   2.8147E+14
        12          1         68   52428800        512          2 YES INACTIVE               3193114 11:27:02      3200620 14:51:46
        13          1         69   52428800        512          2 YES INACTIVE               3200620 14:51:46      3206240 17:16:24
        14          1         70   52428800        512          2 YES INACTIVE               3206240 17:16:24      3206704 17:29:54
        15          1         71   52428800        512          2 YES INACTIVE               3206704 17:29:54      3229699 09:19:29

8 rows selected.
SYS@orcl 10:19:55> truncate table leo.tspitr_emp;

Table truncated.
2、truncate时间确认
2.1、logminer方案
说明：由于测试库redo较少，从v$log判断truncate操作在75号日志中.此处采用logminer找回truncate误操作的具体时间.生产环境恢复时需确认到准确的时间点，以减少数据的丢失.
--确认该时间段的归档日志，其实此处可以直接分析redo日志，并不一定非要分析归档日志.
SYS@orcl 10:47:57> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",NAME from v$archived_log where first_time between to_date('2022-12-24 10','yyyy-mm-dd hh24') and to_date('2022-12-24 11','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

no rows selected

SYS@orcl 10:56:34> alter system switch logfile;

System altered.
SYS@orcl 10:57:03> col name for a60
SYS@orcl 10:57:28> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",NAME from v$archived_log where first_time between to_date('2022-12-24 10','yyyy-mm-dd hh24') and to_date('2022-12-24 11','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;

 SEQUENCE# TIME                   Size(M) NAME
---------- ------------------- ---------- ------------------------------------------------------------
        75 2022-12-24 10:19:30          1 /u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf
--进行归档日志分析.
SYS@orcl 10:57:29> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf',dbms_logmnr.new);

PL/SQL procedure successfully completed.

SYS@orcl 11:03:41> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.
SYS@orcl 11:06:57> col sql_redo for a50
SYS@orcl 11:07:12> select a.scn,a.timestamp,a.sql_redo from v$logmnr_contents a where table_name='TSPITR_EMP' and operation='DDL' order by a.scn;

       SCN TIMESTAM SQL_REDO
---------- -------- --------------------------------------------------
   3235800 10:20:54 truncate table leo.tspitr_emp;

说明：由此确认到执行truncate的时间为10:20:54.
2.2、dba_objects方案
也可以通过视图dba_objects中的LAST_DDL_TIME字段来确认truncate时间,如下所示：
SYS@orcl 10:39:07> select owner,object_name,object_type,to_char(created,'yyyy-mm-dd hh24:mi:ss'),to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from dba_objects where object_name='TSPITR_EMP';

OWNER      OBJECT_NAME     OBJECT_TYPE         TO_CHAR(CREATED,'YY TO_CHAR(LAST_DDL_TI
---------- --------------- ------------------- ------------------- -------------------
LEO        TSPITR_EMP      TABLE               2022-12-24 09:51:32 2022-12-24 10:20:54
2.3、trace日志
--也可以在trace日志中查看到具体的truncate时间.
Sat Dec 24 10:20:54 2022
truncate table leo.tspitr_emp

相关视图：
dba_tab_modifications中timestamp字段.
参考网址：https://blog.csdn.net/weixin_34233421/article/details/89824907