CREATE TABLE TRAN_TB
(
TXN_ID VARCHAR2(20),
CUST_MOB_NO VARCHAR2(20),
TXN_DT TIMESTAMP (6) DEFAULT SYSTIMESTAMP
)
TABLESPACE users
PARTITION BY RANGE (TXN_DT) INTERVAL (NUMTODSINTERVAL( 1, 'DAY' ))
(PARTITION PT_TXN VALUES LESS THAN (TIMESTAMP' 2020-01-01 00:00:00')
TABLESPACE users ) ;
CREATE TABLE TRAN_HIST
(
TXN_ID VARCHAR2(20),
CUST_MOB_NO VARCHAR2(20),
TXN_DT TIMESTAMP (6) DEFAULT SYSTIMESTAMP
);
REM INSERTING into TRAN_TB
SET DEFINE OFF;
Insert into TRAN_TB (TXN_ID,CUST_MOB_NO,TXN_DT) values ('133422111012','123456789',to_timestamp('20-AUG-21 10.00.48.812133000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TRAN_TB (TXN_ID,CUST_MOB_NO,TXN_DT) values ('133422111013','123456789',to_timestamp('24-DEC-21 10.00.58.818269000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TRAN_TB (TXN_ID,CUST_MOB_NO,TXN_DT) values ('133422111014','123456789',to_timestamp('25-DEC-21 10.01.04.582363000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TRAN_TB (TXN_ID,CUST_MOB_NO,TXN_DT) values ('133422111024','123456789',to_timestamp('20-MAR-21 10.00.48.812133000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into TRAN_TB (TXN_ID,CUST_MOB_NO,TXN_DT) values ('133422111095','123456789',to_timestamp('20-JUN-21 10.00.48.812133000 PM','DD-MON-RR HH.MI.SSXFF AM'));
COMMIT;
---Tried Proc
create or replace procedure EXCH_PROC
as
cursor c1
IS
SELECT partition_name,high_value
FROM user_tab_partitions
WHERE table_name = 'TRAN_TB'
and partition_name<>'PT_TXN';
TYPE CUR_PART
IS
TABLE OF c1%rowtype INDEX BY PLS_INTEGER;
v_array CUR_PART;
V_ERRMSG VARCHAR2(255);
l_date DATE;
begin
open c1;
loop
fetch c1 bulk collect into v_array;
exit when v_array.count=0;
for i in 1..v_array.count
loop
V_ERRMSG:='OK';
EXECUTE IMMEDIATE 'BEGIN :v := ' || v_array(i).high_value || '; END;' USING OUT l_date;
if l_date < (sysdate-35) then
begin
execute immediate 'ALTER TABLE TRAN_TB EXCHANGE PARTITION '||v_array(i).PARTITION_NAME||' WITH TABLE TRAN_HIST WITHOUT VALIDATION ';
execute immediate 'alter table TRAN_TB DROP PARTITION '||v_array(i).PARTITION_NAME||'';
end;
END IF;
end loop;
commit;
end loop;
close c1;
end;
Here only 20-Jun-21 Data is moved to TRAN_HIST table and dropped in TRAN_TB table.
20th march and 20 th august data is not moved to tran_hist table and dropped from tran_tb table,and facing data lost when we are doing exchange partition.
Please help