VIEW: SYS.EXPTABSUBPARTLOB_VIEW

Source

View Text - Preformatted

SELECT
              tp.obj#            PONO,
              lp.defts#          LPDEFTS,
              lf.tsno            LFTS,
              lb.defts#          LCDEFTS,
              dsp.lob_spart_ts#  LSPDEFTS,
              tsp.ts#            SPTS
        FROM  sys.tabcompart$ tp, sys.lobcomppart$ lp, sys.partlob$ lb,
              sys.exptabsubpartlobfrag lf, sys.defsubpartlob$ dsp,
              sys.obj$ lspo, sys.obj$ tpo, sys.tabsubpart$ tsp
        WHERE
              lp.tabpartobj# = tp.obj# AND
              lp.lobj# = lb.lobj# and
              lf.parentobjno = lp.partobj# AND
              dsp.bo# = tp.bo# and
              dsp.intcol# = lb.intcol# AND
              lspo.obj# = lf.fragobjno AND
              tpo.obj# = tp.obj# AND
              (lspo.subname = tpo.subname || '_' || dsp.lob_spart_name OR
               (tpo.subname LIKE 'SYS_P%' AND lspo.subname LIKE 'SYS_LOB_SUBP%')) AND
              dsp.spart_position = lf.fragno AND
              tsp.obj# = lf.tabfragobjno
     UNION ALL
        SELECT tp.obj#           PONO,
               lp.defts#         LPDEFTS,
               lf.tsno           LFTS,
               lb.defts#         LCDEFTS,
                                 NULL,
               tsp.ts#           SPTS
        FROM sys.tabcompart$ tp, sys.lobcomppart$ lp, sys.partlob$ lb,
             sys.exptabsubpartlobfrag lf, sys.obj$ lspo, sys.obj$ tpo,
             sys.tabsubpart$ tsp
        WHERE lp.tabpartobj# = tp.obj# AND
              lp.lobj# = lb.lobj# AND
              lf.parentobjno = lp.partobj# AND
              lb.intcol# NOT IN
                (SELECT distinct dsp.intcol#
                  FROM sys.defsubpartlob$ dsp
                  WHERE dsp.bo# = tp.bo#) AND
              lspo.obj# = lf.fragobjno AND
              tpo.obj# = tp.obj# AND
              lspo.subname LIKE 'SYS_LOB_SUBP%' AND
              tsp.obj# = lf.tabfragobjno

View Text - HTML Formatted

SELECT TP.OBJ# PONO
, LP.DEFTS# LPDEFTS
, LF.TSNO LFTS
, LB.DEFTS# LCDEFTS
, DSP.LOB_SPART_TS# LSPDEFTS
, TSP.TS# SPTS
FROM SYS.TABCOMPART$ TP
, SYS.LOBCOMPPART$ LP
, SYS.PARTLOB$ LB
, SYS.EXPTABSUBPARTLOBFRAG LF
, SYS.DEFSUBPARTLOB$ DSP
, SYS.OBJ$ LSPO
, SYS.OBJ$ TPO
, SYS.TABSUBPART$ TSP WHERE LP.TABPARTOBJ# = TP.OBJ# AND LP.LOBJ# = LB.LOBJ# AND LF.PARENTOBJNO = LP.PARTOBJ# AND DSP.BO# = TP.BO# AND DSP.INTCOL# = LB.INTCOL# AND LSPO.OBJ# = LF.FRAGOBJNO AND TPO.OBJ# = TP.OBJ# AND (LSPO.SUBNAME = TPO.SUBNAME || '_' || DSP.LOB_SPART_NAME OR (TPO.SUBNAME LIKE 'SYS_P%'
AND LSPO.SUBNAME LIKE 'SYS_LOB_SUBP%')) AND DSP.SPART_POSITION = LF.FRAGNO AND TSP.OBJ# = LF.TABFRAGOBJNO UNION ALL SELECT TP.OBJ# PONO
, LP.DEFTS# LPDEFTS
, LF.TSNO LFTS
, LB.DEFTS# LCDEFTS
, NULL
, TSP.TS# SPTS
FROM SYS.TABCOMPART$ TP
, SYS.LOBCOMPPART$ LP
, SYS.PARTLOB$ LB
, SYS.EXPTABSUBPARTLOBFRAG LF
, SYS.OBJ$ LSPO
, SYS.OBJ$ TPO
, SYS.TABSUBPART$ TSP
WHERE LP.TABPARTOBJ# = TP.OBJ# AND LP.LOBJ# = LB.LOBJ# AND LF.PARENTOBJNO = LP.PARTOBJ# AND LB.INTCOL# NOT IN (SELECT DISTINCT DSP.INTCOL#
FROM SYS.DEFSUBPARTLOB$ DSP
WHERE DSP.BO# = TP.BO#) AND LSPO.OBJ# = LF.FRAGOBJNO AND TPO.OBJ# = TP.OBJ# AND LSPO.SUBNAME LIKE 'SYS_LOB_SUBP%' AND TSP.OBJ# = LF.TABFRAGOBJNO