1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Get DDL from EXPDP dumpfile.

Discussion in 'Server Administration and Options' started by Arju, Nov 8, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    With parameter sqlfile=file_name you can easily get DDL from dumpfile.
    Following is the steps.

    1.Create directory where dump resides.
    Code (Text):
    create directory d as 'E:';
    2.Now perform impdp. Note that no actual rows will be imported. Only you will get the file includes the DDL inside dump.
    Code (Text):
    impdp system/s sqlfile=1.log dumpfile=test.dmp directory=d
    With above command you will get DDL command inside the 1.log of dump file test.dmp
    Now if you look at 1.log you will get output as,
    Code (Text):
    -- CONNECT SYSTEM
    -- new object type path is: SCHEMA_EXPORT/USER
     CREATE USER "CR_2" IDENTIFIED BY VALUES '754F6AD69455CF37'
          DEFAULT TABLESPACE "CR_SPC"
          TEMPORARY TABLESPACE "TEMP";
     
    -- new object type path is: SCHEMA_EXPORT/SYSTEM_GRANT
    GRANT UNLIMITED TABLESPACE TO "CR_2";
     
    -- new object type path is: SCHEMA_EXPORT/ROLE_GRANT
     GRANT "DBA" TO "CR_2";
     
    -- new object type path is: SCHEMA_EXPORT/DEFAULT_ROLE
     ALTER USER "CR_2" DEFAULT ROLE ALL;
     
    -- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
    -- CONNECT CR_2

    BEGIN
    sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'SKYDB.REGRESS.RDBMS.DEV.US.ORACLE.COM', inst_scn=>'1264232148');
    COMMIT;
    END;
    /
     CREATE SEQUENCE  "CR_2"."ADMIN_SQ"  MINVALUE 1 MAXVALUE
    999999999999999999999999999 INCREMENT BY 1 START WITH 3 NOCACHE  NOORDER  NOCYCLE ;
     
     CREATE SEQUENCE  "CR_2"."BENING_ACCOUNTING_ID_SQ"  MINVALUE 1 MAXVALUE
    999999999999999999999999999 INCREMENT BY 1 START WITH 8222517 NOCACHE  NOORDER  NOCYCLE ;
     
    .
    .
    .

     
     
  2. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    In above post I have showed how we can get DDL from dumpfile that was taken by EXPDP. Now I will show how we can get DDL from dumpfile that was taken by EXP.

    The dump that was taken by EXP, has several ways to see DDL inside it.

    Way 1: Using strings command in unix:
    If you are on unix machine then the strings tool helps a lot to get DDL inside dump. suppose my dump file is test.dmp then use,
    Code (Text):
    strings test.dmp

    Way 2: Using show clause.

    Import command will be,
    Code (Text):
    imp userid=username/password file=test.dmp show=y
    In your screen you will see see the DDL.

    Way 3:Using indexfile clause.
    With indexfile clause you can get a fine output inside your file. Use it like,
    Code (Text):
    imp userid=username/password file=test.dmp indexfile=a.sql
    In the file a.sql you will get your desired DDL.
     
  3. jesthr@gmail.com

    jesthr@gmail.com Active Member

    Messages:
    23
    Likes Received:
    0
    Trophy Points:
    80
    I have Oracle 11g on HP-UX system.

    First, I checked, all user have set DEFAULT_ROLE = 'YES'

    I Granted a ROLE two users, user A and user B.
    After set new ROLE user A and user B,
    User A have default_role = 'YES' and user B have default_role = 'NO'

    Why two user have different parameter DEFAULT_ROLE

    Regards!

    Hine