Command to know free space in database

Asked By archie franco 0 points N/A Posted on - 03/07/2012

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

• Status: Open
• Question Views: 1074
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: Oracle Database

Best Answer by Sharath Reddy

Go To Solution

Answered By Kanhai Else 0 points N/A #102844

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

About Kanhai Else

Questions
0

Answers
56

Best Answers
8

• Vote Up 0 Vote Down

• Posted on - 03/14/2012
• Question Category: Oracle Database

Best Answer

Best Answer

Answered By Sharath Reddy 590495 points N/A #102845

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.

About Sharath Reddy

Questions
1

Answers
14599

Best Answers
2290

• Vote Up 0 Vote Down

• Posted on - 10/01/2013
• Question Category: Oracle Database