Exporting Data from oracle
Oracle provides a command line method for extracting data from the database: export. Use the corresponding import utility to load the data into a destination system.
Note. 11gR2. See for notes on potential problems when taking data from 11gR2
this comes in two forms
- exp (deprecated from oracle 10g )
- expdp (from Oracle 10g onwards)
these programs are located in the ORACLE_HOME\bin directory. eg \oracle\product\11.2.0\bin\ – \ – your path may be different depending on installation preference and operating system.
- Note. Imp can only read files generated by exp. Similarly, impdp can only read files generated by expdp. They are not mutually compatible.
- source exports generated from a higher release than that of the destination database generally will not work – see expdp Compatibility parameter for exception to this rule.
Run the following from the command line as appropriate to your operating system.
eg windows : start > run > cmd
Depending on your local path setting, you may need to change the program source directory to run the program
eg c:\> cd c:\oracle\product\11.2.0\bin\
Using exp
Exp is very useful, especially when executing from a client pc. It is however deprecated and will not be available in future releases.
the command can be run interactively, or as one whole command
to export a schema to a single file:
EXPusername/password FILE=path[filename] LOG=PATH[LOGFILENAME] CONSISTENT=Y eg exp aesuser/password file=\dumps\mydumpfile LOG=\dumps\mylogfile CONSISTENT=Y
- this will export the logged in schema to the given filename.
Additional operators can be added onto this command. :
NO ROW DATA
If you do not require the content data and wish only the METADATA, add the operator ” ROWS=N”
eg
exp aesuser/password file=\dumps\mydumpfile LOG=\dumps\mylogfile ROWS=N
Good practices
- Always take care about CHARSETS when you do export and import. Using the wrong ones can convert your data in a lossy manner. The best situation is when your source and destination database have the same character sets, so you can avoid completely any character conversion. You control this behaviour by setting NLS_LANG environment variable appropriately. When not set properly you may see ‘Exporting questionable statistics’ messages
- You may need to patch your Oracle client (where you are running exp/imp) to the same level as the Oracle server to prevent errors
2. EXPDP
expdp is a powerful took available from Oracle 10g onwards
Requirements:
- expdp program
- [EMEAOPS:oracle_directory] pre-created. An oracle directory is a logical link from the database to a physical location on the network which is visible from the database server
to export a schema to a single file:
EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename] LOGFILE=PATH[LOGFILENAME]
eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile
- this will export the logged in schema to the given filename to the location associated with oracle directory “DUMPS”. A logfile will also be output to that same location. Filename of both as specified in the command.
Expdp comes with many operators, below is not exhaustive, but common requirements below.
- for a complete list see [http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref90]
NO ROW DATA
the operator to use here is
CONTENT=
choices:
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}
- ALL unloads both data and metadata. This is the default.
- DATA_ONLY unloads only table row data; no database object definitions are unloaded.
- METADATA_ONLY unloads only database object definitions; no table row data is unloaded.
example:
EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename] LOGFILE=PATH[LOGFILENAME] CONTENT=xxx eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile CONTENT=metadata_only
Exclude named objects
The exclude operator allows us to
- EXCLUDE=SEQUENCE
EXCLUDE=TABLE:”IN (’EMP’,’DEPT’)”
EXCLUDE=INDEX:”= ‘MY_INDX’”
eg
EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename] LOGFILE=PATH[LOGFILENAME] EXCLUDE=XXXX
eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile EXCLUDE=TABLE:"IN ('PRITEMDATA','PRITEMDATARCHIVE')"
- the above will export the entire schema with the exception of table PRITEMDATA and PRITEMDATAARCHIVE. ** Note exclude/include list is enclosed in double quotes** object name need to be enclosed in single quotes.
Include names objects
- INCLUDE=PROCEDURE:”LIKE ‘MY_PROC_%'”
INCLUDE=TABLE:”‘> ‘E'”
Compatibility
you can set a version to use with expdp for example, if you needed to load the data into an older release than that of the source.
VERSION=
{COMPATIBLE | LATEST | version_string}
- COMPATIBLE – This is the default value. The version of the metadata corresponds to the database compatibility level. Database compatibility must be set to 9.2 or higher.
- LATEST – The version of the metadata corresponds to the database version.
- version_string – A specific database version (for example, 11.1.0). In Oracle Database 11g, this value cannot be lower than 9.2.
EXPDP username/password DIRECTORY=mydirectory DUMPFILE=path[filename] LOGFILE=PATH[LOGFILENAME] EXCLUDE=XXXX VERSION=xxx
eg expdp aesuser/password DIRECTORY=dumps dumpfile=mydumpfile LOGFILE=mylogfile EXCLUDE=TABLE:"IN ('PRITEMDATA','PRITEMDATARCHIVE')" VERSION=11.1
Big Nose Kates 