Is it good to reclaim space using resizing datafiles? What are the things to be taken care before resizing the datafile?

  • What datafiles? Sys or SysAux tablespaces should be regarded with caution. Is the amount of data growing or static? Clob, non Clob? – kevinsky Oct 20 '15 at 16:22
  • for non-system tablespaces.. like indexes and others. data is growing. non clob. – tesla747 Oct 20 '15 at 16:23
  • should I for sure rebuild the indexes if I resize them? – tesla747 Oct 20 '15 at 16:30

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
|improve this answer|||||
  • what about indexes? should it be rebuilt after resizing? my situation unfortunately falls under 'reasons not to resize' and I have still resized, thanks to my ignorance. since I have done something which I should not have (considering your reasons for resizing) what I can do now? I mean will it be harmful since I have resized it? I have also resized one index data file.. should I rebuild it? – tesla747 Oct 20 '15 at 16:47
  • My guess is you need data integrity more than you need disk space. Doesn't hurt to rebuild the indexes but there should be a reason to do so. Are they fragmented? If not, leave things alone. – kevinsky Oct 20 '15 at 16:55
  • Alright.. But it isnt a rule to rebuild the indexes if the respective data file is resized right? – tesla747 Oct 20 '15 at 17:08

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.