Troubleshooting sudden tablespace growth

I started noticing that my application is inserting very high volume of data compared with previous day when we started researching why we are adding more data files to a particular tablespace.

Here is how I did the troubleshooting. First Find biggest segments consuming this TABLESPACE

SQL> col segment_name format A35

SQL> col partition_name format A35

SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/(1024*1024) M_bytes from dba_Segments where TABLESPACE_NAME= 'ELF_TIBCO_DATA' order by 3;

OWNER                          SEGMENT_NAME                        PARTITION_NAME                      SEGMENT_TYPE          M_BYTES

------------------------------ ----------------------------------- ----------------------------------- ------------------ ----------

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48333                   LOB SUBPARTITION         1099

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48334                   LOB SUBPARTITION         1155

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48335                   LOB SUBPARTITION         1151

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48336                   LOB SUBPARTITION         1117

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48337                   LOB SUBPARTITION         1159

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48338                   LOB SUBPARTITION         1045

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48339                   LOB SUBPARTITION         1156

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48392                   LOB SUBPARTITION         1199

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48393                   LOB SUBPARTITION         1176

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48394                   LOB SUBPARTITION         1143

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48395                   LOB SUBPARTITION         1227

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48396                   LOB SUBPARTITION         1177

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48397                   LOB SUBPARTITION         1169

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48398                   LOB SUBPARTITION         1173

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48399                   LOB SUBPARTITION         1203

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48452                   LOB SUBPARTITION        22516

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48453                   LOB SUBPARTITION        22617

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48454                   LOB SUBPARTITION        22130

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48455                   LOB SUBPARTITION        22297

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48456                   LOB SUBPARTITION        22632

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48457                   LOB SUBPARTITION        22397

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48458                   LOB SUBPARTITION        22341

ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48459                   LOB SUBPARTITION        22341

We can easily correlate SEGMENT_NAME to TABLE_NAME if SEGMENT_TYPE is TABLE and to partition if it has partitions. But if SEGMENT_TYPE is LOB_PARTITION we need to find the table and partition associated with this lob segment.

To do that, try

SQL> col column_name format A30

SQL> select owner,TABLE_NAME , COLUMN_NAME ,SEGMENT_NAME,TABLESPACE_NAME  from dba_lobs where segment_name='SYS_LOB0000101461C00004$$';

OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                        TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------ ----------------------------------- ------------------------------

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$           ELF_TIBCO_DATA

SQL> select TABLE_OWNER,TABLE_NAME ,COLUMN_NAME,LOB_NAME, PARTITION_NAME, LOB_PARTITION_NAME,TABLESPACE_NAME from dba_lob_partitions where table_name='TIBCO_EVENT_LOG_ATTACHMENTS';

TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                    LOB_NAME                       PARTITION_NAME                      LOB_PARTITION_NAME             TABLESPACE_NAME

------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------- ------------------------------ ------------------------------

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131017            SYS_LOB_P48091

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131018            SYS_LOB_P48151

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131019            SYS_LOB_P48211

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131020            SYS_LOB_P48271

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131021            SYS_LOB_P48331

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131022            SYS_LOB_P48391

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131023            SYS_LOB_P48451

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131024            SYS_LOB_P48511

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131025            SYS_LOB_P48571

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131026            SYS_LOB_P48631

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131027            SYS_LOB_P48691

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131028            SYS_LOB_P48751

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131029            SYS_LOB_P48811

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131030            SYS_LOB_P48871

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131031            SYS_LOB_P48931

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131101            SYS_LOB_P48991

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131102            SYS_LOB_P49051

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131103            SYS_LOB_P49111

Now we know that it is related to ELF_ADMIN.TIBCO_EVENT_LOG_ATTACHMENTS and is having big LOB attachments associated with ATTACHMENT_DATA column. As we started seeing this issue on 2013/10/23, we can

Check the details of SYS_LOB_P48451

SQL> select TABLE_OWNER,TABLE_NAME , COLUMN_NAME,LOB_NAME  , LOB_PARTITION_NAME,SUBPARTITION_NAME, LOB_SUBPARTITION_NAME from dba_lob_subpartitions where  TABLE_NAME ='TIBCO_EVENT_LOG_ATTACHMENTS' and LOB_PARTITION_NAME='SYS_LOB_P48451';

TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                    LOB_NAME                       LOB_PARTITION_NAME             SUBPARTITION_NAME              LOB_SUBPARTITION_NAME

------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB1  SYS_LOB_SUBP48452

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB2  SYS_LOB_SUBP48453

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB3  SYS_LOB_SUBP48454

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB4  SYS_LOB_SUBP48455

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB5  SYS_LOB_SUBP48456

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB6  SYS_LOB_SUBP48457

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB7  SYS_LOB_SUBP48458

ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB8  SYS_LOB_SUBP48459

It looks like there are 8 sub partitions associated with this LOB SEGMENT and compared with previous day we are inserting 22 times more attachments (error messages).

Now I approached app support and started investigating into the contents of this table.