Hi
Is there any direct command to know utilized tablespace and available free space and used space in database.
And also the logfile space ?
Thanks
Command to know free space in database
How to find used space within the database
SQL> SELECT SUM(bytes)/1024/1024 FROM dba_segments;
To find the free space in data file
SQL> col "DataSize" format a20
col "FreeSpace" format a20
select round(sum(U.bytes) / 1024 / 1024 ) || ' MB' "DataSize"
, round(F.x / 1024 / 1024) || ' MB' "FreeSpace"
from (select bytes from v$datafile
union all
select bytes from v$tempfile
union all
select bytes from v$log) U
, (select sum(bytes) as x from dba_free_space) F
group by F.x
To find free table space name, used & free spaces
SQL> SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM V$temp_space_header
GROUP BY tablespace_name;
To find free log file space
SQL> select bytes from v$log
Command to know free space in database
There are different ways how you can determine the amount of free space available for your database and database files. One of the many functions of DBA is to track the amount of free space available in your database and database files. Practically managing the database files is a healthier approach although using the auto grow feature is also good but as a last resort.
There are several methods of checking the amount of free space available in your database. You need first to select a database and then gather few information about the files. You can use these commands for this task. See image.
Running these commands will have the output below. It will generate the total size of the database and the size of the files. If the database had more files, they will all be listed. But still this doesn’t display the total amount of free space that the database has. See image.
One method you can use to check the free space is “sp_spaceused”. You can run it in this format. See image.
Running “sp_spaceused” will have the following output. See image.
Other methods you can use are DBCC SQLPERF, DBCC SRHINKFILE, FILEPROPERTY. To know how to use them, visit Ways of checking database free space.
