1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
SELECT 'ROLLBACK SEGMENT' segment_name , u.name, NULL, 'HARD', 'STORAGE', NULL
FROM SYS.UNDO$ u, sys.ts$ ts WHERE ts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and u.ts#=ts.ts#
and u.status$ != 1 and substr(u.name,1,7) != '_SYSSMU' UNION SELECT 'USER',
u.name, NULL, 'SOFT', 'DEF_TABLESPACE', NULL FROM sys.user$ u, sys.ts$ dts
WHERE dts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and u.datats# = dts.ts#
and u.tempts# != u.datats# and u.type# = 1 UNION SELECT 'USER',
u.name, NULL, 'SOFT', 'TEMP_TABLESPACE', NULL FROM sys.user$ u, sys.ts$ tts
WHERE tts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and u.tempts# = tts.ts# and u.tempts# != u.datats# and u.type# = 1
UNION
SELECT 'USER', u.name, NULL, 'SOFT', 'DEF_AND_TEMP_TABLESPACE', NULL
FROM sys.user$ u, sys.ts$ tts WHERE tts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and u.tempts# = tts.ts# and u.tempts# = u.datats# and u.type# = 1
UNION
SELECT 'USER', username, NULL, 'SOFT', 'TABLESPACE_QUOTA', NULL
FROM SYS.DBA_TS_QUOTAS WHERE tablespace_name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО'
UNION
SELECT 'CLUSTER', o.name, u.name, 'HARD', 'STORAGE', NULL
FROM sys.ts$ ts, sys.clu$ clu, sys.obj$ o, sys.user$ u
WHERE ts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and clu.ts# = ts.ts# and o.obj# = clu.obj# and u.user# = o.owner#
UNION
SELECT 'TABLE',DECODE(t.iot_type,'IOT_OVERFLOW', t.iot_name, p.table_name),DECODE(t.iot_type,'IOT_OVERFLOW', t.owner,p.table_owner),
'HARD', DECODE(t.iot_type, 'IOT_OVERFLOW','IOT_OVERFLOW_STORAGE','PARTITION_STORAGE'), partition_name
FROM sys.dba_tab_partitions p, sys.dba_tables t
WHERE p.tablespace_name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО'
AND p.table_name = t.table_name AND p.table_owner = t.owner
UNION
SELECT 'INDEX', decode(i.index_type,'IOT-TOP',i.table_name,p.index_name), decode(i.index_type,'IOT_TOP',i.table_owner,p.index_owner),
'HARD', 'PARTITION_STORAGE', PARTITION_NAME FROM SYS.DBA_IND_PARTITIONS p, SYS.DBA_INDEXES i
WHERE p.tablespace_name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО'
AND p.index_name = i.index_name AND p.index_owner = i.owner AND i.index_type != 'LOB'
UNION
SELECT 'TABLE', pt.table_name, pt.owner, 'HARD', 'DEF_TABLESPACE', NULL
FROM SYS.DBA_PART_TABLES pt, SYS.DBA_TABLES t
WHERE pt.def_tablespace_name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО'
AND pt.table_name = t.table_name AND pt.owner = t.owner
AND (t.iot_type != 'IOT_OVERFLOW' OR t.iot_type IS NULL) UNION SELECT 'INDEX', i.index_name, i.owner,
'HARD', 'DEF_TABLESPACE', NULL FROM SYS.DBA_PART_INDEXES pi, SYS.DBA_INDEXES i
WHERE i.partitioned = 'YES' AND i.index_type != 'LOB' AND i.index_name = pi.index_name AND i.owner = pi.owner
AND pi.def_tablespace_name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО'
UNION
SELECT 'TABLE', table_name, table_owner, 'HARD', 'PARTITION_STORAGE', PARTITION_NAME
FROM SYS.DBA_TAB_SUBPARTITIONS WHERE tablespace_name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО'
UNION
SELECT DECODE( i.type#, 8, 'TABLE', 4, 'TABLE', 'INDEX'), DECODE( i.type#, 8, io.name, 4, io.name, o.name),
DECODE( i.type#, 8, iu.name,4, iu.name, u.name), 'HARD', DECODE( i.type#, 8, 'LOB_INDEX_STORAGE', 'STORAGE'), NULL
FROM sys.ts$ ts, sys.ind$ i, sys.user$ u, sys.user$ iu, sys.obj$ o, sys.obj$ io
WHERE ts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and i.ts# = ts.ts# and bitand(i.flags, 4096) = 0 and bitand(i.property, 34) = 0
and i.type# != 9 and u.user# = o.owner# and o.obj# = i.obj# and i.bo# = io.obj# and io.owner# = iu.user#
UNION
select 'TABLE',o.name, u.name, 'HARD','LOB_STORAGE',decode(bitand(c.property, 1), 1, ac.name, c.name)
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac, sys.tab$ ta, sys.lob$ l, sys.obj$ lo, sys.obj$ io, sys.user$ u, sys.ts$ ts
where ts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and o.owner# = u.user# and o.obj# = c.obj# and c.obj# = l.obj# and c.intcol# = l.intcol#
and l.lobj# = lo.obj# and l.ind# = io.obj# and l.ts# = ts.ts# and c.obj# = ac.obj#(+)
and c.intcol# = ac.intcol#(+) and bitand(c.property,32768) != 32768
/* not unused column */ and o.obj# = ta.obj# and bitand(ta.property, 32) != 32
/* not partitioned table */
union all
select 'TABLE',o.name, u.name, 'HARD','LOB_STORAGE',decode(bitand(c.property, 1), 1, ac.name, c.name)
from sys.obj$ o, sys.col$ c, sys.attrcol$ ac, sys.partlob$ plob, sys.lob$ l, sys.obj$ lo, sys.obj$ io, sys.ts$ ts1, sys.tab$ ta,
sys.partobj$ po, sys.ts$ ts2, sys.user$ u where ts1.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and o.owner# = u.user# and o.obj# = c.obj#
and c.obj# = l.obj# and c.intcol# = l.intcol# and l.lobj# = lo.obj# and l.ind# = io.obj# and l.lobj# = plob.lobj# and plob.defts# = ts1.ts# (+)
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+) and bitand(c.property,32768) != 32768
/* not unused column */ and o.obj# = ta.obj# and bitand(ta.property, 32) = 32
/* partitioned table */ and o.obj# = po.obj# and po.defts# = ts2.ts#
UNION
select 'TABLE', decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),0, o.name, co.name),
u.name, 'HARD', decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),0, 'STORAGE','IOT_OVERFLOW_STORAGE'), NULL
FROM sys.ts$ ts, sys.tab$ t, sys.seg$ s ,sys.user$ u, sys.obj$ co, sys.obj$ o,
sys.obj$ cx, sys.user$ cu where ts.name = 'ТАБЛИЧНОЕ ПРОСТРАНСТВО' and t.ts# = ts.ts# and bitand(t.property, 4194400) = 0
and bitand(t.property, 1) = 0 and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and o.owner# = u.user#
and o.obj# = t.obj# and t.bobj# = co.obj# (+) and t.dataobj# = cx.obj# (+) and cx.owner# = cu.user# (+) and cx.owner# = cu.user# (+)
