How to reduce TEMP01.DBF?

Hello,
I've got two temporary tablespaces TEMP and TEMP02:

select file_name from dba_temp_files where tablespace_name = 'TEMP';
C:\ORACLE\ORADATA\OEA1\TEMP01.DBF

select file_name from dba_temp_files where tablespace_name = 'TEMP02';
F:\DATOSORA\TEMP02:DBF

User System uses tablespace TEMP02 and user Sys uses tablespace TEMP.
Problem is TEMP01.DBF is quite large and I would like to reduce it. But how? Maybe if I do:

alter user SYS temporary tablespace Another_Table_Space;

I would spoil a lot of things... Could anyone give me an example of what to do, please?
Thanks!
 
mogulwindowsAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
geotigerConnect With a Mentor Commented:
Here is the excerption from this Q&A: http://www.experts-exchange.com/Databases/Oracle/Q_20827160.html?query=reduce+temp+tablespace&searchType=all

1. shutdow the database instance to make sure there is nobody using the temp tablespace at this very moment.l
2. say your current , giant temporary tablespace is called "TEMP",

SQL> create temporary tablespace temp2 tempfile '<file_path_to_new_temp_file>' size 100M extent management local uniform size 6M;

SQL> alter database default temporary tablespace temp2;
SQL> drop tablespace TEMP;

--In the OS level, delete the temp file.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.