How do i extract data from one database and load it into another

[m2001331]

Dear all,
I have a question - I have two databases residing in two different hosts -one db is oracle8i and the other is 9i.
Both are connected in a network.
I receive data daily from a live update to the oracle8i db and my task is to come up with a script that would insert these data from the 8i db into the 9i db - the db structures are exactly the same.I need to do this on a 'online' basis.I know creating a *.dmp file and importing into the 9i db would be an option but I cannot do that as i am required to do this on a script - say something like running this in a cronjob at a certain time of the day.
Can anyone please advise me on how to go about achieving this.
Your answers/suggections are appreciated.
thanks and regards.

 

[SantaMufasa]

M,

By far, the simplest method of accomplishing what you want to do is with a database link and a trigger. The database link allows you access to the remote (Oracle 9i) database and the trigger automatically does your data manipulation on the remote table when you INSERT a new row in the Oracle 8i table.

Here is sample, working code that simulates what you are trying to do. (The database link in this example connects to a remote database schema that is about 750 miles away from the source database.) I have changed the username, password, and host alias names to protect our security, but you can replace the "<value>" entries with values appropriate to your environment:

create database link yada connect to <username> identified by <password> using '<tnsnames alias>'
/

Database link created.

create or replace trigger MReplicate after insert on s_region
for each row
begin
    insert into t_region@yada values (:new.id, :new.name);
end;
/

Trigger created.

select * from s_region;

        ID NAME
---------- --------------------
         1 North America
         2 South America
         3 Africa / Middle East
         4 Asia
         5 Europe

select * from t_region@yada;

        ID NAME
---------- --------------------
         1 North America
         2 South America
         3 Africa / Middle East
         4 Asia
         5 Europe

insert into s_region values (6,'M2001331');

1 row created.

select * from s_region;

        ID NAME
---------- --------------------
         1 North America
         2 South America
         3 Africa / Middle East
         4 Asia
         5 Europe
         6 M2001331

select * from t_region@yada;

        ID NAME
---------- --------------------
         1 North America
         2 South America
         3 Africa / Middle East
         4 Asia
         5 Europe
         6 M2001331

Let us know if these examples guide you to accomplishing your objectives.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 04:35 (24Feb05) UTC (aka "GMT" and "Zulu"),
@ 21:35 (23Feb05) Mountain Time

Do you use Oracle and live or work in Utah, USA? Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

I think that trigger is the simplest but not the optimal option. That's mostly because in the case of network or other failures between databases the main transaction also fails. Building a snapshot instead of table seems to be more reliable.

Regards, Dima

http://seminihin.narod.ru

 

[m2001331]

yes - thank you for the solution -i accomplished what i set put to do.
thanks again