8

I have generated an EXPDAT.DMP file of an Oracle database with the command:

exp userid=usr/pass@db owner=own rows=n compress=n

I then ran the following command to generate a file containing the 'Create Table...' statements:

imp userid=usr/pass@db full=y indexfile=output.sql

This is great for the Create Table statements, but I also want the Create Procedure / Create Function / Create View source code. I can see them there in the .DMP file, but I haven't found a way to extract them.

Is it possible to extract this information from the EXPDAT.DMP file? Or will I need to use another method?

The purpose is to have the source exportable to files that can be source-controlled.

Nick Chammas
  • 14,810
  • 17
  • 76
  • 124
MatthewToday
  • 325
  • 2
  • 3
  • 7

3 Answers3

7

If you've got access to the original database, I'd go with DBMS_METADATA.GET_DDL.

You can script it with UTL_FILE so that it goes through each object (from USER_OBJECTS), uses the name and type to extract the object then write it to a file that has the appropriate naming convention.

It will be a lot cleaner than trying to split a single file.

Gary
  • 1,959
  • 12
  • 14
3

Use the SHOW parameter to see the DDL:

imp userid=usr/pass@db full=y show=y log=imp.log
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
0

If you can use datapump(expdp,impdp) utility ,instead of traditional export , then sqlfile command can be used.

-- Take expdp

 expdp dumpfile=full.dmp logfile=full.log directory=EXP_DIR schemas=SCOTT

-- below impdp wont import, it will just generated all the object code in fullcode.sql file 

impdp dumpfile=full.dmp logfile=full.log directory=EXP_DIR schemas=SCOTT content=metadata_only sqlfile=fullcode.sql
rajkishore patro
  • 48
  • 1
  • 6