ORA-1652

The quick fix for temp tablespace issue (ORA-1652) which is to add temporary datafile and then find root cause of temp tablespace issue.

Extent – An 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.
Segment – A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace.
Temporary Tablespace – contains transient data that persists only for the duration of the session.

Quick Fix for ORA-1652
=======================

1. Identify temporary datafile details :
SQL> select file_name , TABLESPACE_NAME from DBA_TEMP_FILES;
—–
/u01/oradata/VIS11i/temp01.dbf TEMP

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

SQL> 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;

—-
output like

TABLESPACE MB_TOTAL MB_USED MB_FREE
——————————- ———- ———- ———-
TEMP 2548 2536 12

(in above case out of 2 GB only 12 MB is free)

3. Temporary fix

a) Resize temporary file as
SQL> ALTER DATABASE TEMPFILE ‘/u01/oradata/VIS11i/temp01.dbf’ RESIZE 3072M;

or

b) Add temp datafile to temporary tablespace as

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE ‘/u01/oradata/VIS11i/temp02.dbf’
SIZE 1024M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE 1024M;

.

Root Cause Analysis
===================

1. 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;

2. 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;
Depending on outcome of temp segment usage per session and per statement focus on problematic session/statement.

.
References/Related

•793380.1 ORA-1652 Error Troubleshooting
•258941.1 RAC databases and ORA-1652: Unable To Extend Temp Segment By %s In Tablespace TEMP

=============================Real workaround========================

SQL> SELECT tablespace_name,SUM(bytes_used/1024/1024),SUM(bytes_free/1024/1024) FROM v$temp_space_header GROUP BY tablespace_name;

Tablespace SUM(BYTES_USED/1024/1024) SUM(BYTES_FREE/1024/1024)
——————– ————————- ————————-
GX_TEMP 84 2916
NAS30_TMP 196 804
PROSOD_TMP01 10592 37408
TEMP 5000 0
TIVOLI_TEMP 250 0

SQL> select bytes/1024/1024,FILE_NAME from DBA_TEMP_FILES where TABLESPACE_NAME=’TEMP’;

BYTES/1024/1024
—————
FILE_NAME
——————————————————————————————————————————————————
5000
/uat_u03/oradata/odrmu/data/temp_01.dbf

SQL> alter DATABASE TEMPFILE ‘/uat_u03/oradata/odrmu/data/temp_01.dbf’ resize 6000m;

Database altered.

SQL> select bytes/1024/1024,FILE_NAME from DBA_TEMP_FILES where TABLESPACE_NAME=’TEMP’;

BYTES/1024/1024
—————
FILE_NAME
——————————————————————————————————————————————————
6000
/uat_u03/oradata/odrmu/data/temp_01.dbf

SQL> SELECT tablespace_name, SUM (bytes_used/1024/1024),SUM (bytes_free/1024/1024) FROM v$temp_space_header GROUP BY tablespace_name;

Tablespace SUM(BYTES_USED/1024/1024) SUM(BYTES_FREE/1024/1024)
——————– ————————- ————————-
GX_TEMP 84 2916
NAS30_TMP 196 804
PROSOD_TMP01 10592 37408
TEMP 5000 1000
TIVOLI_TEMP 250 0

Advertisements

About shanojkumar

• Oracle Certified Professional with over 15 years of IT experience in database administration and support. • Certifications: OCP (12c, 11g & 10g), Oracle Database 11g Performance Tuning Certified Expert (OCE), ITIL V3 Certified and LPIC-1(Linux). • Oracle Certified Expert in oracle database performance tuning has solved over 100 database performance problems. • Quality oriented IT professional with notable success in Banking, Airlines, Retails, FMGC, Telecommunication and Media projects. • Maintaining an online technical blog - contributed more than 54+ articles based on ‘Database Administration’ real life scenarios on: https://shanojkumar.wordpress.com/ – 258,429 visitors all over the world. • Instructor of one of the most appreciated courses (Oracle Dataguard 12c) on Udemy with an average rating of 5 star. • Supervised a team of database administrators and technical infrastructure team members. Created documentation on best practices for database performance tuning and Oracle application installation practices.
This entry was posted in Oracle and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s