Create DB Link

Create DB Link and Import Data

This document is showing how to import table data from 12 to 11g

Oracle DB Link

PURPOSE: All documents are provided on this Blog just for educational purposes only.  Please make sure that you run it in your test environment before to move on to production environment. 
 
NOTE: 

exp and imp does not support because of different version.

expd and impd also does not support because of the same reason.

In this document, we are going to use the Approach via DB Link and using insert command.

You check are there any DB link exist.

DBA_DB_LINKS:    All DB links defined in the database
ALL_DB_LINKS:     All DB links the current user has access to

USER_DB_LINKS:  All DB links owned by current user

col OWNER format a20;
col DB_LINK format a20;
col USERNAME format a10;
col HOST format a20;

set linesize 200;
select * from all_db_links;

If you want to grant, revoke user or drop any link which you don't want to use.

GRANT create database link TO "user_name";


REVOKE create database link from "user_name";

drop database link IMPORT_MYDB_01;

Copy data from FASDBPRD to KARDBPRD.

Database Name           : FASDBPRD   =>> 12G DB. This is source DB. Database Name                    : KARDBPRD   =>> 11g DB.  This is destination DB

Hostname                : ORADBPRD11.th.com

Hostname                : ORADBPRD12.th.com

Create DB LINK on Source DB (The DB where you want to copy the data)

create public database link IMP_KARDBPRD_01 connect to system identified by "your_password" using

'(DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = ORADBPRD11.th.com)(PORT = 1521))

    )

    (CONNECT_DATA = (SERVICE_NAME = FASDBPRD)

    )

  )';

OR

You can create link as follow, wenn you have entry in the tnsnames.ora

CREATE DATABASE LINK [Links_name] CONNECT TO [User_name] IDENTIFIED BY <password from destinationDB user> USING '[DB_connection alias from tnsnames.ora]';

You can check whether the link works.

select table_name, num_rows counter from dba_tables@IMP_KARDBPRD_01 where owner = 'SCHEMANAME05' and table_name='TABLE_NAME01' order by table_name;

TABLE_NAME                                      COUNTER

----------------------------------------------- ----------

TABLE_NAME01                                    12343320

You may need to truncate the table in the database destination DB (where you want to copy the data)

The following command should also create table with data, if this is not the case please use insert command.

Table name is same on both side but schema name is different.

You need to drop the table as follow, if it exist. Because create command will create table with the data.

drop table SCHEMANAME01.TABLE_NAME01 cascade constraints;

create table SCHEMANAME01.TABLE_NAME01 as select * from SCHEMANAME05.TABLE_NAME01@IMPORT_MYDB_01;

If the table already exist with the same description and you do not want to drop it, then you need to just truncate the table and execute the following command.

truncate table SCHEMANAME01.TABLE_NAME01 drop storage;

insert into SCHEMANAME01.TABLE_NAME01 select * from SCHEMANAME05.TABLE_NAME01@IMP_KARDBPRD_01;

Execute the following command to get the stats gather.

exec dbms_stats.gather_table_stats (ownname  => 'SCHEMANAME01', tabname => 'TABLE_NAME01' , estimate_percent => 5, degree => 8, cascade => true);

12343320 rows created.

You can check the count on both DB's

select count(*) from SCHEMANAME01.TABLE_NAME01;

select count(*) from SCHEMANAME05.TABLE_NAME01@IMP_KARDBPRD_01;