advertisements
_____________________________________________________________________________________________________________________
Using the NETWORK_LINK option you can import the schema from source database to target database. One advantage of this option you don’t need export and import as it does the export and import in single shot from the source to destination. Also, the file system space is not needed to accommodate the huge dump files as we can directly import to target using network_link.
It is very amazing option with data pump. You can take the backup of source database schema from another database and you can store in dump files in target location as well.
It is very amazing option with data pump. You can take the backup of source database schema from another database and you can store in dump files in target location as well.
See the examples below. Here we have two databases – prod8 (source) and prod9(target)
SQL> select name from v$database;
NAME
---------
PROD8
SQL> show user
USER is "SCOTT"
SQL> select * from tab;
no rows selected
SQL> create table example_tab1 as select * from all_objects;
Table created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EXAMPLE_TAB1 TABLE
I have added a TNS entry (File location: $ORACLE_HOME/network/admin/tnsnames.ora) for prod8 in my prod9 database box. Entry as below:
prod8 =
(description =
(address =
(protocol = tcp)
(host = devdata.abc.diamond.net)
(port = 1522)
)
(connect_data =
(server = dedicated)
(sid = prod8)
)
)
Test the connectivity using the tnsping utility
$ tnsping prod8
TNS Ping Utility for Solaris: Version 11.1.0.7.0 - Production on 05-JUL-2011 22:26:12
Copyright (c) 1997, 2008, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (description = (address = (protocol = tcp) (host = devdata.abc.diamond.net) (port = 1522)) (connect_data = (server = dedicated) (sid = prod8)))
OK (20 msec)
Connect to prod9 using sqlplus and create a database link to prod8 with scott user
$ sqlplus
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jul 5 22:26:20 2011
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter user-name: scott/tiger
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create database link prod8 connect to scott identified by scott using 'prod8';
Database link created.
SQL> select * from tab@prod8
2 ;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
EXAMPLE_TAB1 TABLE
Database link is working and ready from the database prod9 to prod8
Now I am going to import the scott schema of prod8 database to prod9 database without dumpfile. See below
$ impdp scott/tiger directory=exp_dir logfile=impnetworkscott.log network_link=prod8
Import: Release 11.1.0.7.0 - 64bit Production on Tuesday, 05 July, 2011 23:55:37
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_SCHEMA_01": scott/******** directory=exp_dir logfile=impnetworkscott.log network_link=prod8
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SCOTT" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "SCOTT"."EXAMPLE_TAB1" 95307 rows
Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 23:58:04
Verify whether it is imported or not. Please ignore the error because the schema already exist in the target.
SQL> select name from v$database;
NAME
---------
PROD9
SQL> show user
USER is "SCOTT"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP1 TABLE
EMP2 TABLE
EXAMPLE TABLE
EXAMPLE_PARTITION TABLE
EXAMPLE_TAB1 TABLE
GT_EMP TABLE
TEST TABLE
8 rows selected.
Yes. table EXAMPLE_TAB1 has been imported without dumpfile to prod9 database!!!!
Next example is taking the schema export from source database from target machine. You can store the dump in files.
$ expdp scott/tiger directory=exp_dir dumpfile=networkscott.dmp logfile=networkscott.log network_link=prod8
Export: Release 11.1.0.7.0 - 64bit Production on Tuesday, 05 July, 2011 23:29:50
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/******** directory=exp_dir dumpfile=networkscott.dmp logfile=networkscott.log network_link=prod8
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 12 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SCOTT"."EXAMPLE_TAB1" 9.496 MB 95307 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/scott/networkscott.dmp
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 23:34:26
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment