Insert/delete Partition

Hi,
I am searching in google for a pl/sql script that will purge old partitions and insert a new partition based on the date. (ie. keep 24 months, each month insert a new partition and delete the old partition(24),

I have not found anything in google and thought maybe someone might have a similar script that I can use as a base.

Your help is greatly appreciated.

Thanks,

Assuming from your posting you are resorting to range-partitioning...

You do not insert a new partition, you usually split the max-value partition.
You do not delete a partition, you either truncate it or drop it.

Ora10g might help you creating new partitions as needed but, don't be lazy, you can write the script. Just google "alter table drop partition" and "alter table split partition"

Pascal,

Is it realy so hard to read the Oracle documentation and to write 2 lor 3 lines of code ???

I gave you the links of all the documents you would need to do it:
http://www.dbasupport.com/forums/showthread.php?t=55723

this proc drop part based on value.........u can modify with ur req...i dont know wather its helpfull for u or not:confused:

-----CREATE A PARTITION TABLE.

SQL> ED
Wrote file afiedt.buf

1 CREATE TABLE p_history (
2 prof_history_id NUMBER(10),
3 person_id NUMBER(10) NOT NULL,
4 organization_id NUMBER(10) NOT NULL,
5 record_date DATE NOT NULL,
6 ph_comments VARCHAR2(2000))
7 PARTITION BY RANGE (record_date) (
8 PARTITION YEAR1
9 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
10 PARTITION YEAR2
11 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
12 PARTITION YEAR3
13 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')),
14 PARTITION YEAR4
15* VALUES LESS THAN (MAXVALUE))
16 /

Table created.


----SEE THE PARTITION CREATED.

SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME
2 FROM DBA_TAB_PARTITIONS
3 WHERE TABLE_NAME='P_HISTORY';

TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT P_HISTORY YEAR1
SCOTT P_HISTORY YEAR2
SCOTT P_HISTORY YEAR3
SCOTT P_HISTORY YEAR4


----CREATE A PROCEDURE FOR DROP PARTITION.

SQL> ED
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE DROP_PART(P_OWNER VARCHAR2, P_TABLE VARCHAR2, P_PARTITION VARCHAR2)
2 IS
3 V_STAT VARCHAR2(300);
4 BEGIN
5 V_STAT := 'ALTER TABLE '||P_OWNER||'.'||P_TABLE||' DROP PARTITION '||P_PARTITION||' UPDATE GLOBAL INDEXES';
6 EXECUTE IMMEDIATE V_STAT;
7 DBMS_OUTPUT.PUT_LINE('Owner:'||P_OWNER||' Table:'||P_TABLE||' Partition:'||P_PARTITION||' has droped.');
8 EXCEPTION
9 WHEN NO_DATA_FOUND THEN
10 DBMS_OUTPUT.PUT_LINE('Partition is not found.');
11 WHEN OTHERS THEN
12 DBMS_OUTPUT.PUT_LINE(SQLERRM);
13* END;

Procedure created.

----RUN THE PROCEDURE.

SQL> SET SERVEROUTPUT ON
SQL> EXEC DROP_PART('SCOTT', 'P_HISTORY', 'YEAR2');
Owner:SCOTT Table:P_HISTORY Partition:YEAR2 has droped.

PL/SQL procedure successfully completed.


---SEE THE RESULT.

SQL> SELECT TABLE_OWNER,TABLE_NAME,PARTITION_NAME
2 FROM DBA_TAB_PARTITIONS
3 WHERE TABLE_NAME='P_HISTORY';

TABLE_OWNER TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------ ------------------------------
SCOTT P_HISTORY YEAR1
SCOTT P_HISTORY YEAR3
SCOTT P_HISTORY YEAR4


---PARTITION (YEAR 2) IS DROPED.

Quote:

---

Originally Posted by liakat

this proc drop part based on value

---

No it doesn't. It drops partition based on partition_name.

Hi.
"Is it realy so hard to read the Oracle documentation and to write 2 lor 3 lines of code ???" Mike9, I had no problem creating the partitioned table, that was not the issue for me. It was writing the pl/sql to loop and remove the oldest partition.

Liakat. Thanks for the terrific example. I will use it as my baseline. Much appreciated.

Cheers.

Quote:

---

Originally Posted by PAVB

No it doesn't. It drops partition based on partition_name.

---

SORRY, YES It drops partition based on partition_name.
I thing i need more carefull.

thanks prov