11 Replies Latest reply on Apr 25, 2012 9:06 PM by JustinCave

    Interval Partition naming Issue (Oracle 11g R2 )

    610131
      I need help on identifying latest partition:

      I am using Interval Partition for my table,which creates partition every month end based on inserted data.
      When oracle creates partition assigning its own name but users have automated reports using partition names like Table_name_YYYYMM.

      I tested following ways to identify partition then renamed,worked fine but in long run do i get any problems ?
      or any other way to identify the latest partition ?

      1)Using max partition position :
      select partition_name from dba_tab_partitions a where partition_position = (select max(partition_position)
      from user_tab_partitions b where a.table_name=b.table_name
      and b.table_name = 'INTRVL_PARTITION');

      2)Using Sub Object max creation date :
      SELECT SUBOBJECT_NAME FROM dba_objects
      WHERE OBJECT_TYPE='TABLE PARTITION'
      AND OWNER='ABCD'
      AND OBJECT_NAME='INTRVL_PARTITION'
      AND CREATED=(SELECt MAX(CREATED) FROM DBA_OBJECTS
      WHERE OBJECT_TYPE='TABLE PARTITION'
      AND OWNER='ABCD'
      AND OBJECT_NAME='INTRVL_PARTITION');


      Thanks in advance .

      Edited by: user607128 on Mar 2, 2012 7:09 AM
        • 1. Re: Interval Partition naming Issue (Oracle 11g R2 )
          sb92075
          user607128 wrote:
          I need help on identifying latest partition:

          I am using Interval Partition for my table,which creates partition every month end based on inserted data.
          When oracle creates partition assigning its own name but users have automated reports using partition names like Table_name_YYYYMM.

          I tested following ways to identify partition then renamed,worked fine but in long run do i get any problems ?
          or any other way to identify the latest partition ?

          1)Using max partition position :
          select partition_name from dba_tab_partitions a where partition_position = (select max(partition_position)
          from user_tab_partitions b where a.table_name=b.table_name
          and b.table_name = 'INTRVL_PARTITION');

          2)Using Sub Object max creation date :
          SELECT SUBOBJECT_NAME FROM dba_objects
          WHERE OBJECT_TYPE='TABLE PARTITION'
          AND OWNER='ABCD'
          AND OBJECT_NAME='INTRVL_PARTITION'
          AND CREATED=(SELECt MAX(CREATED) FROM DBA_OBJECTS
          WHERE OBJECT_TYPE='TABLE PARTITION'
          AND OWNER='ABCD'
          AND OBJECT_NAME='INTRVL_PARTITION');


          Thanks in advance .

          Edited by: user607128 on Mar 2, 2012 7:09 AM
          It looks OK to me.
          • 2. Re: Interval Partition naming Issue (Oracle 11g R2 )
            610131
            Thanks for your quick reply

            Which one is the best method from below ?

            using max created date OR max partition position ?


            Thanks
            • 3. Re: Interval Partition naming Issue (Oracle 11g R2 )
              JustinCave
              Why would a report ever specify a particular partition name in the first place? It would be much easier to simply specify a predicate on the DATE column that the table is partitioned on and to let Oracle do the appropriate partition pruning.

              Justin
              • 4. Re: Interval Partition naming Issue (Oracle 11g R2 )
                >
                Which one is the best method from below ?

                using max created date OR max partition position ?
                >
                For an interval partitioned DB use max partition position to identify the last one created; but just use USER_TAB_PARTITIONS.
                USER_TAB_PARTITIONS has all of the information you need AND the user/schema doesn't need privileges on the DBA view.

                Position will always give you the last partition for interval.

                The option based on the date the partition was created could get the wrong one if a backup/restore or partition exchange is done at some point.
                • 5. Re: Interval Partition naming Issue (Oracle 11g R2 )
                  610131
                  -----------------------------------------------------------------------------------------------------------------
                  Why would a report ever specify a particular partition name in the first place? It would be much easier to simply specify a predicate on the DATE column that the table is partitioned on and to let Oracle do the appropriate partition pruning.

                  Justin
                  -----------------------------------------------------------------------------------------------------------------

                  Justin,

                  Thanks for your reply.

                  Corporate wide they use partition naming standards.


                  Thanks
                  • 6. Re: Interval Partition naming Issue (Oracle 11g R2 )
                    JustinCave
                    But why?

                    Explicitly specifying partition names is more fragile. It requires the query tools to dynamically assemble SQL statements making them subject to SQL injection attacks. It generates non-sharable SQL in the database. It's all-around far less elegant than omitting the partition name and letting Oracle prune the partitions appropriately.

                    If you really must refer to partitions directly, could you at least use the new syntax
                    SELECT *
                      FROM table_name PARTITION FOR (to_date( '01/01/2012', 'MM/DD/YYYY' ))
                    Oracle explicitly introduced that so that you wouldn't have to worry about the system-generated partition names when you adopted interval partitioning.

                    Justin
                    • 7. Re: Interval Partition naming Issue (Oracle 11g R2 )
                      Justin - thanks for providing that. I wasn't aware of that syntax. There seem to be several recent questions trying to get the date ranges out of the long HIGH_VALUE column of ALL_TAB_PARTITIONS and that syntax and use should obviate the need for that method.
                      • 8. Re: Interval Partition naming Issue (Oracle 11g R2 )
                        610131
                        thank you all for replies.

                        My table is already existing and having 60 partitions,each has 18 million rows,now users asking to convert Range to Interval Partition and keeping existing partitions same name.

                        I believe when i convert to Interval partition, Oracle change names for existing partitions names also.

                        What's the best way to achieve this ?



                        Thanks
                        • 9. Re: Interval Partition naming Issue (Oracle 11g R2 )
                          Your initial question said you were already using interval partitioning.
                          >
                          I am using Interval Partition for my table
                          . . .
                          now users asking to convert Range to Interval Partition and keeping existing partitions same name.
                          >
                          So is your issue that you want to use interval partitining for a table now is now using range partitioning? Why are your users driving this change? That should be a decision made by the DBA and technical management since the difference is mainly on of management.

                          Though there is one BIG difference that is data related. With interval partitioning if data (even erroneous data) is inserted for a partition that does not exist Oracle will create one. So if you have an insert statement that inserts 12 records for the year '2038' you will get 12 new partitions even though the data may be bogus.

                          Now your management problem is detecting the problem, deleting the data (or fixing the date to move it to the right partition) and then dropping the partitions and storage.

                          With 'interval' partitioning you had better be absolutely sure your data is clean in terms of the partitioning key values.

                          That said, you can use table redefinition or just create a new interval partitioned table and do partition exchanges with the existing partitions to move the data.
                          • 10. Re: Interval Partition naming Issue (Oracle 11g R2 )
                            Vitaliy Orbidan
                            Justin,
                            I believe the original question was about selecting from the latest partition, so your example will work just fine for selecting from the specific partition, but not the latest one.

                            Thank you,

                            Vitaliy
                            • 11. Re: Interval Partition naming Issue (Oracle 11g R2 )
                              JustinCave
                              The original question was about finding the latest partition with the intention of renaming it so that subsequent queries could use the "correct" partition name. My point is that the subsequent queries should never depend on the name of the partition. They should specify a predicate on the partition key and let Oracle prune the partitions. Or they should use the PARTITION FOR syntax. Relying on partition names for logic tends to be highly fragile-- one of the benefits of interval partitioning is that there is no longer any reason to consider coding that sort of logic.

                              Justin