Oracle Database Discussions

If the dump was made with the 9i export tool (exp), then you can use the following:

imp user/pass file=dump.dmp indexfile=output.sql

However, this will not show you inserts for the data, just DDL.

If it's a 10g export (expdp) then you will do:

impdp user/pass dumpfile=dump.dmp sqlfile=output.sql

Out of curiosity, if you are hoping to get the DDL, why not just import the dump to an Oracle database like it is meant to do? Then you can get all the DDL you want, if need be.

Thanks for the instructions.
This is the dump that i got from a team that , for what reason it was i don't know.
We ask them the ddl to create a database needed for an app they're using. Then they gave that dump to us to figure out.
You said that i can dump it to the database and i can get the ddl, how so?

First of all make sure it is an export file, export files are binary files. If it is then you can use some think like this:

$ imp file=filename userid=system/manager full=y show=y log=logfile.log

To get DDL out of log file is really a pain in butt, but you can try. Why you dont want to create the database directly from this dump? why you want plain DDL?

What version of database are you using?

Daljit Singh

If you are in UNIX, save this code to a script and try it.

:
# impshow2sql   Tries to convert output of an IMP SHOW=Y command into a 
#               usage SQL script.
#
# To use:
#               Start a Unix script session and import with show=Y thus:
#               
#               $ imp user/password file=exportfile show=Y log=/tmp/showfile
#               
#               You now have the SHOW=Y output in /tmp/showfile . 
#               Run this script against this file thus:
#               
#               $ ./impshow2sql /tmp/showfile > /tmp/imp.sql
#               
#               The file /tmp/imp.sql should now contain the main SQL for
#               the IMPORT. 
#               You can edit this as required.
# Note:         This script may split lines incorrectly for some statements
#               so it is best to check the output.
#
# CONSTRAINT "" problem:
#               You can use this script to help get the SQL from an export
#               then correct it if it includes bad SQL such as CONSTRAINT "".
#               Eg:
#                Use the steps above to get a SQL script and then
#                $ sed -e 's/CONSTRAINT ""//' infile > outfile
#               Now precreate all the objects and import the export file.
#
# Extracting Specific Statements only:
#	It is fairly easy to change the script to extract certain statements
#	only. For statements you do NOT want to extract change N=1 to N=0
# 	Eg: To extract CREATE TRIGGER statements only:
#		a) Change all lines to set N=0. 
# 		   Eg: / \"CREATE /    { N=0; }
#		       This stops CREATE statements being output.
#
#		b) Add a line (After the general CREATE line above): 
#			/ \"CREATE TRIGGER/	{ N=1; }
#		   This flags that we SHOULD output CREATE TRIGGER statements.
#
#		c) Run the script as described to get CREATE TRIGGER statements.
# 

awk '  BEGIN	{ prev=";" }
	/ \"CREATE / 	{ N=1; }
	/ \"ALTER / 	{ N=1; }
	/ \"ANALYZE / 	{ N=1; }
	/ \"GRANT /  	{ N=1; }
	/ \"COMMENT /   { N=1; }
	/ \"AUDIT /     { N=1; }
	N==1 { printf "\n/\n\n"; N++ }
	/\"$/ { prev=""
		if (N==0) next;
		s=index( $0, "\"" );
              	if ( s!=0 ) {
			printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 ) 
			prev=substr($0,length($0)-1,1 );
	      	} 
		if (length($0)<78) printf( "\n" );
	      }'  $*

user

If it is indeed an Oracle export file (and not a comma delimited text or something like that), and you use the method Daljit gave for importing to your database, you can then use:

set long 9999999
set head off
set pages 0
set echo off
set feedback off
spool generate_ddl.sql
select dbms_metadata.get_ddl(object_type, object_name) from dba_objects;

Note that this will only work on a 9i or 10g database, however.

But I agree with Daljit...once you have it in the DB, why worry about the DDL?

Excellent script!

copy/paste

Thanks for the help.
My task is to automate the process to deploy an application using this database.
I need a ddl file to create a database and then insert data later, may be with different set of data that we want.
I think i can import the dump in my new database for this test.
I'll try to do both ways then, see what would be effective.

I'm using Oracle 9i.

when you say "create a database", do you actually mean a schema owner?

So you're saying every time you have a new client, per se, you must create a schema, import all these empty tables, and fill them as necessary, yes?

You have two choices.

Import the data first, connect as the user that owns the imported data, and run:

select dbms_metadata.get_ddl(object_type, object_name) || '/' from user_objects;

Use all the "set" stuff I gave you in the similar script above.

The other choice is to do your import, but with DATA=N. That will load all the STRUCTURES and no data.

imp user/pass file=filename.dmp fromuser=original_user touser=new_user data=n

Shouldn't it be ROWS=N ?

Nicolas.

Foiled again!

Yes, ROWS=N, don't mind me.

Hi,

I am not able to generate DDL for JAVA SOURCE and JAVA CLASS object types.

regards
Mathew

I am using oracle 9i. want to import .dmp file into my oracle 91.

my o.s is windows xp professional;

And... ?

Nicolas.