How to Export Database Schema in Oracle to a Dump File

How to export database schema in Oracle to a dump file

It depends on which version of Oracle? Older versions require exp (export), newer versions use expdp (data pump); exp was deprecated but still works most of the time.

Before starting, note that Data Pump exports to the server-side Oracle "directory", which is an Oracle symbolic location mapped in the database to a physical location. There may be a default directory (DATA_PUMP_DIR), check by querying DBA_DIRECTORIES:

  SQL> select * from dba_directories;

... and if not, create one

  SQL> create directory DATA_PUMP_DIR as '/oracle/dumps';
  SQL> grant all on directory DATA_PUMP_DIR to myuser;    -- DBAs dont need this grant

Assuming you can connect as the SYSTEM user, or another DBA, you can export any schema like so, to the default directory:

 $ expdp system/manager schemas=user1 dumpfile=user1.dpdmp

Or specifying a specific directory, add directory=<directory name>:

 C:\> expdp system/manager schemas=user1 dumpfile=user1.dpdmp directory=DUMPDIR

With older export utility, you can export to your working directory, and even on a client machine that is remote from the server, using:

 $ exp system/manager owner=user1 file=user1.dmp

Make sure the export is done in the correct charset. If you haven't setup your environment, the Oracle client charset may not match the DB charset, and Oracle will do charset conversion, which may not be what you want. You'll see a warning, if so, then you'll want to repeat the export after setting NLS_LANG environment variable so the client charset matches the database charset. This will cause Oracle to skip charset conversion.

Example for American UTF8 (UNIX):

 $ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Windows uses SET, example using Japanese UTF8:

 C:\> set NLS_LANG=Japanese_Japan.AL32UTF8

More info on Data Pump here: http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm#g1022624

how to export schema and import it to another schema in PL\SQL Developer

PL/SQL Developer has tools Export Tables and Import Tables witch can import/export dmp files using EXP and IMP utilites. See PL/SQL Developer's help :

Export Tables:

The Export Tables tool allows you to export one or more table definitions and their data into a file, so that you can import the tables later. After starting the Export Tables tool, you can select the user and the
tables you wish to export, choose an export method (Oracle Export, SQL Inserts, or PL/SQL Developer), and set various options that apply to the export method...

Import Tables:

The Import Tables tool allows you to import table definitions and data from a file that was previously exported with the Export Tables tool described in the previous chapter. Just like with the Export Table
tool, there are 3 methods to import tables, each with its own file format...

P.S. As you see schema where you want import to must already exists.

But in such a way you can export/import only tables.
So if you want export whole schema use utility througth command line, see example:

1. Command to export a schema:

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

   This will create the export dump file.

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

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

3. Then import the data:

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

How to export 100 Oracle schemas to dump files

You can export using expdp multiple schemas using this line:

expdp schemas=SCOTT,SH directory=YOUR_DIRECTORY dumpfile=schemas.dmp  
logfile = schemas.log

Check this example exports schemas SCOTT and SH. Before execute this command you must create the directory in your database like:

CREATE DIRECTORY YOUR_DIRECTORY AS 'C:\Desktop'  -- where dumpfile will be saved

How to determine the Schemas inside an Oracle Data Pump Export file

If you open the DMP file with an editor that can handle big files, you might be able to locate the areas where the schema names are mentioned. Just be sure not to change anything. It would be better if you opened a copy of the original dump.

How do you export an exact copy of an Oracle database 11g

Use a data pump. Look up the oracle tool expdp.

https://oracle-base.com/articles/10g/oracle-data-pump-10g

---

Related Topics