CODEWITHSUNDEEP
oracle-databaseplsqldeveloper
Gokul
Gokul

Reputation:

How to create a dump with Oracle PL/SQL Developer?

I need to take dump of a user (including tables, procedures ,etc.) as FILENAME.dmp.

If I create a new user and import that FILENAME.dmp, then everything should be created.

How can I create this dump file?

Don't tel me to use the Run > EXP or Run > IMP functions because, due to some problem, that feature is not working for me.

Upvotes: 12

Views: 137083

Answers (5)

Babar Siddique
Babar Siddique

Reputation: 36

There are some easy steps to make Dump file of your Tables,Users and Procedures:

Goto sqlplus or any sql*plus connect by your username or password

  1. Now type host it looks like SQL>host.
  2. Now type "exp" means export.
  3. It ask u for username and password give the username and password of that user of which you want to make a dump file.
  4. Now press Enter.
  5. Now option blinks for Export file: EXPDAT.DMP>_ (Give a path and file name to where you want to make a dump file e.g e:\FILENAME.dmp) and the press enter
  6. Select the option "Entire Database" or "Tables" or "Users" then press Enter
  7. Again press Enter 2 more times table data and compress extent
  8. Enter the name of table like i want to make dmp file of table student existing so type student and press Enter
  9. Enter to quit now your file at your given path is dump file now import that dmp file to get all the table data.

Upvotes: 0

sayannayas
sayannayas

Reputation: 774

Just as an update this can be done by using Toad 9 also.Goto Database>Export>Data Pump Export wizard.At the desitination directory window if you dont find any directory in the dropdown,then you probably have to create a directory object.

CREATE OR REPLACE DIRECTORY data_pmp_dir_test AS '/u01/app/oracle/oradata/pmp_dir_test';

See this for an example.

Upvotes: 1

Guy
Guy

Reputation: 9816

EXP (export) and IMP (import) are the two tools you need. It's is better to try to run these on the command line and on the same machine.

It can be run from remote, you just need to setup you TNSNAMES.ORA correctly and install all the developer tools with the same version as the database. Without knowing the error message you are experiencing then I can't help you to get exp/imp to work.

The command to export a single user:

exp userid=dba/dbapassword OWNER=username DIRECT=Y FILE=filename.dmp

This will create the export dump file.

To import the dump file into a different user schema, first create the newuser in SQLPLUS:

SQL> create user newuser identified by 'password' quota unlimited users;

Then import the data:

imp userid=dba/dbapassword FILE=filename.dmp FROMUSER=username TOUSER=newusername

If there is a lot of data then investigate increasing the BUFFERS or look into expdp/impdp

Most common errors for exp and imp are setup. Check your PATH includes $ORACLE_HOME/bin, check $ORACLE_HOME is set correctly and check $ORACLE_SID is set

Upvotes: 23

Matthew Watson
Matthew Watson

Reputation: 14253

Export (or datapump if you have 10g/11g) is the way to do it. Why not ask how to fix your problems with that rather than trying to find another way to do it?

Upvotes: -1

Simon
Simon

Reputation: 2008

Just to keep this up to date:

The current version of SQLDeveloper has an export tool (Tools > Database Export) that will allow you to dump a schema to a file, with filters for object types, object names, table data etc.

It's a fair amount easier to set-up and use than exp and imp if you're used to working in a GUI environment, but not as versatile if you need to use it for scripting anything.

Upvotes: 9

Related Questions