[Space]: Global size of a table including its lobs and indexes

HIDDEN CONTENT

set pages 100 lines 200 feed off
col segment_name for a30
col size_M for a10
set verify off

define own=AS2CLIENT
define tab=SURVEY

prompt DETAILS
prompt =======
select table_owner, table_name, segment_name, segment_type , ceil(bytes/1024/1024)||'M' size_M  FROM
(
select i.table_owner, i.table_name, s.segment_name, s.segment_type, s.bytes
from dba_segments s, dba_indexes i
where
    s.segment_name =i.index_name
and s.owner = i.owner
and i.index_type !='LOB'
UNION
select t.owner  table_owner, t.table_name ,  s.segment_name, s.segment_type, s.bytes
from dba_segments s, dba_tables t
where
    t.table_name = s.segment_name
and t.owner = s.owner
UNION
select l.owner table_owner,  l.table_name,  s.segment_name, s.segment_type, s.bytes
from dba_lobs l, dba_segments s
where
        l.segment_name = s.segment_name
and l.owner =s.owner
)
where
table_owner='&own'
and table_name='&tab'
order by segment_type,segment_name
;
prompt TOTAL
prompt =====

select table_owner, table_name, ceil(sum(bytes)/1024/1024)||'M' size_M  FROM
(
select i.table_owner, i.table_name, s.segment_name, s.segment_type, s.bytes
from dba_segments s, dba_indexes i
where
    s.segment_name =i.index_name
and s.owner = i.owner
and i.index_type !='LOB'
UNION
select t.owner  table_owner, t.table_name ,  s.segment_name, s.segment_type, s.bytes
from dba_segments s, dba_tables t
where
    t.table_name = s.segment_name
and t.owner = s.owner
UNION
select l.owner table_owner,  l.table_name,  s.segment_name, s.segment_type, s.bytes
from dba_lobs l, dba_segments s
where
        l.segment_name = s.segment_name
and l.owner =s.owner
)
where
table_owner='&own'
and table_name='&tab'
group by table_owner, table_name
;

TABLE_OWNER     TABLE_NAME      SEGMENT_NAME                   SEGMENT_TYPE       SIZE_M
--------------- --------------- ------------------------------ ------------------ ----------
AS2CLIENT       SURVEY          IND_SURVEY_IDX1                INDEX              1M
AS2CLIENT       SURVEY          IND_SURVEY_IDX2                INDEX              1M
AS2CLIENT       SURVEY          IND_SURVEY_IDX_04              INDEX              50M
AS2CLIENT       SURVEY          IND_SURVEY_IDX_05              INDEX              5M
AS2CLIENT       SURVEY          IND_SURVEY_PRJ_PARTNER_PARAM   INDEX              63M
AS2CLIENT       SURVEY          PART_ID_IDX                    INDEX              25M
AS2CLIENT       SURVEY          PROJECT_UP_PA_IDX              INDEX              10M
AS2CLIENT       SURVEY          QUESTI_ID_IDX                  INDEX              56M
AS2CLIENT       SURVEY          SURVEYOUTQUOTAINDEX            INDEX              127M
AS2CLIENT       SURVEY          SURVEYPANELISTINDEX            INDEX              112M
AS2CLIENT       SURVEY          SURVEYPROJECTPANELISTINDEX     INDEX              112M
AS2CLIENT       SURVEY          SURVEYREDIRECTINDEX            INDEX              136M
AS2CLIENT       SURVEY          SYS_C00200388                  INDEX              48M
AS2CLIENT       SURVEY          SYS_LOB0000174058C00010$$      LOBSEGMENT         1M
AS2CLIENT       SURVEY          SYS_LOB0000174058C00011$$      LOBSEGMENT         814M
AS2CLIENT       SURVEY          SURVEY                         TABLE              2170M
TOTAL
=====

TABLE_OWNER     TABLE_NAME      SIZE_M
--------------- --------------- ----------
AS2CLIENT       SURVEY          3729M

All the tables

col tab for a40
set pages 100 lines 200 feed off
col segment_name for a30
col size_M for 999999999
col table_name for a40
col table_owner for a15
set verify off

define seuil_size_M=10000
prompt Tables occupying above &seuil_size_M M, with their indexes and lobs
prompt ======================================================================

select taball.table_owner, taball.table_name, ceil(sum(bytes)/1024/1024) size_M  FROM
(
select i.table_owner, i.table_name, s.segment_name, s.segment_type, s.bytes
from dba_segments s, dba_indexes i
where
    s.segment_name =i.index_name
and s.owner = i.owner
and i.index_type !='LOB'
UNION
select t.owner  table_owner, t.table_name ,  s.segment_name, s.segment_type, s.bytes
from dba_segments s, dba_tables t
where
    t.table_name = s.segment_name
and t.owner = s.owner
UNION
select l.owner table_owner,  l.table_name,  s.segment_name, s.segment_type, s.bytes
from dba_lobs l, dba_segments s
where
        l.segment_name = s.segment_name
and l.owner =s.owner
) taball, dba_tables tab
where
taball.table_name = tab.table_name
and taball.table_owner = tab.owner
group by taball.table_owner, taball.table_name
having ceil(sum(bytes)/1024/1024) >= &seuil_size_M
order by 3
;

Tables occupying above 10000 M, with their indexes and lobs
======================================================================

TABLE_OWNER     TABLE_NAME                                   SIZE_M
--------------- ---------------------------------------- ----------
KORNEV5         POSTCODELOOKUP                                13205
AS2CLIENT       ANSWERSNOTSHOWNINSURVEY                       13897
AUTOPROD        SURVEY                                        16954
AUTOPROD        SAMPLE_IMPORT_EXPORT                          16968
AUTOPROD        ATTR_STRING_VALUE                             17844
KORNEV5         T_REPLIC_REPONSE_FLAG_V2                      18322
KORNEV5         T_POINTS                                      22366
AUTOPROD        ATTR_LIST_VALUE                               73660
KORNEV5         T_REPONSE_OP_V2                               85920
AUTOPROD        RECIP                                        104573
AUTOPROD        UPLOADGROUPPANELIST                          106621
AUTOPROD        EMAILMESSAGE                                 155017