Home » RDBMS Server » Server Administration » TEMP_TSXX.dbf files (I have quite a few of these files and they are taking up large amounts of disk.)
TEMP_TSXX.dbf files [message #400966] Thu, 30 April 2009 13:11 Go to next message
nateholtrop
Messages: 37
Registered: May 2008
Member
I have quite a few of these files and they are taking up large amounts of disk. Is it safe to remove them? How do I go about removing them safely? Thanks

-Nate
Re: TEMP_TSXX.dbf files [message #400968 is a reply to message #400966] Thu, 30 April 2009 13:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> Is it safe to remove them?
Depends on what it is holding.
Do you know what kind of tablespace are they?
If it is Oracle temporary tablespaces, you can remove them and consolidate into one.
Exact method depends on your version.
Re: TEMP_TSXX.dbf files [message #400971 is a reply to message #400968] Thu, 30 April 2009 13:36 Go to previous messageGo to next message
nateholtrop
Messages: 37
Registered: May 2008
Member
They are all being held being the TEMP tablespace. Can you show me how you would do consolidate them all into one? Version 9.2.0.8

[Updated on: Thu, 30 April 2009 13:38]

Report message to a moderator

Re: TEMP_TSXX.dbf files [message #400972 is a reply to message #400971] Thu, 30 April 2009 13:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.orafaq.com/node/2

In 9i and above, you can just assign a default temporary tablespace.
You create a new one, make it default and drop all the rest.
I would do some research on the temp tablespace utilization to derive an approximate size for the new one.
Re: TEMP_TSXX.dbf files [message #400973 is a reply to message #400966] Thu, 30 April 2009 14:12 Go to previous messageGo to next message
nateholtrop
Messages: 37
Registered: May 2008
Member
Is it safe to just remove some of the temp_tsxx.dbf files? Typically when I restore a database I blow away all of the temp files and recreate them. Would that be appropriate?

edit- all of the temp files are empty.

[Updated on: Thu, 30 April 2009 14:14]

Report message to a moderator

Re: TEMP_TSXX.dbf files [message #400974 is a reply to message #400973] Thu, 30 April 2009 14:17 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10671
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator

I would drop them from the database using

Drop tablespace tmp1 including contents and datafiles;


But first I would create a default temporary tablespace.
Re: TEMP_TSXX.dbf files [message #401131 is a reply to message #400966] Fri, 01 May 2009 14:11 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
Query dba_tablespaces to see how many temporary tablespaces you have . query dba_users to see what temporary tablespaces are assigned to users. Let make one as your default tablespace,assign users to it and you may drop other .

[Updated on: Fri, 01 May 2009 14:12]

Report message to a moderator

Re: TEMP_TSXX.dbf files [message #401136 is a reply to message #400966] Fri, 01 May 2009 14:57 Go to previous messageGo to next message
nateholtrop
Messages: 37
Registered: May 2008
Member
Plan of action

*create new default temp tablespace based off old version. - Done through OEM.

*blow away old and old files - done through console

*leave new temp tablespace place

I can do most of this with OEM and the CLI of my HPUX server.

Comments?
Re: TEMP_TSXX.dbf files [message #401193 is a reply to message #400966] Sat, 02 May 2009 03:51 Go to previous messageGo to next message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
i would prefer to do everything in command line.you may do few test in your local database before you proceed. here are the sqls for you.
1.SELECT TABLESPACE_NAME FROM DBA_TABLESPACES
WHERE CONTENTS='TEMPORARY';

2.SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

3.select username,temporary_tablespace from dba_users;

4.ALTER DATABASE DEFAULT TEMPORARY TABLESPACE <<your temporary tablespace>>

5.select username,temporary_tablespace from dba_users; -- to make sure all users are assigned default temp tablespace.

6.drop other temporary tablespaces listed in step 2.

DROP TABLESPACE <<tablespacename>>
   INCLUDING CONTENTS AND DATAFILES;

Re: TEMP_TSXX.dbf files [message #401381 is a reply to message #400966] Mon, 04 May 2009 07:04 Go to previous messageGo to next message
nateholtrop
Messages: 37
Registered: May 2008
Member
Thank you for the commandline.
Re: TEMP_TSXX.dbf files [message #426459 is a reply to message #400966] Thu, 15 October 2009 09:14 Go to previous messageGo to next message
nateholtrop
Messages: 37
Registered: May 2008
Member
Just to update this post for anyone searching:

alter tablespace TEMP drop tempfile '/database/oradata/cm/temp_ts02.dbf';
-Specify EXACT location of tempfile to drop

This will drop the tempfile from the tablespace and give you some space back. I do this whenever I need some space back on my drives for whatever reason.
Re: TEMP_TSXX.dbf files [message #426491 is a reply to message #400966] Thu, 15 October 2009 22:51 Go to previous message
trantuananh24hg
Messages: 664
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Said Mahesh Rajendran that ">> Is it safe to remove them?
Depends on what it is holding". You may identify how many session use sort in temporary tablespace.

1. Create a view
CREATE OR REPLACE VIEW temp_sort_user (
   tablespace_name,
   username,
   sid,
   serial_id,
   contents,
   segtype,
   extents,
   blocks,
   bytes )
AS
SELECT
    b.tablespace          tablespace_name
  , a.username            username
  , a.sid                 sid
  , a.serial#             serial_id
  , b.contents            contents
  , b.segtype             segtype
  , b.extents             extents
  , b.blocks              blocks
  , (b.blocks * c.value)  bytes
FROM
    v$session     a
  , v$sort_usage  b
  , (select value from v$parameter
     where name = 'db_block_size') c
WHERE
      a.saddr = b.session_addr
/


2. Recheck
SQL> select * from temp_sort_user;


3. Create another temporary and make it as default
4. Drop the old temporary tablespace TEMP1 if there are not any session are using it.
5. If you want to re-use the TEMP1, so that, you re-create TEMP1, make it as default, drop TEMP2.

Previous Topic: Pause long running query / job (merged 3)
Next Topic: Row/table/database locks
Goto Forum:
  


Current Time: Thu Sep 29 10:50:06 CDT 2016

Total time taken to generate the page: 0.12627 seconds