View tablespace usage in Oracle

Before writing a program, you need to implement the function of querying the tablespace usage in the database. Although you do not know the significance of doing it, you have to do the project requirements.

I wrote one and can only find permanent tablespaces. I don't know how to query temp tablespaces. Today I went online to find them and supplemented the SQL statements completely. In fact, I used them to find existing resources and add them to my favorites.

 

Select *
From (select a. tablespace_name tablespacename,
Nvl (A. Bytes/1024/1024, 0) totalsize,
Nvl (B. largest/1024/1024, 0) freesize,
Nvl (A. bytes-B. bytes)/1024/1024, 0) usedsize,
Round (nvl (A. bytes-B. bytes)/A. bytes * 100, 0), 2) usedpercent
From (select tablespace_name, sum (bytes) bytes
From dba_data_files
Group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest
From dba_free_space
Group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by A. tablespace_name)

Union

Select D. tablespace_name tablespacename,
Nvl (A. Bytes/1024/1024, 0) totalsize,
Nvl (A. Bytes/1024/1024, 0)-nvl (T. bytes, 0)/1024/1024 freesize,
Nvl (T. bytes, 0)/1024/1024 usedsize,
Round (nvl (T. Bytes/a. bytes * 100, 0), 2) usedpercent
From SYS. dba_tablespaces D,
(Select tablespace_name, sum (bytes) bytes
From dba_temp_files
Group by tablespace_name),
(Select tablespace_name, sum (bytes_cached) bytes
From v $ temp_extent_pool
Group by tablespace_name) T
Where D. tablespace_name = A. tablespace_name (+)
And D. tablespace_name = T. tablespace_name (+)
And D. extent_management like 'local'
And D. Contents like 'temporary'