Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Dropping data files in a temprorary tablespace

Status
Not open for further replies.

torturedmind

Programmer
Jan 31, 2002
1,052
PH
Hi,

We had a very large amount of data that were transferred from a simple Novell file server to Oracle DB. In the Oracle side, we have a permanent tablespace and a temporary tablespace. This temporary tablespace is NOT the default TS. In it, there are 2 data files called CIM_TMP1.DBF and CIM_TMP2.DBF each having a size of 512MB. During the transfer, one of these data files (CIM_TMP2.DBF) bloated to a size of +33GB because it was set to autoincrement. But viewing the current info of the said data file in the Oracle OEM, only 4MB is being used. Now, I want to reclaim the unused space by resizing it back to 512MB but Oracle won't let me saying that the target size is much smaller compared to its current content or something like that. I read somewhere that, since it is a temporary tablespace, I may just drop the temporary data file (NOT the entire tablespace) using ALTER TABLESPACE TEMPFILE...DROP INCLUDING DATAFILES command.

My question is: How much risk is involved in this procedure? I just want to have other DBA or expert knowledge. TIA

kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
torturedmind said:
How much risk is involved in this procedure?
None, just make sure it is not the "default" temporary tablespace.
Remember also to change it for the users that have this temp tablespace as default.
[3eyes]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Thank you very much, good sir, for your reply. I am pretty sure it is not the default temporary tablespace.

LKBrwnDBA said:
Remember also to change it for the users that have this temp tablespace as default.
Good advice. [thumbsup2]

What we finally decided to do is to create a new temporary tablespace with the proper (and of course more reasonable) size and point the users to that new temporary tablespace. Then drop the old temporary tablepsace once the users have finally switched to the new one. I would like to reiterate that what we are dropping is NOT THE DEFAULT temporary tablespace. It's just a temporary tablespace for a particular schema that was wrongly defined. Anyways, this is where we got the idea.

Thanks again Sir LKBrwnDBA.

kilroy [knight]
philippines

"Once a king, always a king. But being a knight is more than enough."
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top