This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Jan 13, 2011 11:27 AM by 662584 RSS

oracle 10g shrink

563343 Newbie
Currently Being Moderated
Hi,

I understand that in oracle 10g, we can shink a table and compact it so that the high level water mark in the table will be reduced.

However, i would like to know. Through shrinking table, we won't be able to really reduce the datafile high level water mark in order to resize it as compared to move tables to another tablespace for re-org. am i right?

for example, in a tablespace consisting of 1 datafile, there are 10 tables. although we managed to shink (compact) the space in each indivdual table, we are not able to compact the space (gap) between the tables. am i right? only way is to move the table to another tablespace right?>

thanks
  • 1. Re: oracle 10g shrink
    247514 Expert
    Currently Being Moderated
    That's correct. The shrink space is alter table option. You need to
    alter table .. move
    to relocate the table segment.
  • 2. Re: oracle 10g shrink
    Eduardo Legatti Explorer
    Currently Being Moderated
    Hi,

    >>Through shrinking table, we won't be able to really reduce the datafile high level water mark in order to resize it
    I think that reduce the datafile it's possible, because that HWM are adjusted to an appropriate location and unused space is deallocated from the segments, so it is available for the tablespace to reallocate to other objects as needed ... Have you tried to make a test?

    Cheers

    Legatti
  • 3. Re: oracle 10g shrink
    26741 Oracle ACE
    Currently Being Moderated
    SHRINK may cause Oracle to deallocate one or more extents of a table.
    However, remember that a tablespace contains multiple segments -- each table,
    index, lobsegment etc is a segment with multiple extents.
    Thus, at the datafile HWM, you could very well have extent(s) belonging to some
    other table(s) apart from the one(s) you did a SHRINK for.
    It would be those other extents which would prevent you from resizing the datafile.
  • 4. Re: oracle 10g shrink
    Eduardo Legatti Explorer
    Currently Being Moderated
    Hi,

    >>It would be those other extents which would prevent you from resizing the datafile.
    Ok, I mean if all segments contained in a tablespace has been shrunk, so, I think it's possible to reduce the size of the datafile ...

    Cheers

    Legatti
  • 5. Re: oracle 10g shrink
    247514 Expert
    Currently Being Moderated
    The catch is even each table segment have one extent but the location of this extent in the tablespace could still prevent resize the datafile.

    For example, you have 100MB datafile. You create one table A with 50MB size, then added another table B with 50MB.

    The rough datafile mapping will looks like,
    AAAAABBBBB
    Assuming each character stand for 10MB.

    After shrink space, the two tables are only 10MB each now, the datafile mapping become,
    A----B----
    can you resize the datafile to 20MB now ? The answer is No.

    Unless you alter table B move to 20MB mark, you still can only resize to 60MB.
  • 6. Re: oracle 10g shrink
    Eduardo Legatti Explorer
    Currently Being Moderated
    Hi,

    >>Unless you alter table B move to 20MB mark, you still can only resize to 60MB.
    Great explanation ... So, at least, to reduce the size of a datafile from 100MB to 60MB it's possible in this case.

    Cheers

    Legatti
  • 7. Re: oracle 10g shrink
    26741 Oracle ACE
    Currently Being Moderated
    If, after you go through all the effort to SHRINK all or most segments in the
    tablespace (datafile), you find that you need to or you decide to do a MOVE,
    it might have made more sense to have actually done the MOVE upfront.
    A MOVE also does an implicit shrink, except that it requires downtime.
    A SHRINK doesn't require downtime.

    SHRINK Advantages :
    downtime not required

    SHRINK Disadvantages :
    High undo and redo.
    Cannot be done nologging.
    There are other restrictions on which table can be SHRINKed (see the docs).
    Might still require call(s) to MOVE if you want to resize the datafile(s).

    MOVE Advantages :
    can be done with nologging and parallel
    avoiding undo with append

    MOVE Disadvantages :
    Requires double the space (if you do tables serially, it requires only as much as the largest table, not the sum of all tables)
    Requires downtime
    Requires an index rebuild
  • 8. Re: oracle 10g shrink
    563343 Newbie
    Currently Being Moderated
    thanks guys.

    all the advises really help especially example from yingkuan..

    so i figure that the shrink only have the following advantages?
    1. can be done online
    2. increase peformance by reducing time taken for full table scan.

    what other advantages which i missed out?

    let's say right now my table has this mapping
    a= filled
    b=empty - due to some delete

    ababbaa

    so now when oracle is trying to insert some record into the table, can it insert between the gap between the first a and 2rd a?


    thanks
  • 9. Re: oracle 10g shrink
    620899 Newbie
    Currently Being Moderated
    Yes, those free blocks will go to the freelist(according to the extent management option) and oracle will use for new inserts .
  • 10. Re: oracle 10g shrink
    617544 Newbie
    Currently Being Moderated
    is it possible to do it in ASM? I mean is it possible to to resize/move ASM's datafiles on disks without requiring any downtime at prod database. I wonder it may be possible with some online mirroring algorithms or something like that
  • 11. Re: oracle 10g shrink
    Aman.... Oracle ACE
    Currently Being Moderated
    I have not come across that in ASM this thing is allowed that a DBA is allowed to move the files like in o/s.ASM already has the files in striped set so there wont be one complete file stored anywhere whch one can move and moreoever,in asm the striping is actualy with asm Allocation Units AU which are moved depending upon the hardware configuration on the fly to maintain the performance across the disk so I dont think that DBA has to be involved there.
    Resizing of a file I guess should not be an issue as even in ASM the file is still a file which has allthe options available as like a data file on a nomral o/s.So that should not be a problem.
    Aman....
  • 12. Re: oracle 10g shrink
    617544 Newbie
    Currently Being Moderated
    Thx, aman...as most of time i explained myself wrong.
    Let say we are using usual file system files as storage. i mount a faster disc drive to my server and want to move some of my datafiles to this new drive. Is it possible to do it online? (i mean everything online)

    Lets look from an other point of view, in a highend storage box, when you install new disks, you may mirror data to newly installed disks online and when synchronization completes you can remove old disks from mirror configuration. This allow you to migrate from a disk group to an other online.

    We can assume OS drives as disks of ASM. Is it also possible for ASM to migrate data from one OS drive to an other without requiring any downtime at production database. Is there any method for this, like the one mentioned at hardware level.

    What i want to achive is to migrate my physical data from an OS drive to another online
  • 13. Re: oracle 10g shrink
    Aman.... Oracle ACE
    Currently Being Moderated
    Erkan,
    I understood you now cpmpletely.Sorry I have a real small brain so io is very slow :).To really tell you the truth,I don't have the idea at the moment but give me some time and I shall come back to you for this with some sure short answer.Till the time lets wait for others to share about it.
    Aman....
  • 14. Re: oracle 10g shrink
    617544 Newbie
    Currently Being Moderated
    Believe me you have a nice and well tune brain. Other case probably you would be a politician, not dba:)
    Thx...
1 2 Previous Next