分区表(主对象、子对象)历史统计信息

select  
case when subobject_name  is null then       obj.object_id else null end object_id ,--主对象id
case when obj.object_type  = 'TABLE' then       obj.object_type else null end object_type ,--主对象type
case when obj.object_type  = 'TABLE' then       obj.object_name  else null end object_name ,--主对象name  
case when obj.object_type  = 'TABLE PARTITION' then    obj.data_object_id end subobject_id , --子对象id
case when obj.object_type  = 'TABLE PARTITION' then    obj.object_type end subobject_type , --子对象type
case when subobject_name  is not null then obj.object_name||'('||obj.subobject_name||')'   else   null end subobject_name ,  --子对象name
wri.rowcnt,
wri.blkcnt,
wri.avgrln,
wri.analyzetime  
 from dba_objects obj
 left join
wri$_optstat_tab_history wri
on wri.obj#=obj.data_object_id
where obj.object_name='JS_SKSKJDK_DZFPMX'
 order by wri.analyzetime desc nulls last,obj.subobject_name asc nulls last,
 obj.data_object_id desc nulls last;
 
 
OBJECT_ID OBJECT_TYP OBJECT_NAME          SUBOBJECT_ID SUBOBJECT_TYPE       SUBOBJECT_NAME                     ROWCNT     BLKCNT     AVGRLN ANALYZETI
--------- ---------- -------------------- ------------ -------------------- ------------------------------ ---------- ---------- ---------- ---------
                                                 74938 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201710)        150000     103111        748 24-MAY-17
                                                 74939 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201711)             0         73          0 24-MAY-17
                                                 74940 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201712)             0         73          0 24-MAY-17
                                                 79139 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202001)             0          0          0 24-MAY-17
                                                 79145 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202002)             0          0          0 24-MAY-17
                                                 79151 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202003)             0          0          0 24-MAY-17
                                                 79157 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202004)             0          0          0 24-MAY-17
                                                 79163 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202005)             0          0          0 24-MAY-17
                                                 74929 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201701)           999       3205        746 24-MAY-17
                                                 74930 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201702)             0         73          0 24-MAY-17
                                                 74931 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201703)             0         73          0 24-MAY-17
                                                 74932 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201704)             0         73          0 24-MAY-17
                                                 74933 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201705)            25         73        744 24-MAY-17
                                                 74934 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201706)            15         73        744 24-MAY-17
                                                 74935 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201707)             0         73          0 24-MAY-17
                                                 74936 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201708)             0         73          0 24-MAY-17
                                                 74937 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201709)             0         73          0 24-MAY-17
                                                 74918 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201602)             0        685          0 24-MAY-17
                                                 74919 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201603)             0         73          0 24-MAY-17
                                                 74920 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201604)             0         73          0 24-MAY-17
                                                 74921 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201605)             0         73          0 24-MAY-17
                                                 74922 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201606)             0         73          0 24-MAY-17
                                                 74923 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201607)             0         73          0 24-MAY-17
                                                 74924 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201608)             0         73          0 24-MAY-17
                                                 74925 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201609)             0         73          0 24-MAY-17
                                                 74926 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201610)             0         73          0 24-MAY-17
                                                 74927 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201611)             0         73          0 24-MAY-17
                                                 74928 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201612)             0       3064          0 24-MAY-17
                                                 79151 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202003)             0          0          0 23-MAY-17
                                                 79157 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202004)             0          0          0 23-MAY-17
                                                 79163 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202005)             0          0          0 23-MAY-17
                                                 79139 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202001)             0          0          0 23-MAY-17
                                                 79145 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202002)             0          0          0 23-MAY-17
                                                 74940 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201712)             0         73          0 11-APR-17
                                                 74939 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201711)             0         73          0 11-APR-17
                                                 74930 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201702)             0         73          0 11-APR-17
                                                 74931 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201703)             0         73          0 11-APR-17
                                                 74932 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201704)             0         73          0 11-APR-17
                                                 74933 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201705)            25         73        757 11-APR-17
                                                 74934 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201706)            15         73        757 11-APR-17
                                                 74935 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201707)             0         73          0 11-APR-17
                                                 74936 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201708)             0         73          0 11-APR-17
                                                 74937 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201709)             0         73          0 11-APR-17
                                                 74938 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201710)        150000     103111        761 11-APR-17
                                                 74924 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201608)             0         73          0 11-APR-17
                                                 74925 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201609)             0         73          0 11-APR-17
                                                 74926 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201610)             0         73          0 11-APR-17
                                                 74927 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201611)             0         73          0 11-APR-17
                                                 74928 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201612)             0       3064          0 11-APR-17
                                                 74929 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201701)           999       3205        759 11-APR-17
                                                 74918 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201602)             0        685          0 11-APR-17
                                                 74919 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201603)             0         73          0 11-APR-17
                                                 74920 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201604)             0         73          0 11-APR-17
                                                 74921 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201605)             0         73          0 11-APR-17
                                                 74922 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201606)             0         73          0 11-APR-17
                                                 74923 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201607)             0         73          0 11-APR-17
                                                 79139 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202001)
                                                 79145 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202002)
                                                 79151 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202003)
                                                 79157 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202004)
                                                 79163 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_202005)
    74917 TABLE      JS_SKSKJDK_DZFPMX

posted @ 2017-05-24 20:02  Oracle-fans  阅读(266)  评论(0编辑  收藏  举报