Jump to content


  • Content count

  • Joined

  • Last visited

Community Reputation

0 Neutral

1 Follower

About Basavaraj

  • Rank
  1. Hi, 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) interval(100) ( 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); begin execute IMMEDIATE 'ALTER TABLE TEST TRUNCATE PARTITION FOR '||'('||yearmonth||')''; --execute IMMEDIATE 'ALTER TABLE TEST TRUNCATE PARTITION FOR '||'('||p_monthly||')''; end; 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. Thanks, Basavaraj