View the usage of Oracle tablespace

1. Check the size of all tablespaces SQLgt; selecttablespace_name, sum (bytes) 10241024fromdba_data_files2groupbytablespace_na

1. View All tablespace sizes SQLgt; select tablespace_name, sum (bytes)/1024/1024 from dba_data_files 2 group by tablespace_na

1. view the size of all tablespaces

SQL> select tablespace_name, sum (bytes)/1024/1024 from dba_data_files
2 group by tablespace_name;

2. view the idle tablespace size
SQL> select tablespace_name, sum (bytes)/1024/1024 from dba_free_space
2 group by tablespace_name;

3. Therefore, space can be calculated in this way.

Select a. tablespace_name, total, free, total-free used from
(Select tablespace_name, sum (bytes)/1024/1024 total from dba_data_files
Group by tablespace_name),
(Select tablespace_name, sum (bytes)/1024/1024 free from dba_free_space
Group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name;

4. The following statement shows the size of all segments.
Select Segment_Name, Sum (bytes)/1024/1024 From User_Extents Group By Segment_Name

Note that all query results are in MB.

,