-
Moving data from Normal tables to history tables
Hi All,
Can any one guide me in the process of moving data from normal tables to History tables. It can be some sort of a procedure which should be a cron job running at night. My aim is to move data say 1.5 yrs or 2yrs old data to History tables. What aspects i need to check when moving data. And how can i write a procedure for this requirement. Your help in this regard is greatly appreciated. The schema is same in both the normal table and history table.Pls do find the below 2 schemas for which i need to move data.
SQL> DESC PXM_FLT;
Name Null? Type
---------------------------------------- -------- ----------------------------
RCRE_USER_ID NOT NULL VARCHAR2(15)
RCRE_DT NOT NULL DATE
LCHG_USER_ID VARCHAR2(15)
LCHG_DT DATE
AIRLINE_CD NOT NULL VARCHAR2(5)
REF_ID NOT NULL VARCHAR2(12)
BATCH_DT NOT NULL DATE
CPY_NO NOT NULL NUMBER(2)
ACCRUAL_STATUS NOT NULL VARCHAR2(1)
FLT_DT NOT NULL DATE
OPERATING_CARRIER_CD NOT NULL VARCHAR2(3)
OPERATING_FLT_NO NOT NULL NUMBER(4)
MKTING_CARRIER_CD VARCHAR2(3)
MKTING_FLT_NO NUMBER(4)
BOARD_PT NOT NULL VARCHAR2(5)
OFF_PT NOT NULL VARCHAR2(5)
AIR_CD_SHARE_IND VARCHAR2(1)
UPLOAD_ERR_CD VARCHAR2(5)
MID_PT1 VARCHAR2(5)
MID_PT2 VARCHAR2(5)
MID_PT3 VARCHAR2(5)
MID_PT4 VARCHAR2(5)
MID_PT5 VARCHAR2(5)
PAX_TYPE VARCHAR2(3)
PAY_PRINCIPLE VARCHAR2(1)
SQL> DESC HIS_PXM_FLT;
Name Null? Type
---------------------------------------- -------- ----------------------------
RCRE_USER_ID NOT NULL VARCHAR2(15)
RCRE_DT NOT NULL DATE
LCHG_USER_ID VARCHAR2(15)
LCHG_DT DATE
AIRLINE_CD NOT NULL VARCHAR2(5)
REF_ID NOT NULL VARCHAR2(12)
BATCH_DT NOT NULL DATE
CPY_NO NOT NULL NUMBER(2)
ACCRUAL_STATUS NOT NULL VARCHAR2(1)
FLT_DT NOT NULL DATE
OPERATING_CARRIER_CD NOT NULL VARCHAR2(3)
OPERATING_FLT_NO NOT NULL NUMBER(4)
MKTING_CARRIER_CD VARCHAR2(3)
MKTING_FLT_NO NUMBER(4)
BOARD_PT NOT NULL VARCHAR2(5)
OFF_PT NOT NULL VARCHAR2(5)
AIR_CD_SHARE_IND VARCHAR2(1)
UPLOAD_ERR_CD VARCHAR2(5)
MID_PT1 VARCHAR2(5)
MID_PT2 VARCHAR2(5)
MID_PT3 VARCHAR2(5)
MID_PT4 VARCHAR2(5)
MID_PT5 VARCHAR2(5)
PAX_TYPE VARCHAR2(3)
PAY_PRINCIPLE VARCHAR2(1)
Thanks,
Ramakrishnan
K.M.Ramakrishnan
-
Re: Moving data from Normal tables to history tables
You could write a PL/SQL package that would take advantage of PL/SQL collections with BULK BINDING and FORALL. You can also run this package using DBMS_JOB.
You can also do this with straight SQL.
-
Hi Gandolf,
If you don't mind can u give me some more tips on the package you have mentioned.Or can u just highlight a sample format on how to use it or point me to a link where i can have more information with the PL/SQL collection in moving the data to history tables.Your guidance is greatly appreciated.
Thanks,
K.M.Ramakrishnan
-
You can start by looking at tahiti.oracle.com, specifically, PL/SQL Collections and Records.
-
Why can't you use direct insert statement?
Ex:
Insert into history_table(c1,c2..cn)
select c1,c2,..cn from normal_table
where some_date between :d1 and :d2 ;
You can also consider PDML option in 9.2
Tamil
-
Hi TamilSelvan,
Thanks for ur response.I can understand that through Direct insert stmt i can do that.But what is that u have mentioned abt PDML option in 9.2.Can u pls explain more on this and my question is whether shd i take note of Any constraints which are there in both the tables and how shd i go abt it if there are foreign key constraints on both the table.And how can i write a procedure if i want to do it for few 7 or 8 tables.Your suggestion is gretaly appreciated.
Thanks,
Rama
K.M.Ramakrishnan
-
PDML = PARALLEL DML.
If the db server has more than one CPU and the system has many disks (Striping is better), then PDML works faster.
Tamil
-
Hi, seniors have have suggested u well.
Is ur problem solved yet. I agree to insert ur records thru INSERT command.
If ur history table is on another machine then ultimatly u can export ur table into a dump file & then import in ur target table.
-
Originally posted by WallWalker
. . . ur problem . . . ur records . . . ur history . . . ur table . . . ur target table.
http://www.mnsu.edu/emuseum/archaeol...e_east/ur.html
-
Was that ur Bull Headed Lyre?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|