Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

How to resize the PSAPTEMP

Dear expert,

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?

Thank you!

Kate

Tags:
Former Member
replied

Hello

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.

1109743 - Use of Index Key Compression for Oracle Databases

Please make sure that your PSAPTEMP Temporary Tablespace is at least as big as the biggest index to rebuild

Regards

0 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question