How to Resize a Datafile

There may be situations when you need to increase or decrease your datafile size. Prior to oracle 7.2 there was no easy way to resize the datafile. Before 7.2 the solutions was to drop and recreate the tablespace with different sized datafiles, or to add more datafiles to a tablespace whenever you need more space in the tablespace.

Before 7.2 the RESIZE command will raise error,
ORA-00923: FROM keyword not found where expected

Before entering into resize datafile let's be familiar with several views related to datafile.
From V$DATAFILE.CREATION_TIME we can see the timestamp of the datafile creation time.
From V$DATAFILE.BYTES we can see the current datafile size in bytes. 0 in this fields indicate the datafile is inaccessible.
From V$DATAFILE.CREATE_BYTES we can the datafile size when it was created.
From V$DBA_DATA_FILES.MAXBYTES we can see the maximum size of the datafile.

Before going into resize I just create one tablespace containing one datafile of size 10M which can be extended up to 100M.

SQL> create tablespace test_tbs datafile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\SHAIK\test_tbs01.dbf' size 10M autoextend on maxsize 100M;
Tablespace created.

Now have a look at the current size of maximum size of this data file from dba_data_files view.
SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;
BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
10 100

Increase datafile size
To see the current settings of the datafile query from v$datafile view. The BYTES column shows the current size of the datafile, and the CREATE_BYTES column shows what the size was
specified when the file was created.

Current size is 10M we can increase it upto 15M by,

SQL> alter database datafile 6 resize 15M;
Database altered.

SQL> select bytes/1024/1024, maxbytes/1024/1024 from dba_data_files where file_id=6;

BYTES/1024/1024 MAXBYTES/1024/1024
--------------- ------------------
15 100

SQL> select bytes/1024/1024, create_bytes/1024/1024 from v$datafile where file#=6;
BYTES/1024/1024 CREATE_BYTES/1024/1024
--------------- ----------------------
15 10

Here 12 is current size and 10 is creation time size.

Decrease Datafile size
Downsizing a datafile is more complicated than increasing the size of a datafile. Because you cannot deallocate space from a datafile that is currently being used by database objects. To remove space from a datafile, you have to have contiguous space at the END of the datafile. From DBA_FREE_SPACE we can see the free space in the datafile.

To resize our datafile to 2M issue,
SQL> alter database datafile 6 resize 2M;

If you try to resize a datafile to a size smaller than is needed to contain all the database objects in that datafile, the following error is returned,

ORA-03297: file contains blocks of data beyond requested RESIZE value
Or
ORA-03297: file contains used data beyond requested RESIZE value