GV$SEGMENT_STATISTICS

select
s.inst_id,               -- INST_ID NUMBER
u.name,                  -- OWNER VARCHAR2(30)
o.name,                  -- OBJECT_NAME VARCHAR2(30)
o.subname,               -- SUBOBJECT_NAME VARCHAR2(30)
ts.name,                 -- TABLESPACE_NAME VARCHAR2(30)
s.fts_tsn,               -- TS# NUMBER
o.obj#,                  -- OBJ# NUMBER
o.dataobj#,              -- DATAOBJ# NUMBER
decode                   -- OBJECT_TYPE VARCHAR2(18)
  (o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 'UNDEFINED'),
s.fts_statnam,           -- STATISTIC_NAME VARCHAR2(64)
s.fts_statid,            -- STATISTIC# NUMBER
s.fts_staval             -- VALUE NUMBER
from
obj$ o,
user$ u,
x$ksolsfts s,
ts$ ts 
where
o.owner# = u.user# and s.fts_inte = 0 and s.fts_objn = o.obj# and s.fts_tsn = ts.ts# and s.fts_objd = o.dataobj# and o.linkname is null and (o.type# not in (1 /* INDEX - handled below */ 
,
10 /* NON-EXISTENT */ 
) or (o.type# = 1 and 1 = (
select
1 
from
ind$ i 
where
i.obj# = o.obj# and i.type# in (1,
2,
3,
4,
6,
7,
9)))) and o.name != '_NEXT_OBJECT' and o.name != '_default_auditing_options_' 
union all 
select
s.inst_id,               -- INST_ID NUMBER
u.name,                  -- OWNER VARCHAR2(30)
o.name,                  -- OBJECT_NAME VARCHAR2(30)
o.subname,               -- SUBOBJECT_NAME VARCHAR2(30)
ts.name,                 -- TABLESPACE_NAME VARCHAR2(30)
s.fts_tsn,               -- TS# NUMBER
t.ktssoobjn,             -- OBJ# NUMBER
t.ktssoobjd,             -- DATAOBJ# NUMBER
decode                   -- OBJECT_TYPE VARCHAR2(18)
  (o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY', 19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB', 22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE', 28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE', 32, 'INDEXTYPE', 33, 'OPERATOR', 34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION', 40, 'LOB PARTITION', 41, 'LOB SUBPARTITION', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 44, 'CONTEXT', 47, 'RESOURCE PLAN', 48, 'CONSUMER GROUP', 51, 'SUBSCRIPTION', 52, 'LOCATION', 55, 'XML SCHEMA', 56, 'JAVA DATA', 57, 'SECURITY PROFILE', 'UNDEFINED'),
s.fts_statnam,           -- STATISTIC_NAME VARCHAR2(64)
s.fts_statid,            -- STATISTIC# NUMBER
s.fts_staval             -- VALUE NUMBER
from
obj$ o,
user$ u,
x$ksolsfts s,
x$ktsso t,
ts$ ts 
where
o.owner# = u.user# and s.fts_inte = 0 and s.fts_objn = o.obj# and s.fts_tsn = t.ktssotsnum and s.fts_objn = t.ktssoobjn and s.fts_objd = t.ktssoobjd and s.fts_tsn = ts.ts# and t.ktssotsn = ts.name and o.linkname is null and (o.type# not in (1 /* INDEX - handled below */ 
,
10 /* NON-EXISTENT */ 
) or (o.type# = 1 and 1 = (
select
1 
from
ind$ i 
where
i.obj# = o.obj# and i.type# in (1,
2,
3,
4,
6,
7,
9)))) and o.name != '_NEXT_OBJECT' and o.name != '_default_auditing_options_'
home |  up  |  mailbox