OraFAQ Forum: Server Utilities » Schema export import

Home » RDBMS Server » Server Utilities » Schema export import (Oracle 11g,Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Schema export import [message #517830]

Thu, 28 July 2011 10:18

Smith14
Messages: 38
Registered: July 2011
Location: India

Member

Hi ,

I did schema export using below command.

exp jack/test12 file=schemaexp.dmp log=schemaexp.log owner='HENRY','MARK','EMANUEL','SWAN','LORDSON','JENNY','MARIE' rows=y direct=y constraints=y compress=no

The export was sucessful.

To import these schemas, create users and then import users one by one or
is there any way to import all these schemas in one shot ?

Thank you

Report message to a moderator

Re: Schema export import [message #517834 is a reply to message #517830]

Thu, 28 July 2011 10:30

cookiemonster
Messages: 13904
Registered: September 2008
Location: Rainy Manchester

Senior Member

look at fromuser and touser parameters in imp documentation.

Report message to a moderator

Re: Schema export import [message #517835 is a reply to message #517830]

Thu, 28 July 2011 11:00

Michel Cadot
Messages: 68455
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can also just use an import in full mode as you want ALL users.

Why do you still use the old exp/imp in 11g? (By the way, ALWAYS post your Oracle version with 4 decimals.)
You will much more and interesting features with DataPump.

Regards
Michel

Report message to a moderator

Re: Schema export import [message #517836 is a reply to message #517835]

Thu, 28 July 2011 11:14

Smith14
Messages: 38
Registered: July 2011
Location: India

Member

Quote:

You can also just use an import in full mode as you want ALL users.

This will only work if i had taken the full schema export i guess.

Please clarify..

Report message to a moderator

Re: Schema export import [message #517837 is a reply to message #517836]

Thu, 28 July 2011 11:22

Smith14
Messages: 38
Registered: July 2011
Location: India

Member

I gave below command to import and ended with error.

imp jack/test12 file=schemaexp.dmp log=schemaimp.log fromuser=HENRY touser=HENRY commit=y ignore=y

Import: Release 10.2.0.1.0 - Production on Thu Jul 28 21:45:59 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

IMP-00002: failed to open schemaexp.dmp for read
Import file: EXPDAT.DMP >

Can you tell me how to fix this error?

Thank you

Report message to a moderator

Re: Schema export import [message #517838 is a reply to message #517837]

Thu, 28 July 2011 11:27

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can you tell me how to fix this error?
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

00002, 00000, "failed to open %s for read"
// *Cause:  Import failed to open the export file for reading. This message is 
//          usually followed by a device message from the operating system. 
// *Action: Take appropriate action to restore the device.

I suspect file does not exist within current directory.
You did not post sufficient details to ascertain exact cause

Report message to a moderator

Re: Schema export import [message #517839 is a reply to message #517838]

Thu, 28 July 2011 11:30

Smith14
Messages: 38
Registered: July 2011
Location: India

Member

yes, you are correct black swan.I took export in unix env and running the import in windows prompt.. Sorry got confused... Smile

Report message to a moderator

Re: Schema export import [message #517895 is a reply to message #517839]

Fri, 29 July 2011 04:42

Smith14
Messages: 38
Registered: July 2011
Location: India

Member

I gave the below import command in one file called imp_schema.sh and gave permission to imp_schema.sh file

chmod 777 imp_schema.sh

when i give nohup ./imp_schema.sh & to run the schema import , i get error

/imp_schema.sh: imp: Execute permission denied.

How to fix this?

Report message to a moderator