Data Pump EXPDP IMPDP content=METADATA_ONLY to Extract the Skeleton of the Oracle Database

Purpose of Content Option: Using this parameter you can choose the export / import utility for taking the metadata/metadata&data/data_only in to the dump file. The one of the benefit of this option is you can create the similar kind of database/ schema without data using the option content=METADATA. i.e. you can easily create a skeleton of the source. The dump size will not be a big one and you can easily import into the target schema or database.

You can take the data only dump also by setting the parameter value as DATA_ONLY.


Syntax: content=[ALL]/ DATA_ONLY/METADATA_ONLY.

Here is an example for creating a dummy schema with the exact structure of the source schema.

  1. Take an export dump of the schema SCOTT with content=metadata_only option.


expdp content=METADATA_ONLY directory=exp_dir dumpfile=exp_content.dmp logfile=exp_content.log schemas=scott

Export: Release 11.2.0.2.0 - Production on Mon Jun 4 07:15:06 2012

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, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_03":  /******** AS SYSDBA content=METADATA_ONLY directory=exp_dir dumpfile=exp_content.dmp logfile=exp_content.log schemas=scott
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Master table "SYS"."SYS_EXPORT_SCHEMA_03" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_03 is:
  /dbfs_direct/FS1/exp_content.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_03" successfully completed at 07:15:44

  1. Create the new schema with dump which is taken with METADATA_ONLY option

impdp directory=exp_dir dumpfile=exp_content.dmp logfile=imp_content.log remap_schema=scott:scott1 full=y

Import: Release 11.2.0.2.0 - Production on Mon Jun 4 07:19:00 2012

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, Real Application Clusters, Automatic Storage Management, 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=exp_dir dumpfile=exp_content.dmp logfile=imp_content.log remap_schema=scott:scott1 full=y
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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
ORA-39082: Object type ALTER_PROCEDURE:"SCOTT1"."PR_SAL" created with compilation warnings
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-39082: Object type VIEW:"SCOTT1"."V_EMP" created with compilation warnings
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYS"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 07:19:07

  1. Now the dump created a schema (scott1) with just structure of the schema Scott.
SQL> ALTER USER SCOTT1 IDEntified by tiger1;

User altered.

SQL> CONNECT SCOTT1/TIGER1
Connected.
SQL> select count(*) from DEPT;

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

SQL> select count(*) from emp;

  COUNT(*)
----------
         0
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-14 All Rights Reserved | Site Map | Contact | Disclaimer | Google