Skip to content
March 16, 2015 / doganay

HOW TO CALCULATE MINIMUM DATAFILE SIZE of a CURRENT DATAFILE

def TABLESPACE_NAME='TBS1'
def FILE_ID=1
def BLOCK_SIZE=8192

SELECT  SUM(BYTES)
FROM  DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '&&TABLESPACE_NAME'
AND   FILE_ID = &&FILE_ID
AND   BLOCK_ID >=  NVL((SELECT (BLOCK_ID + (BYTES/&&BLOCK_SIZE))
                    FROM DBA_EXTENTS
                    WHERE BLOCK_ID = (SELECT MAX(BLOCK_ID)
                                      FROM   DBA_EXTENTS
                                      WHERE  FILE_ID = &&FILE_ID
                                      AND    TABLESPACE_NAME = '&&TABLESPACE_NAME')
                    AND FILE_ID = &&FILE_ID
                    AND TABLESPACE_NAME = '&&TABLESPACE_NAME'), 0);
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: