Import only Schema structure from on user schema to other user schema in oracle...

Some time requirement is to transfer only structure of one oracle user schema to other oracle user schema for development operation or any operation(migration purpose) so that time impdp/expdp logical backup utility helps us to transfer one schema structure other schema .so my bellow example showing you step by step process of how transfer on schema structure to other schema?


1)export schema structure using following command...

[oracle@aveksa-sena dbs]$ expdp  directory=d_dump dumpfile=scott.dmp logfile=scott.log SCHEMAS=scott CONTENT=METADATA_ONLY parallel=2

(In above command i used parameter CONTENT=METADATA_ONLY for export only structure of schema)

Export: Release 11.2.0.2.0 - Production on Sun Aug 18 22:36:22 2013

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

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=d_dump dumpfile=scott.dmp logfile=scott.log SCHEMAS=scott CONTENT=METADATA_ONLY parallel=2
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/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/scott.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 22:45:50


2) import schema structure using impdp command and specify the user name in remap_schema parameter where you want to import schema structure in my example i used test schema to import schema structure..

[oracle@aveksa-sena dbs]$ impdp  directory=d_dump dumpfile=scott.dmp logfile=scott.log remap_SCHEMA=scott:test  parallel=2

Import: Release 11.2.0.2.0 - Production on Sun Aug 18 22:50:19 2013

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

Username: /as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=d_dump dumpfile=scott.dmp logfile=scott.log remap_SCHEMA=scott:test parallel=2
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" 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
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 22:51:10

[oracle@aveksa-sena dbs]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Sun Aug 18 22:51:21 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn test/test
Connected.
SQL> select table_name from tabs;

TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE

SQL> select count(*) from dept;

  COUNT(*)
----------
         0

SQL> desc dept;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)