Database Administration (MOSC)

MOSC Banner

How should I determine the available free space in temporary tablespace ?

edited Jan 22, 2010 3:19AM in Database Administration (MOSC) 6 commentsAnswered
 Hi all,

I encountered the error ORA-01652: unable to extend temp segment by 640 in tablespace TEMP in the alert log.

DB version : 9.2.0.8.0 on Windows 2000 server

So, I query to see the available free space in tablespace TEMP (temporary) as below :

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

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center