Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Cleanup Temporary segments in a tablespace

835815Sep 15 2011 — edited Sep 15 2011

hi,

I have to drop few tablespace and before that i have to make sure no objects are there. Below some of the tablespace have TEMPORARY segments. Can any1 tell me how to clean them up

SQL> select tablespace_name,segment_type,count(segment_name) from dba_segments
group by tablespace_name, segment_type having tablespace_name  2
  3  IN ('T_LLFPP_2006_I',
  4  'T_NAFPP_2004_D',
  5  'T_NAFPP_2008_D',
'T_GPLP_2006_I',
  6    7  'T_NAFPP_2005_D',
  8  'T_NAFPP_2008_I',
  9  'T_GPLP_2008_I',
 10  'T_GPLP_2007_I',
 11  'T_NAFPP_2006_D',
 12  'T_NAFPP_2007_D',
 13  'T_NAFPP_2007_I',
 14  'T_NAFPP_2005_I',
 15  'T_NAFPP_2006_I',
 16  'T_NAFPP_2004_I' );

TABLESPACE_NAME                SEGMENT_TYPE       COUNT(SEGMENT_NAME)
------------------------------ ------------------ -------------------
T_GPLP_2007_I                  TEMPORARY                           24
T_NAFPP_2005_I                 TEMPORARY                           24
T_NAFPP_2006_I                 TEMPORARY                           24
T_NAFPP_2006_D                 TEMPORARY                           24
T_NAFPP_2007_D                 TEMPORARY                           25
T_NAFPP_2008_I                 TEMPORARY                           16
T_NAFPP_2008_D                 TEMPORARY                           20
T_NAFPP_2005_D                 TEMPORARY                           24
T_NAFPP_2007_I                 TEMPORARY                           23
T_NAFPP_2004_D                 TEMPORARY                           12
T_LLFPP_2006_I                 TEMPORARY                           24

TABLESPACE_NAME                SEGMENT_TYPE       COUNT(SEGMENT_NAME)
------------------------------ ------------------ -------------------
T_GPLP_2008_I                  TEMPORARY                           12
T_GPLP_2006_I                  TEMPORARY                           24
T_NAFPP_2004_I                 TEMPORARY                           12

14 rows selected.

Thanks in advance

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 13 2011

Added on Sep 15 2011

#general-database-discussions

2 comments

1,577 views