Check the Size of Temporary tablespace in Oracle

Check the filled size of temporary tablespace in Oracle

Check the size of temporary tablespace temp files:

select tablespace_name,file_name,bytes/1024/1024, maxbytes/1024/1024 from dba_temp_files;

If you find temp files are full and near to full, then you can add the temp files in tablespace:

Find tablespace_name and location of temp files from upper query and add one another file to make some space in TEMPORARY tablespace for sorting operation

ALTER TABLESPACE temp ADD TEMPFILE 'E:\oradata\orcl\temp02.dbf' SIZE 2M;

Check the percentage usage of TEMP tablespace:

select tablespace_name, ' %free='|| round(sum(free_blocks) / sum(total_blocks) * 100 ,0) from gv$sort_segment group by tablespace_name;

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.