Archival Process - Simple Method

[raja]

Hi,

I have a 2 Schemas : PRODUCTION and ARCHIVE Schema.
I need to select particular rows ( rows of partition 2007 ) for all
the tables present in the PRODUCTION Schema and INSERT them into
ARCHIVE Schema, one by one.
Reason for doing this is : As a part of Archival Process. This method
is opted, to avoid facing any complicated issues in Production and to
track down any issues.

I have tried with the below one, is this correct ?, please help me to
proceed further :

DECLARE
COL1 VARCHAR2(10);
COL2 VARCHAR2(50);
COL3 VARCHAR2(100);
BEGIN
SELECT
DISTINCT
A.TABLE_OWNER,
A.TABLE_NAME,
A.TABLE_OWNER || '.' || A.TABLE_NAME
BULK COLLECT INTO COL1, COL2, COL3
FROM
DBA_TAB_PARTITIONS@TO_ID_OWN_MD_SEC A,
DBA_PART_TABLES@TO_ID_OWN_MD_SEC B
WHERE
A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_OWNER IN ('ID_OWN_PS', 'ID_OWN_DW', 'ID_OWN_DR',
'ID_OWN_DM')
AND A.TABLE_NAME LIKE 'ID_T%'
AND A.TABLE_NAME NOT LIKE '%BKP'
AND A.TABLE_NAME NOT LIKE '%BAK'
AND A.TABLE_NAME NOT LIKE '%TEST'
AND A.PARTITION_NAME LIKE '%2007%'

FOR i in products_tab.first .. products_tab.last LOOP

INSERT INTO COL3(i) SELECT * FROM COL3(i)@TO_ID_OWN_MD_SEC
WHERE EXISTS
(SELECT 1
FROM DBA_TAB_PARTITIONS@TO_ID_OWN_MD_SEC
WHERE
TABLE_OWNER = COL1(i)
AND TABLE_NAME = COL2(i)
AND PARTITION_NAME LIKE '%2007%'
);
COMMIT;

END LOOP;

DBMS_OUTPUT.PUT_LINE('Insert Completed : ' || COL3(i));

END;
/

FYI : After inserting the 2007 partition rows ( just to insert/bacukp
the data ) into Archive Schema, i can truncate the 2007 partition
later.

Thanks in Advance.

With Regards,
Raja.