Search Blog Post

Wednesday, March 6, 2013

ORA-1652: unable to extend temp segment by 128 in tablespace TEMP

This post covers quick fix for temp tablespace issue (ORA-1652) which is to add temporary datafile

ExtentAn extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents make up segment.
SegmentA segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace.
Temporary Tablespacecontains transient data that persists only for the duration of the session.

Fix for ORA-1652
1. Identify temporary datafile details :
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;



2. Check if there is any space available in temporary tablespace (segment)


SQL> select sum(bytes)/1024 "Size_MB"from dba_temp_files where tablespace_name like 'TEMP';


   Size_MB
----------
  19548160
  


3.  Temporary fix
a) Resize temporary file as
 SQL> alter database tempfile '/mnt/PRDdata/trcprd/temp01.dbf' Resize 10G;

Database altered

or

b) Add temp datafile to temporary tablespace as

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/mnt/PRDdata/trcprd/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M  MAXSIZE 1024M;

 

No comments:

Post a Comment