4. 查找drop表当时的data_object_id
方法1:使用闪回查询
如果SYS UNDO段未被覆盖的话,可以闪回查找旧的信息。
select obj#, dataobj# from sys.obj$
as of timestamp to_timestamp('2017-02-08 19:00:00','YYYY-MM-DD HH24:MI:SS')
where owner# = (select user_id from dba_users where username='ANDY')
and name='T_TEST';
此外,还可以获取drop掉的表结构:
select column_name, data_type, data_length
from dba_tab_columns as of timestamp to_timestamp('2017-02-08 19:00:00','YYYY-MM-DD HH24:MI:SS')
where owner = 'ANDY'
and table_name='T_TEST';
方法2:AWR表中可能会记录有旧的data_object_id.
select s.snap_id, s.begin_interval_time, o.objno, o.tsno, o.dataobjno from wrh$_seg_stat_obj o
inner join wrm$_snapshot s
on o.snap_id = s.snap_id
where owner='ANDY' and object_name = 'T_TEST'
order by s.snap_id;
方法3:手工sample表空间,查找
GDUL>sample segment all
$cd sample,手工分辨所有的采样数据,最后确认data_object_id。
方法4:使用logminer
先找到truncate时的归档或在线日志,然后从logminer输出中查找truncate的DDL语句,truncate后几行记录update "SYS"."TAB$"和update "SYS"."OBJ$"中会记录有旧的data_object_id。