文档课题：确认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
