How to resize the PSAPTEMP

[Former Member]

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

[ACE-SAP · Accepted solution]

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

[Former Member · Accepted solution]

Hi Mr Yves,

Thank you so much for the easy command.

Yes, i forget that the tbreorg oltp compression will rebuild the index at the same time.

Then i plan to shrink the temp table later

Best regards

Kate

[former_member195313]

Hello Fidel ,

Then we can try to add new TEMP file and remove the old one instead of so we can change TEMP Tablespace size after that

- Connect to SQLPLUS via SYSDBA authorization.

- RUN following syntax for creating new TEMPFILE with value of 1000MB

alter tablespace temptablespace add tempfile ‘’ size 1000M autoextend on;

example ;

alter tablespace PSAPTEMP add tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ size 1000M autoextend on;

Drop TEMPFILE procedure

- Connect to SQLPLUS via SYSDBA authorization.

- RUN following syntax for first getting OFFLINE to releated TEMPFILE and then DROP with releated Datafiles on OS level

alter database tempfile ‘disk location of TEMPFILE ’ offline ;

alter database tempfile ‘disk location of TEMPFILE ’ drop including datafiles;

example ;

alter database tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ offline;

alter database tempfile ‘G:\ORACLE\SID\SAPDATA1\TEMP_10\TEMP.DATA10′ drop including datafiles;

I hope it helps...

[former_member195313]

Hello All ,

Also you can use the SHRINK option for other SAP tables..

Just open sqlplus and use "row movement" and "shrink space" commands..(You can use it online)

Shrink Oracle Tables

[fidel_vales]

Very nice, good answer but unrelated to the OP.

But it would be great that you read the question before you answer

She is talking about a tablespace, not tables

She is talking about the TEMPORAL tablesplace, which should be temporal and no table should be there, making your answer totally out of place

[karthikeyan_v2]

Hi Kate,

I am not sure on resizing the tablespace but you can try the below option.

Export all tables from the tablespace

** Don't execute the command please follow through Brtools**

      brspace -u / -f tbexport -s <Tablespace> -t "*" -u /dumpdir

2)Delete those tablespaces and recreate.

Before deleting note down the tablespace used size and then create new one based on the usedsize.

3)Recreate the tablespace and import the table contents

Regards,

Karthik

[Former Member]

Hi Karthik

I will first try the easy command , thank you so much for giving so many details!

I will first test it in the sandbox, since haven't yet try the export table

Best regards