Is it good to reclaim space using resizing datafiles? What are the things to be taken care before resizing the datafile?
If the data files are growing then any file space you reclaim will be reused as the files grow.
Reasons that cause me to think about re sizing are:
- after moving a table/partition/index from one table space to another
- after truncating a large table (example: a logging table)
Reasons not to re size:
- data files have to be empty to re size below the high water mark, 11g offers online reorganization where the files do not have to be empty
- from here: a change that demands some preparation:
- take a backup
- Export the schema objects that are stored in the specific tablespace/datafile you want to resize.
- Drop the exported objects.
- Resize the datafiles.
- Import the objects.
- Perform any required maintenance, like grants, recompilation, rebuild indexes
- requires downtime
- for a growing table the space will be used again anyway
It depends what you are trying to achieve. If you create a non-autoextend datafile the space will be allocated immediately, but there will be nothing inside in reality. You begin loading stuff and that "empty" space starts to fill. At some point you decide that the 30GB you have allocated are too much for that datafile and resize it (nothing critical may happen by doing that) up to its high water mark (HWM). If you have autoextensilbe datafile they will extend as you load stuff in them. If you delete stuff the dbf will not resize automatically it expands up to the max you have set. For example you start with a 100MB datafile, and set it to autoextend to 5GB. You load stuff and extend the datafile to 3GB (Oracle does it for you), you decide that something needs to be deleted and delete it, you see that you are tight on disk space (filesystyem or ASM) and need to reclaim some from that same datafile. You can resize it as much as it allows the HWM. I`ve been doing that a million times and nothing has happened that would kill the instance or corrupt it. Hope that helps.