Solution : https://service.sap.com/sap/support/notes/659946 (SAP Service marketplace login required)
Summary :
The SAP Note discusses the need for temporary tablespaces in SAP environments, highlighting their role in storing intermediate data during database operations (e.g., sorting, hash joins, bitmap indexes). It specifies 'PSAPTEMP' as the default temporary tablespace and outlines available types: Dictionary-managed (both permanent and temporary) and Locally-managed (both permanent and temporary), recommending Locally-managed with temporary contents (LMTS/T) for SAP systems. The note advises against using LMTS/P as a temporary tablespace and provides SQL queries to identify tablespaces configurations and assignments to database users. Common issues related to tablespaces are also addressed, such as ORA error codes and solutions for overflow and configuration problems. Recommendations for transitioning between tablespace types and links to additional resources are provided.
Key words :
alter tablespace psaptemp add tempfile '<path_and_filename>' size <size>, extent management local uniform size <size>, extent managementlocal uniform size <size> permanent, alter user <sapuser> temporary tablespace <temp_tsp>, alter database tempfile '<path_and_filename>' drop, alter user <user> temporary tablespace <temp_tsp>, terms frequently asked questions solution 1, create user <user> temporary tablespace <temp_tsp>, alter database default temporary tablespace psaptemp, create temporary tablespace <temp_tsp> tempfile
Related Notes :
| 919588 | Offline backup with RMAN fails with ORA-01220 |
| 827019 | Changed handling of TEMP/UNDO tablespaces in BRCONNECT |
| 825653 | Oracle: Common misconceptions |
| 817253 | FAQ: Open a Database Read only |
| 745639 | FAQ: Oracle enqueues |
| 683075 | Oracle9i: Default Temporary Tablespace |
| 635552 | Backing up temporary tablespaces with BRBACKUP |
| 619188 | FAQ: Oracle wait events |
| 618868 | FAQ: Oracle performance |
| 609532 | ORA-03212 |
| 600513 | ORA-25153 after recovery due to missing tempfiles |
| 548221 | Temporary Files are created as sparse files |
| 546006 | Problems with Oracle due to operating system errors |
| 541538 | FAQ: Reorganization |
| 521264 | Hang situations |
| 500340 | FAQ: LOBS |
| 487318 | ORA-1257 when TEMPILES were created on raw device |
| 485755 | ORA-03232: unable to allocate an extent of blocks |
| 416782 | Support of Oracle TEMPORARY tablespaces in BRBACKUP |
| 416772 | General tablespace types, temporary tablespace parameters |
| 387946 | USE OF LOCALLY MANAGED TABLESPACES FOR BW SYSTEMS |
| 379499 | Query execution: ORA-00604 / ORA-01658 |
| 359835 | Design of the temporary tablespace in the BW System |
| 216881 | SMON process slowly deletes temp. segments PSAPTEMP |
| 216440 | Condensor: ORA-2195 when summarizing InfoCubes |
| 214995 | Oracle locally managed tablespaces in the SAP environment |
| 183842 | Shutdown (immediate) takes a very long time |
| 183791 | Locking on temporary tablespace of Oracle |
| 164925 | Storage parameter of tablespace PSAPTEMP |
| 78595 | ORA1575 timeout waiting for space management res. |
| 4161 | Complete recovery |
| 3155 | Termination due to tablespace overflow |