Check if a the temporary tablespace is autoextensible
Last Updated August 07, 2018
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.
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;
Imported Document ID: TECH219715
Subscribing will provide email updates when this Article is updated. Login is required to Subscribe