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.
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.
No comments:
Post a Comment