6 Replies Latest reply: Jan 4, 2013 10:33 AM by rp0428 RSS

    Partiton maxrange value for a table

    User444780-OC
      Hi Gurus,

      Oracle version : 11.2.0.3
      Operating System: solaris

      Here we need a query how to get the max-range value for a partitioned table.

      Can any one please help me how to get it from all_tab_partitions.

      THanks & Regards,
      Venkata Poorna Prasad.S
        • 1. Re: Partiton maxrange value for a table
          vlethakula
          You can use HIGH_VALUE or PARTITION_POSITION

          select partition_name, partition_position, high_value from user_tab_partitions;
          • 2. Re: Partiton maxrange value for a table
            rp0428
            >
            Here we need a query how to get the max-range value for a partitioned table.

            Can any one please help me how to get it from all_tab_partitions.
            >
            Since you know about all_tab_partitions and the information you need is in the HIGH_VALUE column perhaps your question is how to deal with that column since it is a LONG datatype.

            Perhaps the easiest way is to use PL/SQL and read the data. PL/SQL can work with LONG datatype values. You can use the TO_LOB function to convert the LONG to a CLOB in an INSERT statement or in PL/SQL. Then you can work with the CLOB version.

            If you tell uls what you are really trying to do you will get more specific help.

            If you just want to see how the partitions are defined just use the DBMS_METADATA package to extract the table DDL
            select dbms_metadata.get_ddl('TABLE', '--put tablename here--', '--put schema name here--') from dual
            • 3. Re: Partiton maxrange value for a table
              User444780-OC
              Hi rp0428,

              I have a number of tables with range by date partitions. I need to write a script to monitor the latest partitions and send me an e-mail if it's close to a current date.

              SQL> select max(partition_position),table_name from all_tab_partitions where table_owner='SSP_FLOW' 
              group by table_name;
              
              MAX(PARTITION_POSITION) TABLE_NAME
              ----------------------- ------------------------------
                                   12 EMAIL_XML_NOTIFICATION_DETAILS
                                   58 SSP_BLOB_RESPONSE_MESSAGE
                                   12 SSP_CLOB_REQUEST_MESSAGE
                                    9 NON_SSP_BLOB_MESSAGE
                                   12 EMAIL_DYNA_VARIABLE_DETAILS
                                   27 NON_SSP_CLOB_MESSAGE
                                   12 SSP_CLOB_RESPONSE_MESSAGE_TEMP
                                   12 WS_THREAD_TRACK
                                   12 SSP_OB_SERVICE_DETAILS
              
              9 rows selected.
              
              
              SQL> select high_value,partition_name from all_tab_partitions where partition_position in ( select m
              ax(partition_position),table_name from all_tab_partitions where table_owner='SSP_FLOW' group by tabl
              e_name);
              select high_value,partition_name from all_tab_partitions where partition_position in ( select max(pa
                                                                                                     *
              ERROR at line 1:
              ORA-00913: too many values
              Here i am looking to get all the partitions high_value based on the max(partition_position ).

              Please help me how to solve the problem.

              Thanks & Regards,
              Venkata Poorna Prasad.S
              • 4. Re: Partiton maxrange value for a table
                rp0428
                >
                SQL> select high_value,partition_name from all_tab_partitions where partition_position in ( select m
                ax(partition_position),table_name from all_tab_partitions where table_owner='SSP_FLOW' group by tabl
                e_name);
                select high_value,partition_name from all_tab_partitions where partition_position in ( select max(pa
                *
                ERROR at line 1:
                ORA-00913: too many values
                >
                You have an IN clause but your subquery selects both max(partition_position) and table_name - that won't work.
                with max_partitions as (
                select table_name, max(partition_position) max_part
                from all_tab_partitions where table_owner = 'SSP_FLOW'
                group by table_name)
                select atp.high_value, atp.table_name, atp.partition_name, atp.partition_position
                from all_tab_partitions atp, max_partitions mp
                where atp.table_name = mp.table_name
                  and atp.partition_position = mp.max_part
                • 5. Re: Partiton maxrange value for a table
                  User444780-OC
                  Hi rp0428,


                  Thank you very mucht the below query is very help full to me.

                  Thanks & Regards,
                  Venkata Poorna Prasad.S
                  • 6. Re: Partiton maxrange value for a table
                    rp0428
                    Glad it helped - please mark the question ANSWERED and help keep the forum clean.