I am working with two different PeopleSoft customers who have had challenges with the size of the temporary tablespaces. Critical batch processes have failed because they have exhausted space in the temporary tablespace.
ORA-01652 unable to extend temporary segment...
- In one case, the one and only temporary tablespace in a Payroll system has over time been extended to in excess of 360 GB. This has happen in response to PeopleSoft processes that failed because they cannot allocate temporary tablespace because somebody else has consumed it. This treated the symptom rather than the cause. This system has a number of other Oracle database users who are have read-only access to the PeopleSoft data to perform adhoc queries. These users all share the one temporary tablespace. Occasionally, a query will be submitted that runs for many hours, writing many gigabytes of data to the temporary tablespace, when it would have been better to terminate the process
- Another system has 64 GB in the PSTEMP temporary tablespace used by SYSADM. All other users already use another temporary tablespace, but PeopleSoft processes sometimes still fail because most of the temporary tablespace has been consumed by an adhoc PS/Query process, and there is nothing left for other processes. This system also has other Oracle database users with read-only access, but here they use the default TEMP temporary tablespace.
If a PeopleSoft system has database users executing adhoc queries, then allocating those users to separate temporary tablespace is a sensible first step.
See More: http://blog.psftdba.com/2012/11/using-two-temporary-tablespaces-in....
Tags: