add a new partition and drop old partition !!!

From: sansar <uemit.uenlue_at_googlemail.com>
Date: Sat, 20 Oct 2007 05:50:05 -0700
Message-ID: <1192884605.436447.95290@y27g2000pre.googlegroups.com>

Hallo !
I have write a pl/sql-script. but i don't get a solution. i want add a new partition. And from the new added partition make - 8 to find a old partition. when i find this partition drop this partition.

my script:

declare
-- Local variables here

v_date_string VARCHAR2(20);
v_part VARCHAR2(20);
l_statement VARCHAR2(2000);
v_create_part VARCHAR2(200);
v_drop_part VARCHAR2(20);
v_exists_part VARCHAR2(20);
v_date VARCHAR2(20);

tmp VARCHAR2(2000);

CURSOR context_date_cur IS
Select distinct ext_frontend.context_date from ext_frontend
where context_date not in
(select substr(PARTITION_NAME,6,10)
from dba_tab_partitions where table_name='FRONTEND_TAB') order by context_date asc;

v_date_add_part context_date_cur%ROWTYPE;

CURSOR exists_drop_partion_cur IS
select dba_tab_partitions.partition_name from dba_tab_partitions;

v_date_drop_part exists_drop_partion_cur%ROWTYPE;

begin
-- Test statements here

IF NOT context_date_cur%ISOPEN
THEN
OPEN context_date_cur;
END IF; loop
fetch context_date_cur into v_date_add_part; exit when context_date_cur%NOTFOUND;

v_create_part :='part_'||v_date_add_part.context_date;
v_date:=to_char(1+to_date(v_date_add_part.context_date,'dd.mm.yyyy'),'DD.MM.YYYY');
l_statement:= 'alter table FRONTEND_TAB

add partition "'||v_create_part||'" values less than (TO_DATE('''|| v_date||''','''||'DD.MM.YYYY'||'''))
STORAGE( INITIAL 1048576 NEXT 1048576 PCTINCREASE 0 ) TABLESPACE "LOG_ANALYSE"
NOLOGGING '; DBMS_OUTPUT.put_line(l_statement);
execute immediate l_statement;

-----------------------------this section drop the finding partition,

when not then go out from the for-

---

v_date:=to_char(8-
to_date(v_date_add_part.context_date,'dd.mm.yyyy'),'DD.MM.YYYY'); DBMS_OUTPUT.put_line(v_date);

FOR v_date_drop_part in exists_drop_partion_cur Loop
IF v_date_drop_part.partition_name='part_'||v_date THEN
DBMS_OUTPUT.put_line(' Gefunden !!!');
ELSE
DBMS_OUTPUT.put_line('Nicht gefunden !!!'); End IF;

End Loop;
end loop;
end;

i think the problem in this statement IF v_date_drop_part.partition_name='part_'||v_date

????

Thank you for very much Received on Sat Oct 20 2007 - 07:50:05 CDT