Database Administration (MOSC)

MOSC Banner

Temp Tablespace usage

edited Sep 24, 2020 3:46PM in Database Administration (MOSC) 13 commentsAnswered

Hi - We are in 12.2.0.1 Apr 2020 CPU patch in Exadata. We were doing the proactive monitoring and our DBA found that temp tablespace have gone upto 9TB.

I am using below query to check the temp tablespace size.

SELECT   A.tablespace_name tablespace, D.gb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_used,

D.gb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 /1024 gb_free

FROM     v$sort_segment A,

(

SELECT   B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 /1024 gb_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