Skip to Main Content
  • Questions
  • best way to get high value of partitions from data dictionary

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Zhigang.

Asked: October 07, 2015 - 10:42 am UTC

Last updated: May 01, 2019 - 9:44 am UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi, Chris and Connor,

I'm trying to find the best way to get "high value" of partitions from data dictionary, in order to write a customized statistics gathering program.

As you know, the data type of "high_value" column of DBA_TAB_PARITTIONS is LONG, which is difficult to handle by SQL, even the value might be composed by several columns in case of multi-key partition.

Now, I have to use PL/SQL to fetch the LONG value into a VARCHAR variable firstly, but it's hard to handle high values of multiple keys because the separator comma exists here and there within the "high_value" column.
Another way I tried is using the "high_value" column to construct a dynamic SQL then fetch values into various variables, obviously it's expensive.

Is there any good idea? Thanks ahead.

and Chris said...

Adrian Billington discusses several options for working with longs at:

http://www.oracle-developer.net/display.php?id=430

Here's a quick example borrowed from there showing how to get this in SQL using XML conversion:

create table t (
  x date 
) partition by range (x) (
  partition p0 values less than (date'2015-01-01'),
  partition p1 values less than (date'2015-06-01'),
  partition pmax values less than (maxvalue)
);

with xml as (
  select dbms_xmlgen.getxmltype('select table_name, partition_name, high_value from user_tab_partitions where table_name = ''T''') as x
  from   dual
)
  select extractValue(rws.object_value, '/ROW/TABLE_NAME') table_name,
         extractValue(rws.object_value, '/ROW/PARTITION_NAME') partition,
         extractValue(rws.object_value, '/ROW/HIGH_VALUE') high_value
  from   xml x, 
         table(xmlsequence(extract(x.x, '/ROWSET/ROW'))) rws;

TABLE_NAME PARTITION  HIGH_VALUE                                                                               
---------- ---------- ------------------------------------------------------------------------------------------
T          P0         TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')       
T          P1         TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')       
T          PMAX       MAXVALUE

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another way to get to last partition

Dan, October 07, 2015 - 1:51 pm UTC

My guess is that you are looking for the partition name, not really wanting to deal with longs...

Step 1 - use the PARTITION FOR (xxx) construct to get a row from the desired partition

select rowid from my_date_partitioned_table for ('1-jan-2016') where rownum=1; 


Step 2. Then convert the rowid to the partition name

with z as (select 
  tbl$or$idx$part$num("scott"."my_partitioned_table",
                                0,
                                4,
                                0,
                                "ROWID") objn from my_partitioned_table tp)                                
select subobject_name , object_type from  dba_objects d,z where d.object_id = z.objn


Then use that partition name in whatever maintenance your are doing. No "magical" knowledge of partition naming conventions needed.

What you do need is knowledge of the partition key (date in this example). Also, the strings are literals, so you will be using dynamic sql. Can't use bind variable in the PARTITION FOR.
Chris Saxon
October 07, 2015 - 4:20 pm UTC

Note that tbl$or$idx$part$num is undocumented. If you want to follow this approach it's better to use dbms_rowid to identify the partition name:

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:652093600346909239

A reader, September 28, 2016 - 4:17 am UTC


Solved my problem

Nam Doan, September 28, 2016 - 4:20 am UTC

My issue is truncate some partitioned tables, and Chris's suggesstion had already help to solved my issue. Thank you!

A reader, April 18, 2018 - 3:25 pm UTC


What if ,if we want to delete the partition

Mansi, March 29, 2019 - 2:00 pm UTC

I have requirement where we keep backup of last 15days data. I used interval partition to create partition for each day but I am also deleting data of sysdate -1th day.

How to maintain the interval partition in this case,as it will pile up the date partition in database.
Chris Saxon
March 29, 2019 - 5:02 pm UTC

Then drop or truncate the partition with the data you want to remove!

Maintenance of interval partition

A reader, April 01, 2019 - 6:37 am UTC

Insert and delete of data happens with user with limited access, that user is not having access to drop partition.
Chris Saxon
April 01, 2019 - 10:12 am UTC

So delete the rows then...

extractvalue deprecated

AndyP, April 02, 2019 - 7:55 am UTC

Regarding the response to the original question, about inventive ways to deal with LONGs

https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/whatsnew.htm#ADXDB5978

Oracle Database 11g Release 2 (11.2.0.1) Deprecated Oracle XML DB Constructs

The following Oracle XML DB constructs are deprecated in Oracle Database 11g Release 2 (11.2.0.1)
They are still supported in 11.2.0.1 for backward compatibility, but Oracle recommends that you do not use them in new applications

Oracle SQL function extract – Use SQL/XML function XMLQuery instead. See "XMLQUERY SQL/XML Function in Oracle XML DB".
Oracle SQL function extractValue – Use SQL/XML function XMLTable or SQL/XML functions XMLCast and XMLQuery instead

And so, I think this would be the direct replacement for that query

with xmlform as
(
select dbms_xmlgen.getxmltype('select table_name,partition_name,high_value from user_tab_partitions where table_name=''T''') as x
  from dual
)
select xmltab.*
  from xmlform
      ,xmltable('/ROWSET/ROW'
          passing xmlform.x
          columns table_name varchar2(10) path 'TABLE_NAME'
                 ,partition  varchar2(9)  path 'PARTITION_NAME'
                 ,high_value varchar2(85) path 'HIGH_VALUE'
      ) xmltab;

TABLE_NAME PARTITION HIGH_VALUE
---------- --------- -------------------------------------------------------------------------------------
T          P0        TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
T          P1        TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
T          PMAX      MAXVALUE

Chris Saxon
April 02, 2019 - 10:39 am UTC

Great point, thanks for sharing.

A reader, April 30, 2019 - 7:20 am UTC

Regarding Chris's suggestion of deleting rows from table,
will it delete interval partition which no longer needed,

It will just pile up the partitions,right?
Chris Saxon
April 30, 2019 - 10:06 am UTC

Yes. If you delete all the rows from a partition, it's still there. Just empty.

You need to drop a partition to completely remove it.

Idea

John Keymer, May 01, 2019 - 8:46 am UTC

I posted an idea on the community forums to have a framework which automates this somewhat. It hasn't had much traction, so I doubt will be implemented, but feel free to upvote it all the same. :)

https://community.oracle.com/ideas/24831
Chris Saxon
May 01, 2019 - 9:44 am UTC

I could see that some people may want this. Thanks for sharing.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.