How to Check Temp Tablespace Usage in Oracle

0 Comments

0
(0)

How to Check Temp Tablespace Usage in Oracle


Below is SQL query to find out temporary tablespace utilization of the database. which includes Tablespace name(Temp tablespace), Total size, Used size, and Free size.

SQL command which fetches Temp tablespace size in GB. Click here to get sample output.

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) D
    WHERE A.tablespace_name = D.name
    GROUP by A.tablespace_name, D.gb_total;

SQL command which fetch database size 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 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;

Hope so you like this script!

Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to know more about our pursuit.

 

 282 Total Views,  3 Views Today

How useful was this post?

Click on a star to rate it!

Average rating 0 / 5. Vote count: 0

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the Knowledge!

Leave a Reply

Your email address will not be published. Required fields are marked *

two × one =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru
Direct Call:+91-9310167776
Email: info@dbsguru.com

Share Learn Grow!


Click on the below technical group to join us to share expertise in PostgreSQL and MySQL on WhatsApp group. The purpose of this group is only help to the DBA community.

PostgreSQL Technical Group.

MySQL technical Group.

NOTE: Excuse us for spammer/promoter i.e don't join the group for spam, will be kicked off without warnings.

Thanks.
Team DBsGuru.

Share Learn Grow!

Welcome to DBsGuru! We wish you a very healthy day, hope and pray things to go in a good way for all of humanity. Stay safe!

We encourage technology experts to contribute share technical knowledge in form of writing technical articles/blogs, SQL commands for daily usage (basic to a high level), Carrier guidance on any technology, and become an author.

We have a ready platform for you with no profit no loss (as of now, in the future you may also earn revenue) if you are ready to contribute to writing articles, click on the registration link and the article will be published as an individual contributor on your name.

Click here for registration

Thanks,
Team DBsGuru
We Commit We Deliver