Wednesday, November 24, 2021

ODI - Sql query to monitor TEMP size and resize

Query to monitor the temp total, used and free space in mb:

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, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total from v$tablespace B, v$tempfile C where B.ts#= C.ts#

Group by, C.block_size) D

Where A.tablespace_name =

Group by A.tablespace_name, D.mb_total

Query to Resize the temp file space:

Alter Database TEMPFILE 'tempfile' RESIZE 300G

No comments:

Post a Comment

Featured Post

11g to 12c OSB projects migration points

1. Export 11g OSB code and import in 12c Jdeveloper. Steps to import OSB project in Jdeveloper:   File⇾Import⇾Service Bus Resources⇾ Se...