Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com
http://www.free-online-exams.com
Problem Description: ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Alert log file shows:
Wed Mar 31 04:12:05 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Wed Mar 31 04:29:48 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Wed Mar 31 04:44:35 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Solution:
a) Tune the queries/statements so that the sort operations are done in memory and not on the disk.
To find the owner of a sort, please refer to Note 1069041.6 How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts
b) If the query is well tuned then you mostly need to increase the TEMP tablespace.
Please add free space to the tablespace using one of follow ways:
1) SQL> alter tablespace <tablesapce_name> add datafile <datafile_name> size <integer>
2) SQL> alter database datafile <datafile_name> resize <integer>
Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
Note that the extents allocated for a user's sort segment are NOT deallocated but are marked as FREE from performance reasons.
The FREE extents can be further used by other users that are executing sort operations. After the database restart the allocated extens are NOT released either but are FREE to be reused.
Hence, seeing the physical space of the temporary tablespace fully allocated is not a reason to be concerned . The above query of V$SORT_SEGMENT should be used to establish the
free space inside the temporary tablespace :
SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
References:
Note.19047.1 OERR ORA 1652 unable to extend temp segment by %s in tablespace %s P1-914CU
Note.793380.1ORA-1652 Error Troubleshooting
Note 1025288.6 Ext/Pub How to Diagnose and Resolve ORA-01650,ORA-01652,ORA-01653,ORA-01654,ORA-01688 Unable to Extend <OBJECT > by %S in Tablespace %
Note.317441.1How Do You Find Who And What SQL Is Using Temp Segments
Alert log file shows:
Wed Mar 31 04:12:05 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Wed Mar 31 04:29:48 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Wed Mar 31 04:44:35 2010
ORA-1652: unable to extend temp segment by 128 in tablespace TEMP2
Solution:
a) Tune the queries/statements so that the sort operations are done in memory and not on the disk.
To find the owner of a sort, please refer to Note 1069041.6 How to Find Creator of a SORT or TEMPORARY SEGMENT or Users Performing Sorts
b) If the query is well tuned then you mostly need to increase the TEMP tablespace.
Please add free space to the tablespace using one of follow ways:
1) SQL> alter tablespace <tablesapce_name> add datafile <datafile_name> size <integer>
2) SQL> alter database datafile <datafile_name> resize <integer>
Add more tempfiles, increase the size of the current ones or enable auto extend and the most important:
Note that the extents allocated for a user's sort segment are NOT deallocated but are marked as FREE from performance reasons.
The FREE extents can be further used by other users that are executing sort operations. After the database restart the allocated extens are NOT released either but are FREE to be reused.
Hence, seeing the physical space of the temporary tablespace fully allocated is not a reason to be concerned . The above query of V$SORT_SEGMENT should be used to establish the
free space inside the temporary tablespace :
SQL> select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;
References:
Note.19047.1 OERR ORA 1652 unable to extend temp segment by %s in tablespace %s P1-914CU
Note.793380.1ORA-1652 Error Troubleshooting
Note 1025288.6 Ext/Pub How to Diagnose and Resolve ORA-01650,ORA-01652,ORA-01653,ORA-01654,ORA-01688 Unable to Extend <OBJECT > by %S in Tablespace %
Note.317441.1How Do You Find Who And What SQL Is Using Temp Segments
No comments:
Post a Comment