How to resize the PSAPTEMP
I extended the PSAPTEMP from 30G to 120G (autoextended switch on by alter temfile) due to rebuild big indexes
Now the operation is finished , and i would like to resize it back to 30G
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/QAT/temp.data2' SIZE 30720M REUSE AUTOEXTEND ON NEXT 10240 MAXSIZE 30720M;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/QAT/temp.data3' SIZE 30720M REUSE AUTOEXTEND ON NEXT 10240 MAXSIZE 30720M;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '/oracle/QAT/temp.data4' SIZE 30720M REUSE AUTOEXTEND ON NEXT 10240 MAXSIZE 30720M;
I need to resize it or i need to drop the 3 added tempfile?
Could you share me the command?
Just one more question: there is for table OLTP compression, it will not ues the tempfile right?
Yves KERVADEC replied
You can shrink the 3 datafiles to 10G .
ALTER DATABASE TEMPFILE '/oracle/QAT/temp.data2' resize 10G;
ALTER DATABASE TEMPFILE '/oracle/QAT/temp.data3' resize 10G;
ALTER DATABASE TEMPFILE '/oracle/QAT/temp.data4' resize 10G;
If you are on version 11g you can also do it using
alter tablespace PSAPTEMP shrink space keep 30G;
If you are planning to perform compression, tempfile should be big enough.
Please make sure that your PSAPTEMP Temporary Tablespace is at least as big as the biggest index to rebuild