11.2.0.3/Solaris
When u run a query like below which lists the top space consuming segments , It will only list segments for LOBs and Partitions of a table separately..
select segment_name, bytes/1024/1024/1024 gb, segment_type from dba_segments where owner = 'MCS_WM_USR' order by gb desc;
SEGMENT_NAME GB SEGMENT_TYPE
-------------------------------------------------------------------------------- ---------- ------------------
WMERROR 14.4287109 TABLE
SYS_LOB0000737548C00014$$ 9.93554687 LOBSEGMENT
WMSERVICE 6.00488281 TABLE
WMSESSION 5.11621093 TABLE
SYS_LOB0000737571C00017$$ 4.61914062 LOBSEGMENT
WMCUSTOMPROCESSDATA 2.8046875 TABLE
SYS_C00511081 1.25097656 INDEX
IDX_SVC_COM1 0.95501708 INDEX
IDX_SESS_AUDTM 0.92291259 INDEX
.
.
.
Does anyone have a query which will list the actual table sizes which is calculated after summing up all LOBs and Partitions in that table ?
For example;
If table EMP has 3 LOB columns, in user_segments , it will appear something like
SEGMENT_NAME GB SEGMENT_TYPE
-------------------------------------------------------------------------------- ---------- ------------------
EMP 3 TABLE
SYS_LOB0000737548C00014$$ 7 LOBSEGMENT
SYS_LOB0000451978C00014$$ 2 LOBSEGMENT
SYS_LOB0000875128C00014$$ 5 LOBSEGMENT
The total size of EMP table is 3 + 7 + 2 + 5 = 17 GB
I am looking for query which will do a lookup in DBA_LOBS and DBA_PARTITIONS and add up space for each table.
So, my required output will looks like
TABLE_NAME Size
------------- -------
EMP 17g
DEPT 1g
WMERRORS 104g
.
.