How to shrink datafiles for save disk space

Today i show you how can we resize that datafile which really uses big space and it doesn’t need.
situation is next: i have one big table in USERS tablespace, i don’t need it, therefore drop with purge option

SQL> drop table ika.test purge;

Table dropped.

it isn’t not in recycle bin and space within tablespace free up for other objects, but this space is allocated for tablespace and when you want to return that free space to disk, you can do it as explained below.
First we need to understand what is minimum size that we can resize datafile, for this we can use this script:

SELECT tablespace_name,
       file_size - hwm can_save
  FROM (  SELECT                                                   /*+ RULE */
                 ddf.file_name file_name,
                 ddf.bytes / 1048576 file_size,
                 (ebf.maximum + de.blocks - 1) * dbs.db_block_size / 1048576
            FROM dba_data_files ddf,
                 (  SELECT file_id, MAX (block_id) maximum
                      FROM dba_extents
                  GROUP BY file_id) ebf,
                 dba_extents de,
                 (SELECT VALUE db_block_size
                    FROM v$parameter
                   WHERE name = 'db_block_size') dbs
           WHERE     ddf.file_id = ebf.file_id
                 AND de.file_id = ebf.file_id
                 AND de.block_id = ebf.maximum
        ORDER BY 1, 2);


in USERS tablespace we have one datafile +DISKS/orcl/datafile/users.262.827063307 which size is 13240MB for this datafile we can resize it to approximately 209 MB and can save 13031 MB, let’s do it:

alter database datafile '+DISKS/orcl/datafile/users.262.827063307' resize 210m;

after that when we re-run above check script result is:

As we see USERS tablespace’s datafile resized and now it’s size is 210m and saved space 13031 MB added to disk.

that’s all.


Leave a Reply

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

You are commenting using your 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