**********************************************************************
Following errors written to the Alert log file. Please
verify
ORA-1652: unable to extend temp segment by 128 in
tablespace TEMP2
ORA-1652: unable to extend temp segment by 128 in
tablespace TEMP2
ORA-1652: unable to extend temp segment by 128 in
tablespace TEMP2
**********************************************************************
Solution
you would just add disk to TEMP to avoid the ORA-1652 error, but you can also wait for SMON to clean-up the TEMP segment.
In sever cases, a slowdown may occur, in which you might want try one of the following work-arounds:
- Increase size of the temp tablespace
- Increase sort_area_size and/or pga_aggregate_target
Identify temporary datafile details :
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;
FILE_NAME TABLESPACE_NAME
/apps/db/apps_st/data/temp01.dbf TEMP1
/apps/db/apps_st/data/temp02.dbf TEMP2
SQL> SELECT TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS FROM v$sort_segment;
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
TEMP2 524160 3456 520704
TEMP 0 0 0
TEMP1 524160 512 523648
Add additional temp file in temporary table-space
SQL> ALTER TABLESPACE TEMP1 ADD TEMPFILE ‘/u01/app/oracle/oradata/ iamdb/temp03.dbf’ size 1024m;
Check if there is any space available in temporary table-space (segment)
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total -SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;
TABLESPACE MB_TOTAL MB_USED MB_FREE
TEMP1 4096 6.625 4089.375
TEMP2 4096 43 4053
Monitoring Temporary Tablespaces and Sorting
Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.
One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE
DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from $TEMP_SPACE_HEADER;
TABLESPACE_NAME BYTES_USED BYTES_FREE
TEMP1 4294967296 0
TEMP2 4294967296 0
Root Cause Analysis
Identify temp segment usages per session
Temp segment usage per session.
SQL > SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,
P.program, -SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used, T.tablespace,
COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY mb_used;
SID_SERIAL USERNAME OSUSER SPID MODULE PROGRAM MB_USED TABLESPACE STATEMENTS
Identify temp segment usages per statement
Temp segment usage per statement.
SQL> SELECT S.sid || ',' || S.serial# sid_serial, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddr
AND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY mb_used;
SID_SERIAL USERNAME HASH_VALUE SQL_TEXT MB_USED TABLESPACE
SQL > select * from v$sort_segment;
TABLESPACE_NAME SEGMENT_FILE SEGMENT_BLOCK EXTENT_SIZE CURRENT_USERS TOTAL_EXTENTS TOTAL_BLOCKS USED_EXTENTS USED_BLOCKS FREE_EXTENTS FREE_BLOCKS ADDED_EXTENTS EXTENT_HITS FREED_EXTENTS FREE_REQUESTS MAX_SIZE MAX_BLOCKS MAX_USED_SIZE MAX_USED_BLOCKS MAX_SORT_SIZE MAX_SORT_BLOCKS RELATIVE_FNO
TEMP2 0 0 128 38 4095 524160 38 4864 4057 519296 0 4980625 0 0 4095 524160 4095 524160 4059 519552 0
TEMP 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1024
TEMP1 0 0 16 37 32760 524160 37 592 32723 523568 0 26657530 0 0 32760 524160 31266 500256 31191 499056 0
Each tablespace has one or more datafiles that it uses to store data.
The max size of a datafile depends on the block size of the database. I believe that, by default, that leaves with you with a max of 32gb per datafile.
To find out if the actual limit is 32gb, run the following:
sql > select value from v$parameter where name = 'db_block_size';
Compare the result you get with the first column below, and that will indicate what your max datafile size is.
I have Oracle Personal Edition 11g r2 and in a default install it had an 8,192 block size (32gb per data file).
Dropping / Recreating Temporary Tablespace Method
Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.
If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:
SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL> DROP TABLESPACE temp;Tablespace dropped.
SQL> CREATE TEMPORARY TABLESPACE TEMP
2 TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
3 AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
Database altered.
Create & drop TEMP TABLE-space in 11gR1
While doing this activity, existing temporary tablespace may have existing live sessions,
due to same oracle won’t let us to drop existing temporary tablespace. Resulting,
we need to kill existing session before dropping temporary tablespace.
Following query will give you tablespace name and datafile name along with path of that
data file.
SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;
Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.
SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/prod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;
Following query will help you to alter database for default temporary tablespace.
( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old
temporary tablespace ( i.e. TEMP ) and kill them.
SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;
Provide above inputs to following query, and kill session’s.
SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';
Example :-
SQL> alter system kill session '59,57391';
Now, we can drop old temporary tablespace without any trouble with following:
SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;
Contents and datafiles are deleted successfully.
If you wish to continue with old temporary tablespace name, i.e.
‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.
