Export And Importing The Metadata

Export And Importing The Metadata

Production database schema : SSSPP
Tables: DIMP
Test database schema: MNPS

Export the tables using the below statement in the production database.

export:
--------      
create a directory and give the read, write access with a schema

Example:

create directory datadir as '/u05/data/datadir';
grant read, write on directory datadir to ssspp;

expdp system/******* dumpfile=expdp.struct.ssspp.dmp directory=datadir logfile=expdp.ssspp.log tables=SSSPP.DIMP content=metadata_only job_name=metadata_expdp
or
vi expdp_metadata.par
dumpfile=expdp.struct.ssspp.dmp directory=datadir logfile=expdp.ssspp.log tables=SSSPP.DIMP content=metadata_only job_name=metadata_expdp
:wq!

expdp system/****** parfile=expdp_metadata.par

To check the status of the data pump job while running

SQL> select OWNER_NAME,JOB_NAME,STATE from dba_datapump_jobs where JOB_NAME ='METADATA_EXPDP';

Import the above dump file in the test database as below.

import:
-------
-Create a dummy schema with a name MNPS
-Create a directory and give the read, write access with a schema

Example:

create user mnps identified by **** default tablespace test;
grant connect, resource to mnps;
create directory datadir as '/u05/data/testdatadir';
grant read, write on directory datadir to mnps;

impdp system/****** dumpfile=expdp.struct.ssspp.dmp directory=datadir logfile=impdp.struct.ssspp.log  remap_schema=ssspp:dimp

or

vi impdp_metadata.par
dumpfile=expdp.struct.ssspp.dmp directory=datadir logfile=impdp.struct.ssspp.log remap_schema=ssspp:mnps

impdp system/****** parfile=impdp_metadata.par

Check the test database for the tables if they are created. And we can see that there will be no data found in those tables.

Test after import:
------------------
SQL> conn DIMP/DIMP
Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
PRODUCT_FAMILY              TABLE
AUDIT                       TABLE
PRODUCTS                    TABLE
PRODUCT_TYPES               TABLE
SPNL                        TABLE
SALES_DIVISION              TABLE
PRODUCT_CONFIGURATIONS      TABLE
OWNED_DISTRIB               TABLE
LABELS                      TABLE
FRCST_PROD_TYPE             TABLE
DISTRIBUTION_CHANNELS       TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DATES                       TABLE
CURRENCY                    TABLE
ARTISTS                     TABLE
SALES_TYPE                  TABLE
REPORTING_LABEL             TABLE
CALENDARS                   TABLE
PROJECTS                    TABLE
CUSTOMERS                   TABLE
CUSTOMER_DIGITAL            TABLE
CUSTOMER_PHYSICAL           TABLE

21 rows selected.

SQL> select count (*)  from CUSTOMER_PHYSICAL ;

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

Content parameter: A content parameter is used to filter the export if only data is loaded or only

syntax:

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
ALL unloads both data and metadata. This is the default.
DATA_ONLY unloads only table row data; no database object definitions are unloaded.
METADATA_ONLY unloads only database object definitions; no table row data is unloaded.