A normal and reasonable scenario to resize a datafile is increasing, for example, to increase a file from 6GB to 8GB:
SQL> ALTER DATABASE DATAFILE '/oracle/oradata/db1/datafile1.dbf' RESIZE 8G;
In a very rare cases, you might want to downsize a datafile when the space is a precious resource in your server. The syntax is the same as above, but it usually fails because of the scattered data is all over the datafile.
It also might succeed on down sizing, if you just added or raised the datafile not for a long time. Otherwise, you should try to defragment (reorganization) all the segments that use the datafile thoroughly first, then try the resize syntax.
SQL> ALTER TABLESPACE users ADD DATAFILE '/oracle/oradata/db1/datafile2.dbf' SIZE 16G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
Tablespace altered.
SQL> ALTER DATABASE DATAFILE '/oracle/oradata/db1/datafile2.dbf' RESIZE 2G;
Database altered.
Further reading: How Big a Data File Can Be?
Got it thanks ED, I know you’re one of the best.
Thank you very much!