7 Replies Latest reply: Mar 18, 2013 8:09 AM by Paul Horth RSS

    delete millions of rows and fragmentation

    873236
      Hi Gurus,

      i have deleted 20 lak rows from a table which has 30 lak rows,

      and i wanted to release the fragmented space , please tell me the procedure other than exp/imp or alter table move

      and also the recommended way to do this prod env... (coalesce /alter move etc.. )

      db version is 11.2

      Thanks for great help
      Raj
        • 1. Re: delete millions of rows and fragmentation
          Karthick_Arp
          Use [url http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_redefi.htm#CBBJJAIF]DBMS_REDEFINITION to perform Online redefinition of a table.
          • 2. Re: delete millions of rows and fragmentation
            Purvesh K
            870233 wrote:
            Hi Gurus,

            i have deleted 20 lak rows from a table which has 30 lak rows,

            and i wanted to release the fragmented space , please tell me the procedure other than exp/imp or alter table move

            and also the recommended way to do this prod env... (coalesce /alter move etc.. )

            db version is 11.2

            Thanks for great help
            Raj
            Instead of Deleting 2 Million rows out of 3 Million, I would suggest Creating a Temporary table with data that should be retained and Dropping the original table.

            I believe, this will amount to lesser work.

            Steps would be like below:
            1. Create table your_table_temp as select * from your_table where condition to retain records;
            2. Drop table your_table;
            3. Alter table your_table_temp rename to your_table;

            You might as well want to exploit the advantage provided by the NOLOGGING while loading your temp table.
            • 3. Re: delete millions of rows and fragmentation
              Gurjeet
              methods:

              1. recreate your table (create table as select * from table_name) and drop the table
              2.export the table and then drop it and finally import it.
              3.use this
              alter table table_name enable row movement;
              alter table table_name move;
              alter table table_name disable row movement;
              and then "gather statistics"


              after movement you have to recreate your indexes (normal , function based index) related to your table


              from my side - 2 method is best
              • 4. Re: delete millions of rows and fragmentation
                Billy~Verreynne
                870233 wrote:

                i have deleted 20 lak rows from a table which has 30 lak rows,
                What is a lak? This is an English international forum, so please use English international terminology and measures. I do not talk about a "+100 two-bob rows+" (two-bob is a very South African term). So do not use lak.

                And please do not bother telling me what a lak is - I have been told numerous times, but as I never use that, I never remember that. :-)
                and i wanted to release the fragmented space , please tell me the procedure other than exp/imp or alter table move
                Why? The space made available will be reused for that table.

                Why do you want to "pack" the existing table data to forcible rewrite the entire table? What will you achieve by that?

                If there is a problem, please explain the problem.
                • 5. Re: delete millions of rows and fragmentation
                  BEDE
                  Hope you do not wish to do such a thing on a database that has to be up and running every day and accessed by more that a few dozens of users...
                  The best is to have tables wisely partitioned and use truncate partition or eventually drop partition to free storage space.
                  Rename table and then recreate the table and insert select into the recreated table would be the way I'd go had I not to do with a system that has to be running almost 24X7 (only a few hours downtime in week-end when, as far as I know, a cold backup is made).
                  • 6. Re: delete millions of rows and fragmentation
                    Gurjeet
                    i dis not posted this thread... but i can pass the message to you

                    *"lak is lakh" and "1 Million = 10 Lakh (i.e. 1000000) "*
                    • 7. Re: delete millions of rows and fragmentation
                      Paul  Horth
                      Mr. singh wrote:
                      i dis not posted this thread... but i can pass the message to you

                      *"lak is lakh" and "1 Million = 10 Lakh (i.e. 1000000) "*
                      Which bit of Billy's message did you not read? I guess it was

                      >
                      And please do not bother telling me what a lak is - I have been told numerous times, but as I never use that, I never remember that. :-)
                      >

                      The point is that this is an international forum, people should not be using a South Asian numbering system that is not well-known outside
                      of that region.