Oracle database is the world’s most popular relational database with advanced features and the capability of storing huge amounts of data. To store this data Oracle provides tablespace, which is a logical storage unit that contains one or more data files (datafiles contain tables, indexes, large objects, and long data).
This post will discuss the following approaches:
- Method 1: Check Tablespace Size in Oracle SQL Developer Using GUI
- Method 2: Check Tablespace Size in Oracle Using Command
- Method 3: Check the Size of a Specific Tablespace in MBs
- Method 4: Check the Size of a Specific Tablespace in GBs
Method 1: Check Tablespace Size in Oracle SQL Developer Using GUI
Open Oracle SQL Developer by searching it from the Start menu:
Double-click on the existing connection and provide its username and password. Press the “OK” button to log in successfully:
Open the drop-down “View” list and click on the “DBA” option. Click on the add “+” icon and select a connection. Provide its credential and hit the “OK” button:
Extend the connection and then click on the “Storage” option:
Locate the folder named “Datafiles” and double-click on it:
A new tab will display details of tablespace along with its size:
You have successfully extracted the information about Oracle tablespace. Let’s see another approach to perform the same operation.
Method 2: Check Tablespace Size in Oracle Using Command
To run a query for checking the tablespace size, open the “SQL Worksheet” by clicking on its icon:
As a result, a new tab will open. Then, type this query to get the details, such as total space, used space (Size), and remaining space in MBs of all tablespaces in Oracle:
totalusedspace "Used MB",
(l.totalspace - m.totalusedspace) "Free MB",
l.totalspace "Total MB",
round(100 * ( (l.totalspace - m.totalusedspace)/ l.totalspace)) "Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) l,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) m
where l.tablespace_name = m.tablespace_name ;
Execute the added query by pressing the “Run Statement” icon. The output will display tablespaces size details:
You have used the SQL command to extract the size details of all Oracle tablespaces, but if you want to extract the size details of a specific tablespace, check out the next approaches.
Method 3: Check the Size of a Specific Tablespace in MBs
Open “SQL Worksheet” and type the command given below to select the space details of a specific tablespace:
from dba_data_files
where tablespace_name=upper('&tname');
A prompt will open to get the name of a specific tablespace. Click on the “OK” button:
The output will display the size of the specified tablespace in MBs:
Method 4: Check the Size of a Specific Tablespace in GBs
Similarly, open the “SQL Worksheet” and run this command to see the space details of a specific Oracle tablespace but in GBs:
from dba_data_files
where tablespace_name=upper('&tname');
Enter the name of tablespace and click on the “OK” button:
The output will show the space details in GBs:
You have learned how to check Tablespace size in Oracle SQL Developer.
Conclusion
In Oracle SQL Developer, open the drop-down “View” list and select the “DBA” option. Add an existing connection and maximize it. Go to the “Storage” folder and double-click on the “Datafiles” folder. It will display the Tablespaces size. You can also run the commands to check the sizes of all tablespaces as well as the size of the specific tablespace. This post demonstrated different methods to check Tablespace size in Oracle SQL Developer.
