Oracle Database - Export Utility (exp)

> Database > Oracle Database

1 - About

Client Export utility.

See Oracle Database - Import (imp)

Advertising

3 - Modes

The Export utility supports four modes of operation:

3.1 - Full

Full: Exports a full database. Only users with the EXP_FULL_DATABASE role can use this mode.

The FULL parameter specifies this mode.

3.2 - Tablespace

Tablespace : Enables a privileged user to move a set of tablespaces from one Oracle database to another.

The TRANSPORT_TABLESPACE parameter specifies this mode.

3.3 - User

user: Enables you to export all objects that belong to you (such as tables, grants, indexes, and procedures). A privileged user importing in user mode can import all objects in the schemas of a specified set of users.

The OWNER parameter specifies this mode in Export.

Example: Export all objects of a user

exp user/pwd@connectionString DIRECT=Y LOG=myLog.LOG OWNER=myUser FILE=myFile.dat 

In Oracle9 and higher, the Export utility always exports user data, including Unicode data, in the character sets of the Export server. (Character sets are specified at database creation.)

Then:

imp FILE=scheman FROMUSER=scheman TOUSER=scheman 

Example of Generations:

SELECT 'expdp SYSTEM/oracle ' ||
    'DUMPFILE=' || username || '_' || to_char(sysdate,'YYYYMMDD_HH24MI') || '.dmp ' ||
    'DIRECTORY=DPDIR ' ||
    'PARALLEL=' || cpuCount || ' ' ||
    'LOGFILE=' || username || '_' || to_char(sysdate,'YYYYMMDD_HH24MI') || '.log ' ||
    'schemas=' || username SQL
FROM 
dba_users users,
(SELECT VALUE AS cpuCount FROM v$parameter WHERE UPPER(name) = 'CPU_COUNT') cpu
Advertising

3.4 - Table

Table: Enables you to export specific tables and partitions. A privileged user can qualify the tables by specifying the schema that contains them. For any table for which a schema name is not specified, Export defaults to the exporter's schema name.

The TABLES parameter specifies this mode.

4 - NLS Considerations

In Oracle9 and higher, most data is automaticaly exported in the character set of the database that is exported. The only exception to that, is that data-dictionary information is exported in the character set that is set in the NLS_LANG environment variable when making the export.

In pre-Oracle9 export, ALL data is exported in the character set that is set in the NLS_LANG environment variable when making the export.

set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
exp user/pwd@connectionString DIRECT=Y LOG=myLog.LOG OWNER=myUser FILE=myFile.dat 

see Old Exp/Imp (not datapump) and NLS Considerations (Doc ID 15095.1)

5 - Help

exp help=y

6 - Documentation

db/oracle/exp.txt · Last modified: 2017/09/06 19:28 by gerardnico