Dropping data files in a temprorary tablespace

[torturedmind]

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
philippines

"Once a king, always a king. But being a knight is more than enough."

 

[LKBrwnDBA]

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.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[torturedmind]

Thank you very much, good sir, for your reply. I am pretty sure it is not the default temporary tablespace.

Remember also to change it for the users that have this temp tablespace as default.

Good advice.

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
philippines

"Once a king, always a king. But being a knight is more than enough."