1 2 Previous Next 19 Replies Latest reply: Jan 13, 2011 1:27 PM by 662584 RSS

    oracle 10g shrink

    563343
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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....
                              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
                                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....
                                  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
                                    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