Archival Process - Simple Method

From: raja <dextersunil_at_gmail.com>
Date: Wed, 18 Nov 2009 22:44:14 -0800 (PST)
Message-ID: <2f9791ff-e852-40f1-b5c7-acd0c70916de_at_b25g2000prb.googlegroups.com>

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_at_TO_ID_OWN_MD_SEC A, DBA_PART_TABLES_at_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)_at_TO_ID_OWN_MD_SEC WHERE EXISTS
(SELECT 1
FROM DBA_TAB_PARTITIONS_at_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. Received on Thu Nov 19 2009 - 00:44:14 CST