Command to know free space in database

Asked By 0 points N/A Posted on -
qa-featured

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

SHARE
Best Answer by Sharath Reddy
Answered By 0 points N/A #102844

Command to know free space in database

qa-featured

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

Best Answer
Best Answer
Answered By 590495 points N/A #102845

Command to know free space in database

qa-featured

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.

Related Questions