DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Monday, August 31, 2015

Data Pump Parameter-REMAP_SCHEMA


Data Pump Parameter-REMAP_SCHEMA


In conventional import (IMP) utility we havefromuser and touser parameter to import the data dump which is taken from one user to another user. In Data Pump impdp oracle introduced new parameter called REMAP_SCHEMA.

Syntax:
impdp REMAP_SCHEMA=source:target where source and target are the schema names.




[oracle@oracle11g ~]$expdp system/oracle schemas=scott directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log compression=all

Export: Release 11.2.0.3.0 - Production on Mon Aug 31 17:00:49 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** schemas=scott directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log compression=all
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 320 KB
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
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."DEPT"                              4.976 KB       4 rows
. . exported "SCOTT"."EMP"                               5.640 KB      14 rows
. . exported "SCOTT"."RAM"                               4.687 KB       1 rows
. . exported "SCOTT"."SALGRADE"                          4.890 KB       5 rows
. . exported "SCOTT"."SITA"                              4.687 KB       1 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
. . exported "SCOTT"."SALE"                                  0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q1"                         0 KB       0 rows
. . exported "SCOTT"."SALE_SALES_Q2"                         0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u01/dump/remap_schema.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 17:01:09


SQL> set lines 1000 pages 1000
SQL> select username from dba_users;

USERNAME
------------------------------
DBSNMP
SCOTT
RAM
HR
SYSMAN
MGMT_VIEW
SYS
SYSTEM
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
WMSYS
ORACLE_OCM
APPQOSSYS
XS$NULL
APEX_030200
OWBSYS_AUDIT
BI
PM
MDDATA
IX
ORDDATA
CTXSYS
ANONYMOUS
SH
OUTLN
DIP
OE
APEX_PUBLIC_USER
XDB
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS

37 rows selected.


impdp system/oracle directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log remap_schema=scott:test


[oracle@oracle11g ~]$ impdp system/oracle directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log remap_schema=scott:test

Import: Release 11.2.0.3.0 - Production on Mon Aug 31 17:04:39 2015

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** directory=test_dir dumpfile=remap_schema.dmp logfile=remap_schema.log remap_schema=scott:test
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
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."DEPT"                               4.976 KB       4 rows
. . imported "TEST"."EMP"                                5.640 KB      14 rows
. . imported "TEST"."RAM"                                4.687 KB       1 rows
. . imported "TEST"."SALGRADE"                           4.890 KB       5 rows
. . imported "TEST"."SITA"                               4.687 KB       1 rows
. . imported "TEST"."BONUS"                                  0 KB       0 rows
. . imported "TEST"."SALE"                                   0 KB       0 rows
. . imported "TEST"."SALE_SALES_Q1"                          0 KB       0 rows
. . imported "TEST"."SALE_SALES_Q2"                          0 KB       0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at 17:04:52




SQL> select username from dba_users;

USERNAME
------------------------------
MGMT_VIEW
SYS
SYSTEM
DBSNMP
SYSMAN
SCOTT
RAM
HR
TEST
OUTLN
FLOWS_FILES
MDSYS
ORDSYS
EXFSYS
WMSYS
APPQOSSYS
APEX_030200
OWBSYS_AUDIT
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
OWBSYS
SI_INFORMTN_SCHEMA
OLAPSYS
ORACLE_OCM
XS$NULL
BI
PM
MDDATA
IX
SH
DIP
OE
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
SPATIAL_WFS_ADMIN_USR

38 rows selected.

2 comments:

Sridevi K said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

Hemanth Chintapalli said...

I want to export and import one table from one database to another database
I need to change username and tablespace at the time of import. How it is ? command please.