Siebel SQLs/Error Messages >  How to calculate space usage stats in Siebel with Oracle

How to calculate space usage stats in Siebel with Oracle

This can be used to check slow performance in Siebel. To run this query, you need DB Administrator privileges.

 

SELECT *
FROM (SELECT owner,
object_name,
object_type,
table_name,
ROUND(bytes) / 1024 / 1024 AS ALLOCATED_MB,
ROUND(space_used) / 1024 / 1024 AS USED_MB,
tablespace_name,
extents,
initial_extent,
ROUND(Sum(bytes / 1024 / 1024)
OVER(PARTITION BY owner, table_name)) AS TOTAL_ALLOCATED_MB,
ROUND(Sum(space_used / 1024 / 1024)
OVER(PARTITION BY owner, table_name)) AS TOTAL_USED_MB
FROM (
-- Tables
SELECT owner,
segment_name AS object_name,
'TABLE' AS object_type,
segment_name AS table_name,
bytes,
tablespace_name,
extents,
initial_extent,
space_used
FROM dba_segments,
table(dbms_space.object_space_usage_tbf(owner,
segment_name,
segment_type,
null,
partition_name))
WHERE segment_type IN
('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
and owner not in ('SYS', 'SYSTEM', 'SYSMAN')
and bytes > 10 * 1024 * 1024
UNION ALL
-- Indexes
SELECT i.owner,
i.index_name AS object_name,
'INDEX' AS object_type,
i.table_name,
s.bytes,
s.tablespace_name,
s.extents,
s.initial_extent,
u.space_used
FROM dba_indexes i,
dba_segments s,
table(dbms_space.object_space_usage_tbf(s.owner,
s.segment_name,
s.segment_type,
null,
s.partition_name)) u
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type IN
('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
and s.owner not in ('SYS', 'SYSTEM', 'SYSMAN')
and s.bytes > 10 * 1024 * 1024
-- LOB Segments
UNION ALL
SELECT l.owner,
l.column_name AS object_name,
'LOB_COLUMN' AS object_type,
l.table_name,
s.bytes,
s.tablespace_name,
s.extents,
s.initial_extent,
u.space_used
FROM dba_lobs l,
dba_segments s,
table(dbms_space.object_space_usage_tbf(s.owner,
s.segment_name,
'LOB',
null)) u
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
and s.owner not in ('SYS', 'SYSTEM', 'SYSMAN')
and s.bytes > 10 * 1024 * 1024
-- LOB Indexes
UNION ALL
SELECT l.owner,
l.column_name AS object_name,
'LOB_INDEX' AS object_type,
l.table_name,
s.bytes,
s.tablespace_name,
s.extents,
s.initial_extent,
u.space_used
FROM dba_lobs l,
dba_segments s,
table(dbms_space.object_space_usage_tbf(s.owner,
s.segment_name,
'INDEX',
null)) u
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
and s.owner not in ('SYS', 'SYSTEM', 'SYSMAN')
and s.bytes > 10 * 1024 * 1024))
WHERE TOTAL_ALLOCATED_MB > 10
ORDER BY TOTAL_USED_MB DESC, owner ASC, table_name ASC, USED_MB DESC;