分区表主对象、子对象对应关系

16:56:49 DZFPMX(24_112)@test> col object_type for a10
16:57:00 DZFPMX(24_112)@test> col object_name for a20
16:57:07 DZFPMX(24_112)@test> col subobject_id for 99999
16:57:33 DZFPMX(24_112)@test> col subobject_type for a20
16:57:42 DZFPMX(24_112)@test> col subobject_name for a30
16:58:04 DZFPMX(24_112)@test>  col object_id for 99999


16:56:09 DZFPMX(24_112)@test> 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
 from dba_objects obj
where obj.object_name='JS_SKSKJDK_DZFPMX'
order by obj.object_id asc ;


OBJECT_ID OBJECT_TYP OBJECT_NAME          SUBOBJECT_ID SUBOBJECT_TYPE       SUBOBJECT_NAME
--------- ---------- -------------------- ------------ -------------------- ------------------------------
    74917 TABLE      JS_SKSKJDK_DZFPMX
                                                 74918 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201602)
                                                 74919 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201603)
                                                 74920 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201604)
                                                 74921 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201605)
                                                 74922 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201606)
                                                 74923 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201607)
                                                 74924 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201608)
                                                 74925 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201609)
                                                 74926 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201610)
                                                 74927 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201611)
                                                 74928 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201612)
                                                 74929 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201701)
                                                 74930 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201702)
                                                 74931 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201703)
                                                 74932 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201704)
                                                 74933 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201705)
                                                 74934 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201706)
                                                 74935 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201707)
                                                 74936 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201708)
                                                 74937 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201709)
                                                 74938 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201710)
                                                 74939 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201711)
                                                 74940 TABLE PARTITION      JS_SKSKJDK_DZFPMX(P_201712)
                                                 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)

转载于:https://www.cnblogs.com/iyoume2008/p/6900741.html