Moving data from Normal tables to history tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Moving data from Normal tables to history tables

  1. #1
    Join Date
    Dec 2002
    Location
    Singapore
    Posts
    27

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968

    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.

  3. #3
    Join Date
    Dec 2002
    Location
    Singapore
    Posts
    27
    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

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    You can start by looking at tahiti.oracle.com, specifically, PL/SQL Collections and Records.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  6. #6
    Join Date
    Dec 2002
    Location
    Singapore
    Posts
    27
    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

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    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

  8. #8
    Join Date
    Oct 2004
    Location
    Karachi
    Posts
    6
    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.

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    Was that ur Bull Headed Lyre?
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width