Solved

export schema

Posted on 2014-02-25
8
296 Views
Last Modified: 2014-11-01
To export schema..
in the following version
what do you prefer to export

exp or expdp

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
0
Comment
Question by:thomasliju
8 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 39886179
expdp is Export Data Pump

exp is just export - which is the older version, included for backward compatibility.

Expdp is usually preferred - it's generally faster.

See the docs: http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#SUTIL200
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 125 total points
ID: 39886191
I typically use the classic exp.  You can run this from a remote client and have the file saved on that remote client.

The datapump version places the file on the database server.
0
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 250 total points
ID: 39886206
True - for data pump you do need to create a "directory" inside the instance so that Oracle can write to the server file system and grant permissions on that directory to the user who is doing the export.

Also, on the import side, impdp doesn't seem to handle referential integrity constraints very well - where as imp seems like it will load all the data and then enable constraints at the end.

However, impdp gives you great flexibility in remapping users and tablespaces on the import that imp does not.

I guess you need to find out what your requirements are and use what suits your individual requirements best.

I've found that Datapump is generally faster, but from time to time there are still databases I have where I use import / export.

As in many database related questions, there are not necessarily absolute answers.  The answer is often "it depends" :)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Assisted Solution

by:manzoor_dba
manzoor_dba earned 125 total points
ID: 39888211
I will prefer and we are using the expdp for schema / table / full export, which is fastest and convenient when compared to exp.
0
 

Author Comment

by:thomasliju
ID: 40383694
datapump worked.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40384041
thomasliju,

Your closing comment mentioned datapump worked but you selected an answer that didn't suggest using it.

Please take the time to close your questions properly.  I will have a Moderator take a look at this question.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle function works in 11g but not in 12c 21 72
Identify records which do NOT qualify for a view 9 36
sql for Oracle views 8 49
passing parameters to sql script oracle 4 27
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now