Sql

[spidereddy]

To the people who are curious to know the solution for the sql partition renaming..

I have the solution here..

 

People who have worked on partitioning, might have known  about List,Hash,Range partitions..

There is also another type of partition called Interval Partitioning,which is a new feature of Oracle 11g database..

 

Interval Partitioning is like an extension to Range Partition..But, in range partition we have to declare the ranges for the partitions beforehand..(like partition <partition_name> values less than <date>)

 

So, if we want to create Partition for a table on a monthly basis,we have to declare 12 partitions while creating the partition table. (Check the syntax for Range Partitioning)

 

In case of Interval partitioning, we need not define partition names while creating the partition table..Instead, we can create the table with JUST one partition..and whenever we try to insert the data into partition table in the next interval..oracle will detect that interval and creates the partition for that data and a system generated partition will be created..

 

The below is used to create a month partition..(google for full syntax on creating the interval-partitioning partition table)

interval(numtoyminterval(1,'MONTH'))

We can even define this interval to be DAY or MONTH or YEAR or even a min...

 

Creating a partition table:

 

create table partition_table

(

emp_key number,

emp_num number,

rfrsh_dt date, 

)

partition by range (rfrsh_dt)

interval(numtodsinterval(1, 'DAY'))

(

partition p_20141209 values less than (to_date('20141209','YYYYMMDD'))

);

 

In this example..I have created a partition table for a daily interval..and named the first partition as DAY_1..

So, when I insert the data into this table next day and following day and day after..oracle will create system generated partition like SYS_P1,SYS_P2,SYS_P3....so on..

 

Generally, the name of the partition doesn't matter..But in my scenario, I have to rename the partition in my desired format, P_<sysdate> after inserting the data into the table..

 

Renaming the partition names..

 

set serveroutput on;

using dbms_output;

declare    

default_name varchar2(10); 

begin

   for X in (select partition_name, high_value from dba_user_partitions  where table_name= 'partition_table' and partition_name not like 'P_%')

LOOP

    execute immediate 'select to_char('||X.high_value||'-1,''YYYYMMDD'') from dual' into default_name;

    dbms_output.put_line('alter table partition_table rename partition '||X.partition_name||' to '||'P'||'_'||default_name;

END LOOP;

END;

 

With the above logic we can rename the partition name of the table..

 

The concept of renaming partitions will come into place when we want to drop the partitions after certain period of time..(in my case it is 60 days)

So that we can easily add and drop partitions..instead of truncating and inserting the data into same partitions..

 

If you have any questions contact me..

[Captain_nd_Coke]

Gp

[spidereddy]

C

[mettastar]

good post Jessie

[suryausa]

thanks for sharing info spider ..... appudappudu ilantivi vestundu

[Silver_mani]

gp man 

[ronitreddy]

gp

[Silver_mani]

gp

ok

[Tadika]

[ronitreddy]

ok

eyyyyyyyyy

[Silver_mani]

eyyyyyyyyy