Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Clean Temporary Segments

Posted on 2014-10-24
9
Medium Priority
?
215 Views
Last Modified: 2014-11-25
I can see several temporary segments eating about 485 MB in one of the schemas in my database:

SEGMENT_NAME				 TABLESPACE_NAME		SEGMENT_TYPE		   MB
---------------------------------------- ------------------------------ ------------------ ----------
4.194730				 USERS				TEMPORARY		.0625
4.194722				 USERS				TEMPORARY		.0625
4.194634				 USERS				TEMPORARY		.0625
4.194626				 USERS				TEMPORARY		.0625
4.194554				 USERS				TEMPORARY		.0625
4.194474				 USERS				TEMPORARY		.0625
4.194450				 USERS				TEMPORARY		.0625
4.194442				 USERS				TEMPORARY		.0625
6.370					 USERS				TEMPORARY		  104
6.354					 USERS				TEMPORARY		   13
4.30594 				 USERS				TEMPORARY		  104
4.122354				 USERS				TEMPORARY		   13
4.58578 				 USERS				TEMPORARY		    2
4.10322 				 USERS				TEMPORARY		   43
4.78234 				 USERS				TEMPORARY		   13
6.34810 				 USERS				TEMPORARY		   28
6.71458 				 USERS				TEMPORARY		  112
4.181154				 USERS				TEMPORARY		   53

18 rows selected.

Open in new window


I bounced the database but they are still there. Can you please help me clean them?
0
Comment
Question by:sysautomation
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 40403864
Hi!

Cleaning up temporary segments is done automatically by SMON for DMT.
However, you can do that manually, by
alter tablespace USERS coalesce;

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_3002.htm

Regards,
    Tomas Helgi
0
 

Author Comment

by:sysautomation
ID: 40403990
I have already tried coalesce but no change.
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40404310
I think the link below covers the issue quite nicely, with various other options to remove the temporary segments.

http://askdba.org/weblog/2009/07/cleanup-temporary-segments-in-permanent-tablespace/
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:sysautomation
ID: 40427822
Sorry I could not see it timely. But none of the solutions given worked for me.

SQL> select ts# from sys.ts$ where name = 'USERS';

       TS#
----------
       4

SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 5';

Session altered.

But still I have temporary segments as before:

SQL>  select tablespace_name, sum(bytes/1024/1024) from dba_segments
  2* where segment_type = 'TEMPORARY' group by tablespace_name

SQL> /

TABLESPACE_NAME              SUM(BYTES/1024/1024)
------------------------------ --------------------
USERS                                 488.5625


Second method is throwing some error:

SQL> exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('USERS');
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('USERS'); END;

*
ERROR at line 1:
ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 83
ORA-06512: at line 1
0
 
LVL 5

Expert Comment

by:Sanjeev Labh
ID: 40429287
You have not posted as to what type of operation was in progress. Did you try to find and see whether it was a DDL. Because for that you will not be able to drop till the time the operation finishes.
0
 

Author Comment

by:sysautomation
ID: 40429789
I am not aware of any operation going on. It has been about a month old problem and hence even if a DDL was run it should have finished by now. Also the DB was bounced a few times therefore any operation would have been killed before completion.
0
 
LVL 62

Accepted Solution

by:
gheist earned 1500 total points
ID: 40465658
They are kept around for reuse. If you dump and reload data sure they disappear for short time DB spins up.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40465759
They probably should not have survived a database bounce.

There are many posts out there that talk about the tablespace being read only but I cannot imagine USERS being set to read only.  If it is, set it to read write and try the coalsece again.

If that isn't the case try removing ALL temp segments:
alter session set events 'immediate trace name DROP_SEGMENTS level 2147483647';

If they remain, I would probably open up an SR with Oracle Support.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question