Export and importing the metadata

Our ETL team requested us to create the table structures in a test database from the production database. The table names ends with the “DIM” which are in the SRMA schema. There are almost 75 tables with names ended with “DIM” .So it is a very difficult task to create a master slave script in the production database to get the create statement code of the table structures. To make this task very easy, there is an option in the expdp utility to export and import only the structure, storage etc.(metadata) of the segments. Below are the steps to export and import the metadata:

Production database schema :     SRMA

Tables:                %_DIM

Test database schema:        DMDB

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

export:

--------       

expdp dumpfile=expdp.struct.dmp directory=dpump logfile=expdp.struct.log tables=SRMA.%_DIM content=metadata_only

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

import:

-------

impdp dumpfile=expdp.struct.dmp directory=dpump logfile=impdp.struct.log  remap_schema=srma:DMDB

3. 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 DMDB/DMDB

Connected.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

PRODUCT_FAMILY_DIM             TABLE

AUDIT_DIM                      TABLE

PRODUCTS_DIM                   TABLE

PRODUCT_TYPES_DIM              TABLE

SPNL_DIM                       TABLE

SALES_DIVISION_DIM             TABLE

PRODUCT_CONFIGURATIONS_DIM     TABLE

OWNED_DISTRIB_DIM              TABLE

LABELS_DIM                     TABLE

FRCST_PROD_TYPE_DIM            TABLE

DISTRIBUTION_CHANNELS_DIM      TABLE

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

DATES_DIM                      TABLE

CURRENCY_DIM                   TABLE

ARTISTS_DIM                    TABLE

SALES_TYPE_DIM                 TABLE

REPORTING_LABEL_DIM            TABLE

CALENDARS_DIM                  TABLE

PROJECTS_DIM                   TABLE

CUSTOMERS_DIM                  TABLE

CUSTOMER_DIGITAL_DIM           TABLE

CUSTOMER_PHYSICAL_DIM          TABLE

21 rows selected.

SQL> select count (*)  from CUSTOMER_PHYSICAL_DIM;

 COUNT(*)

----------

        0

content parameter in export:

content parameter is used to filter the export if only data is loaded or only definition.

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.

Ref: http://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_export.htm

Note:
We can do the same task in another simple way by creating the tables in the test database using the database links.

For example:
SQL>create table CUSTOMER_PHYSICAL_DIM as select * from CUSTOMER_PHYSICAL_DIM@HQ.ACE.COM where 1=2;