Oracle：dba_hist_seg_stat_obj 字典表 初步研究

dba_hist_seg_stat_obj

 

首先，该字典表，是为了配合 dba_hist_seg_stat 而专门抓取的 object信息

接着，抓取的是snap sample 那一特定时刻的object字典信息记录。如果snap sample时刻，对象不存在了，相关的 "???name"信息就会缺失！显示为 ：UNDEFINED  、** MISSING **

最后，我们可以利用该表干吗呢？！可以利用它来查询历史中，各种seg对象的生存状态信息！

 

 

with cte as
 ( --
  select distinct obj#,
                   (case
                       when o.obj# = 4294967295 then
                        o.dataobj#
                       else
                        o.obj#
                   end) obj2#,
                   o.object_type,
                   o.owner,
                   o.object_name,
                   --o.subobject_name,
                   o.tablespace_name
  from dba_hist_seg_stat_obj o
  -- order by 1 desc, 2 desc
  --
  )
select o.*, --
       d.owner,
       d.object_name,
       d.created,
       d.status,
       d.temporary,
       d.generated
from cte o
left join dba_objects d
on o.obj# = d.object_id
where d.object_id is null
      or o.object_name != d.object_name
order by 1 desc, 2 desc