6 Replies Latest reply on Mar 29, 2019 7:19 AM by 3842403

    sub partition on table

    3842403

      Hi Gurus,

       

      we are using  oracle 12c. I have 300 + tables and will be going to create sub  partition on monthly bases..  we need to drop data on  every month because table should have only last 1 years data.

       

      i am going to create package with below steps. could you please help me here by provide best your valuable inputs.

       

      here is my flow.

       

      1. will check is there any job is running.. if running .. will check every 10 mints until all jobs completed..

       

      2. will disable all jobs

       

      3. drop all sub partition 's

       

      4. re build all indexes  on all tables

       

      5. enable jobs

       

      Thanks.

        • 1. Re: sub partition on table
          JohnWatson2

          Why rebuild indexes? If there are any orphaned keys, they will be removed by the autotask overnight. Or launch the process manually if for some reason you want to do it sooner.

          • 2. Re: sub partition on table
            3842403

            we have primary indexes on table.. once drop sub partition .. we need to re build index am i correct..

            • 3. Re: sub partition on table
              JohnWatson2

              No, you are not correct. Look at your 12.x new features, and you will see.

               

              By the way, there is something wrong with your keyboard: it inserts ".." when you mean"."

              • 4. Re: sub partition on table
                3842403

                could you please provide any link. that's very help full for me..

                • 5. Re: sub partition on table
                  AndrewSayer

                  3842403 wrote:

                   

                  Hi Gurus,

                   

                  we are using oracle 12c. I have 300 + tables and will be going to create sub partition on monthly bases.. we need to drop data on every month because table should have only last 1 years data.

                   

                  i am going to create package with below steps. could you please help me here by provide best your valuable inputs.

                   

                  here is my flow.

                   

                  1. will check is there any job is running.. if running .. will check every 10 mints until all jobs completed..

                   

                  2. will disable all jobs

                   

                  3. drop all sub partition 's

                   

                  4. re build all indexes on all tables

                   

                  5. enable jobs

                   

                  Thanks.

                  Why do this with subpartitions when you haven’t even mentioned a partitioning scheme?

                  Checking for jobs running and disabling them is a fools game, just make sure your DDL waits patiently to get locks, once it’s got a lock on the table it will only be blocking some statements from running (for a short amount of time) they should also be handled fine unless you have jobs doing DDL too.

                  Definitely use the update global indexes clause to make sure global indexes are maintained in the nightly maintenance job, then no need to rebuild them entirely each time.

                  • 6. Re: sub partition on table
                    3842403

                    Thanks for input. we have partitioning scheme on top of that, we have sub partitions for drop last moth data.