GV$LOGMNR_SYS_DBA_SEGS definition in Oracle Database 11.2
GV$LOGMNR_SYS_DBA_SEGS
select
u.inst_id, -- INST_ID NUMBER
NVL(u.name, 'SYS'), -- OWNER VARCHAR2(30)
o.name, -- SEGMENT_NAME VARCHAR2(81)
o.subname, -- PARTITION_NAME VARCHAR2(30)
so.object_type, -- SEGMENT_TYPE VARCHAR2(18)
s.type#, -- SEGMENT_TYPE_ID NUMBER
ts.ts#, -- TABLESPACE_ID NUMBER
ts.name, -- TABLESPACE_NAME VARCHAR2(30)
ts.blocksize, -- BLOCKSIZE NUMBER
f.file#, -- HEADER_FILE NUMBER
s.block#, -- HEADER_BLOCK NUMBER
s.blocks * ts.blocksize, -- BYTES NUMBER
s.blocks, -- BLOCKS NUMBER
s.extents, -- EXTENTS NUMBER
s.iniexts * ts.blocksize, -- INITIAL_EXTENT NUMBER
s.extsize * ts.blocksize, -- NEXT_EXTENT NUMBER
s.minexts, -- MIN_EXTENTS NUMBER
s.maxexts, -- MAX_EXTENTS NUMBER
decode( -- PCT_INCREASE NUMBER
bitand(ts.flags, 3), 1, to_number(NULL), s.extpct),
decode( -- FREELISTS NUMBER
bitand(ts.flags, 32), 32, to_number(NULL), decode(s.lists, 0, 1, s.lists)),
decode( -- FREELIST_GROUPS NUMBER
bitand(ts.flags, 32), 32, to_number(NULL), decode(s.groups, 0, 1, s.groups)),
s.file#, -- RELATIVE_FNO NUMBER
s.cachehint, -- BUFFER_POOL_ID NUMBER
NVL(s.spare1,0), -- SEGMENT_FLAGS NUMBER
o.dataobj# -- SEGMENT_OBJD NUMBER
from
x$logmnr_user$ u,
x$logmnr_obj$ o,
x$logmnr_ts$ ts,
v$logmnr_sys_objects so,
x$logmnr_seg$ s,
x$logmnr_file$ f
where
s.file# = so.header_file and s.block# = so.header_block and s.ts# = so.ts_number and s.ts# = ts.ts# and o.obj# = so.object_id and o.owner# = u.user# (+) and s.type# = so.segment_type_id and o.type# = so.object_type_id and s.ts# = f.ts# and s.file# = f.relfile#
union all
select
u.inst_id, -- INST_ID NUMBER
NVL(u.name, 'SYS'), -- OWNER VARCHAR2(30)
un.name, -- SEGMENT_NAME VARCHAR2(81)
NULL, -- PARTITION_NAME VARCHAR2(30)
decode( -- SEGMENT_TYPE VARCHAR2(18)
s.type#, 1, 'ROLLBACK', 10, 'TYPE2 UNDO'),
s.type#, -- SEGMENT_TYPE_ID NUMBER
ts.ts#, -- TABLESPACE_ID NUMBER
ts.name, -- TABLESPACE_NAME VARCHAR2(30)
ts.blocksize, -- BLOCKSIZE NUMBER
f.file#, -- HEADER_FILE NUMBER
s.block#, -- HEADER_BLOCK NUMBER
s.blocks * ts.blocksize, -- BYTES NUMBER
s.blocks, -- BLOCKS NUMBER
s.extents, -- EXTENTS NUMBER
s.iniexts * ts.blocksize, -- INITIAL_EXTENT NUMBER
s.extsize * ts.blocksize, -- NEXT_EXTENT NUMBER
s.minexts, -- MIN_EXTENTS NUMBER
s.maxexts, -- MAX_EXTENTS NUMBER
s.extpct, -- PCT_INCREASE NUMBER
decode( -- FREELISTS NUMBER
bitand(ts.flags, 32), 32, to_number(NULL), decode(s.lists, 0, 1, s.lists)),
decode( -- FREELIST_GROUPS NUMBER
bitand(ts.flags, 32), 32, to_number(NULL), decode(s.groups, 0, 1, s.groups)),
s.file#, -- RELATIVE_FNO NUMBER
s.cachehint, -- BUFFER_POOL_ID NUMBER
NVL(s.spare1,0), -- SEGMENT_FLAGS NUMBER
un.us# -- SEGMENT_OBJD NUMBER
from
x$logmnr_user$ u,
x$logmnr_ts$ ts,
x$logmnr_undo$ un,
x$logmnr_seg$ s,
x$logmnr_file$ f
where
s.file# = un.file# and s.block# = un.block# and s.ts# = un.ts# and s.ts# = ts.ts# and s.user# = u.user# (+) and s.type# in (1, 10) and un.status$ != 1 and un.ts# = f.ts# and un.file# = f.relfile#
union all
select
u.inst_id, -- INST_ID NUMBER
NVL(u.name, 'SYS'), -- OWNER VARCHAR2(30)
to_char( -- SEGMENT_NAME VARCHAR2(81)
f.file#) || '.' || to_char(s.block#),
NULL, -- PARTITION_NAME VARCHAR2(30)
decode( -- SEGMENT_TYPE VARCHAR2(18)
s.type#, 2, 'DEFERRED ROLLBACK', 3, 'TEMPORARY', 4, 'CACHE', 9, 'SPACE HEADER', 'UNDEFINED'),
s.type#, -- SEGMENT_TYPE_ID NUMBER
ts.ts#, -- TABLESPACE_ID NUMBER
ts.name, -- TABLESPACE_NAME VARCHAR2(30)
ts.blocksize, -- BLOCKSIZE NUMBER
f.file#, -- HEADER_FILE NUMBER
s.block#, -- HEADER_BLOCK NUMBER
s.blocks * ts.blocksize, -- BYTES NUMBER
s.blocks, -- BLOCKS NUMBER
s.extents, -- EXTENTS NUMBER
s.iniexts * ts.blocksize, -- INITIAL_EXTENT NUMBER
s.extsize * ts.blocksize, -- NEXT_EXTENT NUMBER
s.minexts, -- MIN_EXTENTS NUMBER
s.maxexts, -- MAX_EXTENTS NUMBER
decode( -- PCT_INCREASE NUMBER
bitand(ts.flags, 3), 1, to_number(NULL), s.extpct),
decode( -- FREELISTS NUMBER
bitand(ts.flags, 32), 32, to_number(NULL), decode(s.lists, 0, 1, s.lists)),
decode( -- FREELIST_GROUPS NUMBER
bitand(ts.flags, 32), 32, to_number(NULL), decode(s.groups, 0, 1, s.groups)),
s.file#, -- RELATIVE_FNO NUMBER
s.cachehint, -- BUFFER_POOL_ID NUMBER
NVL(s.spare1,0), -- SEGMENT_FLAGS NUMBER
s.hwmincr -- SEGMENT_OBJD NUMBER
from
x$logmnr_user$ u,
x$logmnr_ts$ ts,
x$logmnr_seg$ s,
x$logmnr_file$ f
where
s.ts# = ts.ts# and s.user# = u.user# (+) and s.type# not in (1, 5, 6, 8, 10) and s.ts# = f.ts# and s.file# = f.relfile#