7

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.

8

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.

  • 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
  • 1
    First place I look for tutorials is oracle-base.com. oracle-base.com/articles/9i/MetadataAPI.php – Gary May 20 '11 at 10:20
3

Use the SHOW parameter to see the DDL:

imp userid=usr/pass@db full=y show=y log=imp.log
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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