INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dropping data files in a temprorary tablespace

Dropping data files in a temprorary tablespace

(OP)
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."

RE: Dropping data files in a temprorary tablespace

Quote (torturedmind )

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

RE: Dropping data files in a temprorary tablespace

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

Quote (LKBrwnDBA)

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."

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close