How To Shrink Oracle Datafile

How to Shrink Oracle Datafile? SHRINK DATA FILE In order to regain unused space on your database you need to use SHRINK command on Oracle. I will try to illustrate this feature with a very simple example: First create a table: CREATE TABLE SHRING_TEST2_TAB( aa number, tt varchar2(1000) ) / Then fill this table with some dummy data: Fill it more and make it a big sized table; Now we have a big table in USERS tablespace. Attention please, We have not committed the data yet. If we issue a rollback statement all of the inserts we have done will be gone and this table will be empty. Normally, we shall expect what we have done would not cost a space to us. But this is so wrong in Oracle terms. Oracle already allocated that space (segments) and even if you didn't commit it, it wont release it until you do something for releasing. Now lets see how SHRINK command works: Did you see the FREE(MB) column, it was 2MB before and after shrink operation it is 25 MB. Don't worry about the bar chart, it will show the right result soon, it is due to OEM’s weird and lazy behavior. Now we have a smaller table but still have a big datafile. Small table was not our main aim, we want to resize the physical file on OS. After this shrink operation we can resize it because we have free space on that file, lets use OEM for that: Before I resize the USERS01.dbf, it has a 51.25 MB size as below: I changed the File Size value from 51.25 MB to 28 MB as below: ATTENTION: If I entered a value lower than 26.25 MB, it would raise an error because there is still 26.25 MB data on this datafile. * If you receive an ORA-03297 error when trying to apply the changes, you need to REORGANIZE the tablespace. How you can reorganize a tablespace is illustrated at the end of this document? And this is how it looks like now; Lets see the result on OS view too, pay attention to the size value of USERS01.dbf file: This is a great feature if you had a lot of delete operations on your tables before, you can free your unused spaces by using this feature. By the way, you need to enable row movement on the table you want to make shrink operation and also the tablespace you are working on must have auto segment management. ORA-03297- File Contains used data beyond requested RESIZE value If you receive above error when you try to resize your datafile, it means that your datafile is fragmented and it is needed to be reorganized. You can reorganize your datafiles by

reorganizing your tablespaces. Below screen shows how can you reorganize tablespaces through OEM: When you choose the Reorganize action, basically oracle will create a job which will create a new tablespace with a new datafile and move all your tables to your new tablespace from old fragmented tablespace. After move operations complete, it will drop your old tablespace and rename fresh tablespace to deleted one’s name. Finally you will have a unfragmented tablespace with new datafile name. Oracle does not delete the old fragmented datafile after reorganizing operation, you need to manually delete it from OS file structure you need to shutdown and startup your db to be able to delete that file from OS otherwise it gives in use error).