Sql Help Pls

[spidereddy]

i have a partition table with 60 partitions..

everyday when i load the data into this partition table..oracle will create a default partition name and stores the data into that partition..

 

after loading the data into partition table..i want to rename the partition name..

 

 

can anyone help me in writing a function for that..

 

kindha query tho oracle default partition_name osthadhi..(ex: sys_212,sys_213,sys_214 etc)

 

select * from dba_tab_partitions where table_name = 'TABLE_A' and partition_name like '%SYS%';

 

aa default name ni na naming convention tho rename cheyali..

my naming is like.. 

part_20141209--(today's partition name),

part_20141210--(tomm's partition name)

so on..

 

alter table TABLE_A rename partition sys_212 to part_20141208; --I can do this if I know the default partition name

 

but the function should pick up the default partition name and alter it to desired format..

[spidereddy]

Ltt

[DaleSteyn1]

sql server lo aithe rename cheyalem drop and recreate cheyali oracle gurinchi experts ochi cheppandi

[suryausa]

ltt for spide reddy 

[ipaddress0]

ltt

[suryausa]

CREATE OR REPLACE FUNCTION partition_details_func(default_partition)

RETURN VARCHAR2(20)

IS

local_default_partition VARCHAR2(60);

local_date_hold VARCHAR2(40);

changed_partition_name VARCHAR2(60);

BEGIN

BEGIN

SELECT partition_name

    INTO  local_default_partition

   FROM dba_tab_partitions

WHERE table_name = 'TABLE_A' and partition_name like '%SYS%';

EXCEPTION

WHEN_OTHERS THEN

Raise_application_error(handle it );

END;

BEGIN

// this is logic

SELECT TO_DATE(to_char(sysdate,’MM/DD/YYYY’),’MM/DD/YYYY’)

     INTO local_date_hold

   FROM dual;

EXCEPTION

WHEN_OTHERS THEN

Raise_application_error(handle it );

END;

/////// Here append your ‘part_’ to local_date_hold variable

SELECT ‘part_’|| local_date_hold

INTO changed_partition_name

FROM DUAL; //need to check syntax

BEGIN

//////// do this here

alter table TABLE_A rename partition sys_212 to part_20141208

END;

//EXCEPTION_HANDLING

 

 

 

END;

[suryausa]

i can work on this if you reply

[suryausa]

if this is not correct just ignore 

[suryausa]

we dont need function parameter here

[MeetFriendz]

hmm

[suryausa]

spider nachithe thisko nachakapothe vodileyi kaani oka reply ivvu

[loveindia]

There is nothing like Renaming a partition man... what you have to do is, (this is a working example)

 

Let it be sql server / oracle steps are same...

 

Step 1 ) First you have to create a list of partitions using the partition range functions with your datekey, which means partitions like 20141201, 20141202, 20141203.... and so on will be created, upto a desired number as per your company. In My solution I created for the next four years.

 

Step 2) Once you load data using the datekey, it will automatically inserted into the partition based on that datekey.

 

Step 3) There is no need to truncate the table  or use an insert statement to insert into another table... If SQL Server, there is a keyword called SWITCH which you will use with ALTER TABLE to switch the partition to an identical destination table. Now your destination table will have the data and your staging source will be emptied in a flash... (Remember, if there is any select statement running on the source table, partition switch will be locked and cannot happen unless the lock is released).

 

or for Oracle.

 

Step 3) There is a keyword called EXCHANGE PARTITION which you will use with ALTER TABLE.

 

This way the data is transferred faster between two tables which are partitioned... If you have more questions let me know man....

[spidereddy]

There is nothing like Renaming a partition man... what you have to do is, (this is a working example)

 

Let it be sql server / oracle steps are same...

 

Step 1 ) First you have to create a list of partitions using the partition range functions with your datekey, which means partitions like 20141201, 20141202, 20141203.... and so on will be created, upto a desired number as per your company. In My solution I created for the next four years.

 

Step 2) Once you load data using the datekey, it will automatically inserted into the partition based on that datekey.

 

Step 3) There is no need to truncate the table  or use an insert statement to insert into another table... If SQL Server, there is a keyword called SWITCH which you will use with ALTER TABLE to switch the partition to an identical destination table. Now your destination table will have the data and your staging source will be emptied in a flash... (Remember, if there is any select statement running on the source table, partition switch will be locked and cannot happen unless the lock is released).

 

or for Oracle.

 

Step 3) There is a keyword called EXCHANGE PARTITION which you will use with ALTER TABLE.

 

This way the data is transferred faster between two tables which are partitioned... If you have more questions let me know man....

 

Thanks..but i think you didn't get my ques..

 

If I create a partition table like below..

 

CREATE TABLE PARTITION_TABLE
(
EMP_KEY NUMBER,
EMP_NUM NUMBER,
RFRSH_DT DATE,
)
PARTITION BY RANGE (RFRSH_DT)
INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
(
PARTITION PART_20141209 values LESS THAN (TO_DATE('20141209','YYYYMMDD'))
);

 

with one partition named as PART_20141209..next day when I insert data again..oracle will create a partition again..but not with PART_20141210 name..it will create with some default name..like SYS_212..

 

I want that to be renamed..

 

I'm thinking with a function we can do that..by detecting the newly created partition and substitute it with 'part'||'_'||'to_char(sysdate,YYYYMMDD')..something like this.. 

[spidereddy]

spider nachithe thisko nachakapothe vodileyi kaani oka reply ivvu

thanks man.. i just got home..will try and let u know..

[loveindia]

Thanks..but i think you didn't get my ques..

If I create a partition table like below..

CREATE TABLE PARTITION_TABLE
(
EMP_KEY NUMBER,
EMP_NUM NUMBER,
RFRSH_DT DATE,
)
PARTITION BY RANGE (RFRSH_DT)
INTERVAL(NUMTODSINTERVAL(1, 'DAY'))
(
PARTITION PART_20141209 values LESS THAN (TO_DATE('20141209','YYYYMMDD'))
);

with one partition named as PART_20141209..next day when I insert data again..oracle will create a partition again..but not with PART_20141210 name..it will create with some default name..like SYS_212..

I want that to be renamed..

I'm thinking with a function we can do that..by detecting the newly created partition and substitute it with 'part'||'_'||'to_char(sysdate,YYYYMMDD')..something like this..

I think the way you are approaching is not correct man.. when you create table today with a name, a table is created.. how can you create the same table tomorrow if it exists in the database even though they have different partition scheme names.. think of that.. I will post a link related to partitioning in Oracle in a bit.. good luck