Skip to Main Content
  • Questions

  • create sql script file from an export.dmp file

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Patricia.

Asked: April 27, 2001 - 11:14 am UTC

Last updated: October 20, 2004 - 4:59 pm UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

I am required to provide a script file that will recreate an entire database including schema,users,objects,privileges. All of this is contained in my full export. Is it possible to create a sql script file that provides the same results as a full import from the .dmp file.

and Tom said...

I'd really ask the recipient of this file why a DMP file wouldn't satisfy the requirement.

However,

you can get this, albeit with some work, via:

imp userid=.... full=y show=y

and redirecting the output (it'll not actually IMPORT it just show you what it would do). It'll require lots of reformatting to actually be a script you could run.

Toad and other tools like that have abilities in this area as well (but the dmp file is the best option in my opinion).


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

unable to redirect imp with show=Y

Mike Ryan, October 20, 2004 - 11:30 am UTC

This is just what I need to do, but when I try redirecting, it insists on writing to the screen and leaves the file to which I redirect empty. Redirection of the type command works fine. Environement is windows XP Pro service pack 2, Oracle version is 9.2.0.1

Below is my command:

imp gsk532clone/sa parfile=impgsk532cloneshow.par>showimp.txt

parfile contents are:

file=expGSK532.dmp log=impGSK532CLONE.log fromuser=GSK532 touser=GSK532CLONE commit=Y feedback=100 show=Y

Is Oracle preventing the redirect on windows?

The import without show=Y works like a charm.

Thanks

Mike Ryan

Tom Kyte
October 20, 2004 - 11:51 am UTC

show=y writes to stderr

you have to redirect stderr and stdout. google

"redirect stderr" windows

steve, October 20, 2004 - 1:57 pm UTC

If you use INDEXFILE=my_file_name.sql you will get the info directed to the file

Tom Kyte
October 20, 2004 - 4:59 pm UTC

some of the info -- not everything comes out with indexfile

create sql script from an export file

Jim Raines, November 08, 2005 - 6:17 pm UTC

I ran into the same problem with Windows. Redirecting to
stderr worked great for me. Standard redirect > didn't
work but the stderr redirect 2> did work.

d:\sql>imp rms_test/rms_test ^
More? file=d:\ileads\oradmps\ileads2.dmp ^
More? show=y fromuser=leads touser=rms_test ^
More? 2>d:\ileads\oradmps\rms_test.sql

Thanks Mike for the tip on redirecting to stderr

susan, January 22, 2007 - 5:02 am UTC

Hi,

You can mention log=imp.log along with the imp command, so that the output will be present in the logfile specified.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.