Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 8 down vote accepted

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.

share|improve this answer
Can you recommend a good example/tutorial for DBMS_METADATA.GET_DDL? I'm mostly finding 'spool... select dbms_metadata.get_dll() from dual; spool off' through Google. Is that about right? – MatthewToday May 19 '11 at 2:28
First place I look for tutorials is – Gary May 20 '11 at 10:20

Use the SHOW parameter to see the DDL:

imp userid=usr/pass@db full=y show=y log=imp.log
share|improve this answer
As usual, the answer was right in front of my nose - the SHOW command is exactly what I want, must have skipped over it when I was looking at the imp help. – MatthewToday May 19 '11 at 23:21

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.