Oracle export and import of only DDL

Following you'll find the complete procedure that can be followed to restore an Oracle schema using a previous expdp dump and what's more without importing the data rows.


1) To complete the first step it is necessary to get the name of the tablespaces where I'm going to restore the schema using the backup done previously.     

   export ORACLE_SID=TEST
    export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15
    impdp system DIRECTORY=DATA_PUMP JOB_NAME=IMP_SCOTT LOGFILE=imp_SCOTT.log SCHEMAS=SCOTT DUMPFILE=EXP_SCOTT.dmp SQLFILE=ddl_SCOTT.sql

Be careful:  With the option SQLFILE enabled only a DDL file is going to be produced.




2) Using the information obtained before now we are ready to create the new tablespaces:
      
        new object type path: SCHEMA_EXPORT/USER
    CREATE USER "SCOTT" IDENTIFIED BY VALUES 'xxxxxxxxx' DEFAULT TABLESPACE "SCOTTDATA"
        TEMPORARY TABLESPACE "TEMP";

    export ORACLE_SID=TEST
          sqlplus / as sysdba
          create tablespace SCOTTDATA datafile '+DG_DATI_DBF' size 256M autoextend on next 128M;
          create tablespace SCOTTINDX datafile '+DG_INDX_DBF' size 256M autoextend on next 128M;
       
3) Now we can import the schema without the data rows with the following command :

    impdp system DIRECTORY=DATA_PUMP JOB_NAME=IMP_SCOTT LOGFILE=imp_SCOTT.log SCHEMAS=SCOTT DUMPFILE=EXP_SCOTT.dmp CONTENT=METADATA_ONLY 
            
Please remember that who is writing doesn't know your environment so it's important to verify all the procedures in your test database before.

See you in my next Blog.

Byeee