Oracle data pump export/import

Standard Oracle export/import tools are damn usefull and fast.

To export entire schema as sys user you need only to specify directory object (more about directory) , dump file name, schema name. Generated file is binary, investigate log file to be sure that export was succesful.

for example:

 expdp \"SYS AS SYSDBA\"  DIRECTORY=data_pump_dir DUMPFILE=OFMDEVPH1_MDS_new_2.dmp SCHEMAS=OFMDEVPH1_MDS  

As far as OFMDEVPH1_MDS_new_2.dmp is binary you can access file content using impdp tool.

 impdp \"SYS AS SYSDBA\"  DIRECTORY=data_pump_dir DUMPFILE=OFMDEVPH1_MDS_new_2.dmp SCHEMAS=OFMDEVPH1_MDS SQLFILE=user_OFM_MDS.sql INCLUDE=USER,SYSTEM_GRANT,ROLE_GRANT,DEFAULT_ROLE,TABLESPACE_QUOTA  

option SQLFILE tells to generate sql file with all ddls in text format. insted of performing import.
INCLUDE is saying that generated file user_OFM_MDS.sql will be only containing specified categories of objects.
content of generated file:

 -- CONNECT SYS  
 ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';  
 ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';  
 ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';  
 ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';  
 ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';  
 ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';  
 -- new object type path: SCHEMA_EXPORT/USER  
 -- CONNECT SYSTEM  
 CREATE USER "OFMDEVPH1_MDS" IDENTIFIED BY VALUES 'S:E2ED5B8A25;FB42494E4D316374'  
    DEFAULT TABLESPACE "OFMDEVPH1_MDS"  
    TEMPORARY TABLESPACE "OFMDEVPH1_IAS_TEMP";  
 -- new object type path: SCHEMA_EXPORT/SYSTEM_GRANT  
 GRANT CREATE TYPE TO "OFMDEVPH1_MDS";  
 GRANT CREATE PROCEDURE TO "OFMDEVPH1_MDS";  
 GRANT CREATE SEQUENCE TO "OFMDEVPH1_MDS";  
 GRANT CREATE TABLE TO "OFMDEVPH1_MDS";  
 -- new object type path: SCHEMA_EXPORT/ROLE_GRANT  
 GRANT "CONNECT" TO "OFMDEVPH1_MDS";  
 -- new object type path: SCHEMA_EXPORT/DEFAULT_ROLE  
 ALTER USER "OFMDEVPH1_MDS" DEFAULT ROLE ALL;  
 -- new object type path: SCHEMA_EXPORT/TABLESPACE_QUOTA  
 DECLARE  
  TEMP_COUNT NUMBER;  
  SQLSTR VARCHAR2(200);  
 BEGIN  
  SQLSTR := 'ALTER USER "OFMDEVPH1_MDS" QUOTA UNLIMITED ON "OFMDEVPH1_MDS"';  
  EXECUTE IMMEDIATE SQLSTR;  
 EXCEPTION  
  WHEN OTHERS THEN  
   IF SQLCODE = -30041 THEN  
    SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES  
        WHERE TABLESPACE_NAME = ''OFMDEVPH1_MDS'' AND CONTENTS = ''TEMPORARY''';  
    EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;  
    IF TEMP_COUNT = 1 THEN RETURN;  
    ELSE RAISE;  
    END IF;  
   ELSE  
    RAISE;  
   END IF;  
 END;  
 /  

In this way you can review entire dump file. For me it was just matter of curiosity, how passwords are presented.

for importing dump file use:

 impdp \"SYS AS SYSDBA\"  DIRECTORY=data_pump_dir DUMPFILE=OFMDEVPH1_MDS_new_2.dmp SCHEMAS=OFMDEVPH1_MDS  

Good article for further reading: Oracle Data Pump Quick Start