Check if a the temporary tablespace is autoextensible

book

Article ID: 159819

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

Multiple ORA-01555 errors occur stating that the TEMP tablespace is unable to extend the temp segment by a particular size.

If the database template (DBT) was used in the creation of the database, this should be a rare occurrence.  The DBT establishes the temp tablespace with autoextend on and the ability to grow to a max of "unlimited", which is to say 32G for a single file.

 

Resolution

To determine if the autoextend option has been set for a temp tablespace, log into SQLPlus as SYS as SYSDBA, and run the following script:

column file_name format a30
select file_name, tablespace_name,
autoextensible
from dba_temp_files
/;

To turn autoextend on, modify the following script to include your temp datafile and path, and run from SQLPlus as SYS as SYSDBA:

alter database tempfile '<temp data path>\<name of tempfile>.DBF' autoextend on;

Here is an example:

alter database tempfile 'C:\APP\ADMINISTRATOR\ORADATA\PROTECT\TEMP01.DBF' autoextend on;