Must reduce size of temp01.dbf.... but how??

[stevenriz]

We are very close to running out of disk space where one of our Oracle databases reside. It is v8.1.6 on Solaris 2.6. Here are the files and file sizes. As you can see temp01.dbf is huge. We "defragged" temp01 so the mojority of it is empty, but there is still something there ruining the contiguous space. Therefore I cannot truncate it. Any help would be appreciated to reduce the size of the file. If all else fails, I am going to do an export, reinstall the db and import the data into the new instance!

1 oracle dba 1351680 Apr 8 14:05 control01.ctl
1 oracle dba 1351680 Apr 8 14:05 control02.ctl
1 oracle dba 1351680 Apr 8 14:05 control03.ctl
1 oracle dba 20979712 Apr 8 14:04 drsys01.dbf
1 oracle dba 20979712 Apr 8 14:04 indx01.dbf
1 oracle dba 73408512 Apr 8 14:05 rbs01.dbf
1 oracle dba 512512 Apr 8 14:04 redo01.log
1 oracle dba 512512 Apr 8 14:01 redo02.log
1 oracle dba 512512 Apr 8 14:00 redo03.log
1 oracle dba 329916416 Apr 8 14:04 system01.dbf
1 oracle dba 6629629952 Apr 8 14:04 temp01.dbf
1 oracle dba 10493952 Apr 8 14:04 tools01.dbf
1 oracle dba 20979712 Apr 8 14:04 users01.dbf

 

[rastorin]

I believe that this datafile belongs to its tablespace would temporaria, and affirmative case executes the following command:

alter tablespace temp default storage(pctincrease 0);

 

[alexhu]

get the users off the database
drop the tablespace
create a new smaller one

Alex

 

[stevenriz]

Thank you!

We might try that. You can drop temp space just like any other space? Do you need to identify that it is temp space somehow?

 

[alexhu]

The temp space is used for sorting data. If there is no one on the database then it is not being used and its contents are of no value.

alter tablespace temp offline;
alter tablespace temp drop; (you may have to specify 'including contents' here

create tablespace temp datafile...........etc

Alex