Insert data from one database to another in oracle

Insert data from one database to another database in oracle

Sometimes we require to migrate data between the databases when using different servers. Like in my case I use three databases for development, test and production. In development phase I do all the database modelling and designing. Once development work finished then I get to migrate the table structure and data to test database which was a bit annoying before hand by exporting data into csv file and import into another database.

Most problem you will have with formatting field i.e. date, number etc.

I have used DB Link in toad to migrate data over. I found it is quite easy and interesting though. Create a DB link below –

Defining a Fixed-User Database Link: Example

In the following statement, user hr on the remote database defines a fixed-user database link named local to the hr schema on the local database:

CREATE PUBLIC DATABASE LINK local   CONNECT TO hr IDENTIFIED BY hr   USING 'local';

Once this database link is created, hr can query tables in the schema hr on the local database in this manner:

SELECT * FROM employees@local;

[notice]Now the above query may not work depending on grant permission in the database. Remember you also need to create a public SYNONYM for this table. I have created a public synonym below -[/notice]

CREATE PUBLIC SYNONYM employee FOR employee;

Now you can insert data into a table from different server or schema using dblink.

INSERT into emp SELECT FROM employee@local;