Jump to content

Getting partition name dynamically based on partition key value

Recommended Posts



I have been working on one task here converted non partitioned table into range - interval partition table .This table consists of 20 million records hold monthly data.

The process used previously was deleting and inserting into  the non -partitioned table , after converting it into partition table and to improve performance truncating table partition and inserting into table.

Each monthly data  holds 2 millions records, currently i am doing truncation based on partition key . Please find below structures of table and statements.

create table test(yearmonth number,
                  custname varchar2(30),
                  bal_amnt number(8,0) 
    PARTITION by range (yearmonth)
    ( partition p_monthly values less than (20151101 ) );
    insert into test values (20151024,'xyz',1000);
    insert into test values (20151024,'abc',2000);
    insert into test values (20151110,'def',5000);
    insert into test values (20151110,'srt',6000);

     execute IMMEDIATE 'ALTER TABLE TEST TRUNCATE PARTITION FOR '||'('||yearmonth||')'';
     --execute IMMEDIATE 'ALTER TABLE TEST TRUNCATE PARTITION FOR '||'('||p_monthly||')''; 
 Please see commented code above can I get partition name as parameter to dynamic statement based on partition key for ex 20151024 -p_monthly.

Data dictionary user_tab_partitions holds partitions name and high_value but high_value data type of long type so was not able to fetch the partition name.

I searched lot but not able to find solution,could you please guide me the correct approach , I hope  you are clear with my question if need any input let me know.

if currently used method passing partition key for truncation correct way then implement the same solution.









Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now