• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2216
  • Last Modified:

export schema

SQL> expdp sys/sys directory=expdir dumpfile=expfull.dmp full=y logfile=expfull.og;
SP2-0734: unknown command beginning "expdp sys/..." - rest of line ignored.
earlier I did
alter user sys identified by sys;
create directory expdir as 'C:\exp-dir'
grant read,write on directory expdir to sys;

what could be wrong  here
exp-dir is a folder I manually created ..
0
nobleit
Asked:
nobleit
  • 5
  • 4
  • 3
  • +3
1 Solution
 
slightwv (䄆 Netminder) Commented:
expdp is an OS command not a sqlplus command.
0
 
slightwv (䄆 Netminder) Commented:
also, you already asked and accepted this for the old 'exp' command:

http://www.experts-exchange.com/Database/Oracle/Q_26939169.html
0
 
mrjoltcolaCommented:
Be advised that if you use the default directory, DATA_PUMP_DIR, you don't need to explicitly specify a directory when using expdp. You can point DATA_PUMP_DIR to somewhere useful if its current location isn't good for you.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
nobleitAuthor Commented:
I exported successfully both schema as well as database to os directory.
my point is could we import these exported schema to another database..
using the impdp command...
0
 
slightwv (䄆 Netminder) Commented:
>>these exported schema to another database..using the impdp command...

Yes as long as the version of impdp is the same or greater than the expdp used to export it.
0
 
mrjoltcolaCommented:
>>my point is could we import these exported schema to another database

Maybe you have confused this thread with another?
0
 
schwertnerCommented:
You have to transfer the export file to the other database.
Aftre that use Import to import the schema.
0
 
OP_ZaharinCommented:
"my point is could we import these exported schema to another database..
using the impdp command..."

- yes you can. you need to specify REMAP_SCHEMA=sourceschema:destinationschema in you impdp line.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#BEHFIEIH
0
 
OP_ZaharinCommented:
- to add, you also need to specify the target database instance if its another database

impdp user/password@instance DIRECTORY=DATA_PUMP_DIR DUMPFILE=dump.dmp REMAP_SCHEMA=a:b
0
 
slightwv (䄆 Netminder) Commented:
I don't believe remap schema is the concern here.

Also, even if they specify a target database using a sqlnet connection, the file still needs to be visible to the server so why bother with a remote coonnection?
0
 
nobleitAuthor Commented:
if the source schema and destination schema has the same name remap_schema=scott:scott  
shall it work..
0
 
OP_ZaharinCommented:
- for the same schema name, i don't think so u need specify remap_schema. but is this on a same database or different database?
0
 
nobleitAuthor Commented:
I mean source and target database has same schema name..
0
 
OP_ZaharinCommented:
- for the same schema name, i don't think so u need specify remap_schema.
0
 
slightwv (䄆 Netminder) Commented:
You don't 'need' to but it shouldn't be a problem if you do.

That said:  This question is what was wrong with your expdp command.  The answer to that was in the first post  http:#a35723926: It is not a sqlplus command.

If you have questions about specific expdp parameters, they should be new questions.
0
 
schwertnerCommented:
If the schema exists in the target data base you will face the problem with the existing tables.
If you want to clone the source schema you have first to drop the schema:

DROP USER schema_name CASCADE;

If you don't want to lose the target schema you have to use those parameter of the Pump that tells what to do if the table and rows exist.
0
 
martin_seaCommented:
for importing into the new database and if same schema name exists in the then you need to DROP USER schema_name CASCADE;    else you can also just use the below to just replace the existing tables on the schema without dropping.

normal schema import

1.) impdp sys/sys schema=username directory=impdir dumpfile=expfull.dmp  logfile=impfull.log;

replace the existing tables of the schema if already exists

2.)  impdp sys/sys schema=username directory=impdir dumpfile=expfull.dmp  logfile=impfull.log table_exist_action=REPLACE ;
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now