Skip to content
March 16, 2015 / doganay

HOW TO CALCULATE MINIMUM DATAFILE SIZE of a CURRENT DATAFILE

Content of dms.sql (datafile minimum size):

--Datafile minimum size
set verify off serveroutput on
set feed off
var   FILE_ID number;
exec :FILE_ID := &1;
var   TBS varchar2(30);
var   BS number;

begin
 select tablespace_name, file_id into :TBS, :FILE_ID from dba_data_files where FILE_ID=:FILE_ID;
 exception
 when NO_DATA_FOUND
 then :FILE_ID:=-1;dbms_output.put_line(chr(10)||chr(13)||'Datafile does not exist');
end; 
/

declare
 V_SIZE number;
begin
 if :FILE_ID<>-1 then
  select BLOCK_SIZE into :BS from dba_tablespaces where tablespace_name=:TBS;
SELECT SUM(BYTES) into V_SIZE
FROM  DBA_FREE_SPACE
WHERE TABLESPACE_NAME = (select TABLESPACE_NAME from dba_data_files where FILE_ID=:FILE_ID)
AND   FILE_ID = :FILE_ID
AND   BLOCK_ID >=  NVL((SELECT (BLOCK_ID + (BYTES/:BS))
                    FROM DBA_EXTENTS
                    WHERE BLOCK_ID = (SELECT MAX(BLOCK_ID)
                                      FROM   DBA_EXTENTS
                                      WHERE  FILE_ID = :FILE_ID
                                      AND    TABLESPACE_NAME = :TBS)
                    AND FILE_ID = :FILE_ID
                    AND TABLESPACE_NAME = :TBS), 0);
dbms_Output.put_line(chr(10)||chr(13)||V_SIZE || ' BYTES = ' || V_SIZE/1024 || ' KB = ' || V_SIZE/1024/1024 || ' MB = ' || V_SIZE/1024/1024/1024 || ' GB');
end if;
end;
/

undefine 1
prompt
set feed on


Usage:

@dms <FILE_ID>


Example:

SQL> @dms 30

Datafile does not exist

SQL> @dms 1

1471086592 BYTES = 1436608 KB = 1402.9375 MB = 1.37005615234375 GB


Edit:

There is a bug with dba_free_space:

Bug 4215578 : VIEW USER_FREE_SPACE VERY SLOW

So you can use a custom view instead of dba_free_space:
https://doganay.wordpress.com/2014/08/22/tablespace-free-space-script-with-custom-dba_free_space/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: