AskDba.org Weblog

There are situations when we see “temporary segments” in permanent tablespaces hanging around and not getting cleaned up. These temporary segments in permanent tablespace can be created by DDL operations like CTAS and “alter index..rebuild” because the new object is created as a temporary segment in the target tablespace and when the DDL action finishes it will be changed to permanent type. These temporary segments take actual disk space when SMON fails to perform its assigned job to cleanup stray temporary segments. Following query finds out these segments: SQL > select tablespace_name, owner, sum(bytes/1024/1024) from dba_segments where segment_type = 'TEMPORARY' group by tablespace_name, owner; TABLESPACE_NAME     OWNER          SEGMENT_NAME     SUM(BYTES/1024/1024) ------------------- ------------   ------------     -------------------- xxxx_DATA           SYS              123.8365          137706 BDEPST_INDEX        SYS              345.8756            8910 KMRPT_DATA          SYS                345.87       25284.875 BILL_INDEX          SYS                                   .25 DSS_DATA            SYS                                   798 MRKT_INDEX          SYS                                   208 SPCT_DATA           SYS                              69642.25 SPCT_INDEX          SYS                              956.4375 Here we can see that tablespace KMRPT_DATA, … Read entire article »