Moving Records from one database to another database using dblink
i got five database, i have to move sys.aud$ records from five databases to one centralized database into another schema every day at 10:00 clock, i have to use a dblink for this, i have to create same table as sys.aud$ with different schema in centralized database with one extra column db_unique_name,by using db_link how i need to move records from all the five databases to one centralized database, can anyone help me here how to create a db_link from to move records from five database to one centralized database, due to maintainance perspective i have to move the records from all the five databases to one centralized database.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
databases are on different network, but the records need to move from all the five databases to one centralized database, into different schema due to maintainance perspective
If the centralized database cannot connect to the remote databases directly through the network, then you cannot use DB links.
If you can, use remote links.
If not, use an export / import or data pump + scp or ftp to move the data to the central db.
Actually, for better network performance, using export + gzip compression + ftp may give you better overall performance, but the solution is more complex. I'd start with the simple stored procedure with remote queries, as my example shows.
If not, use an export / import or data pump + scp or ftp to move the data to the central db.
Actually, for better network performance, using export + gzip compression + ftp may give you better overall performance, but the solution is more complex. I'd start with the simple stored procedure with remote queries, as my example shows.
you can't have a link to 5 databases. instead you would have 5 links, one for each database.
you would simply iterate through them
you would simply iterate through them
ASKER
for that i have to create a tnsnames.ora file for centralized database, after creating tnsnames.ora what is the procedure for moving records from all the five databases to one centralized database
ASKER
I know that i have to create 5 links to connect to centralized database, how i need to do it, i am not familiar with db link concept.
ASKER
for the maintenance of the audit records, we don’t want to move them from the SYS.AUD$ table to another table in the same database. We want to move them out of the individual databases and place them into a central database. We have a DBA web site and database. We want to move the records to this site.
The production site is http://localhost1:7779/pls/apex/f?p=106:1:0 and the development site is http://localhost2:7779/pls/apex/f?p=106:1:0
These sites are run from a set of databases. The production database is Prod and the development database is dev. We use the INFO schema to store the data.
The production site is http://localhost1:7779/pls/apex/f?p=106:1:0 and the development site is http://localhost2:7779/pls/apex/f?p=106:1:0
These sites are run from a set of databases. The production database is Prod and the development database is dev. We use the INFO schema to store the data.
if you "pull" data into the central db from the other's then you need all 5 tns entries on the central db's server
if you "push" data from the other db's to the central, then you need the a tns entry for the central db on each of the other db's servers
if you "push" data from the other db's to the central, then you need the a tns entry for the central db on each of the other db's servers
ASKER
after creating a tnsentry what is the procedure..?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
for a pull query...
it would look like mrjoltcola's query above...
once for each remote db, all run on the central db
for a push query...
each remote db would run this statement
insert into all_aud@central_db_link select a.*,sys_context('userenv',
it would look like mrjoltcola's query above...
once for each remote db, all run on the central db
for a push query...
each remote db would run this statement
insert into all_aud@central_db_link select a.*,sys_context('userenv',
ASKER
i am trying to pull the records from five databases and i want to transfer to one centralized database., for that i need to use the first one right.
if i need to use the first one , what is the next step procedure for transferring records from all the database to one central database
if i need to use the first one , what is the next step procedure for transferring records from all the database to one central database
First one?
You are losing me. Please be specific on what you are trying.
You asked how to use remote database connections using a dblink; that is what my sample shows.
Please clarify if your requirements have changed.
You are losing me. Please be specific on what you are trying.
You asked how to use remote database connections using a dblink; that is what my sample shows.
Please clarify if your requirements have changed.
I labelled "pull" vs "push" in my posts above both for the db link creation and the queryies needed to actually move the data.
mrjoltcola has only posted one query, that is the "pull" example. ( http:#36385095 )
did you try it? if so, what happened?
if you did not try it, why not?
mrjoltcola has only posted one query, that is the "pull" example. ( http:#36385095 )
did you try it? if so, what happened?
if you did not try it, why not?
Creating the link is easy and the syntax is in the online docs.
What is the real question here?