Pages

Tuesday, 10 January 2012

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

  1. 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

  1. 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.



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;

1 comment: