Even if you are a sysadmin or developer, when you have Oracle running in your environment, you might encounter a situation where you have to do some basic DBA tasks.
One such task is to export data from an Oracle database. Using Oracle’s exp utility, it is very easy to dump data from database.
In this tutorial, we’ll explain everything that you need to know to use exp command effectively to export data from your Oracle database, including the following:
- Connect to DB in exp utility
- Export full database
- Export one or more specific Schemas/Users
- Export one or more specific Tables
- Export all objects in a Tablespace
- Logging the export messages
- Controlling output dump filename
- Export only selected rows from a table
- Controlling constraints, grants, triggers and indexes
- How does compression work during export
- Using wildcards to export multiple tables
- etc.
There are three methods to connect using exp utility to export data from Oracle. The first three example explains these three different methods. Use any one of them depending on your particular situation.
1. Interactive export command with Default Values
By Default, when you type “exp” command, and hit enter, you are entering the interactive mode, where it will prompt you for various values before exporting the Oracle table data.
First, it will ask for the Oracle schema’s username and password. This username can be the name of the schema that you are planning to export, or a username who has DBA privilege to export other user’s objects.
$ exp
Username: hradmin
Password:
Next, it will ask you for the following:
Export file: expdat.dmp >
(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U >
Export grants (yes/no): yes >
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
User to be exported: (RETURN to quit) > hradmin
User to be exported: (RETURN to quit) >
In the above prompt:
- By default, it will use expdata.dmp as the output file where all the exported objects will be stored.
- By default, it will export the whole schema, as shown in the “(2)U” default selection above. To export the entire database, enter 1. To export specific tables, enter 3.
- Since we selected the default option of “(2)U” for users, you should also specify the Oracle username that should be exported. In this example, we are exporting all the objects that belongs to hradmin username (schema).
Finally, it will display the following output indicating that it is performing the export.
. about to export HRADMIN's tables via Conventional Path ...
. . exporting table BENEFITS 420454 rows exported
. . exporting table EXPENSE_DETAILS 347982 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
..
Export terminated successfully without warnings.
As you see below, the following export file is created.
$ ls -l expdat.dmp
-rw-r--r-- 1 oracle oinstall 40820736 Jan 31 14:21 expdat.dmp
2. Take export From Command Line (non-interactive)
Instead of entering the values interactively, you can specify all of them in the command line. This is very helpful, when you want to script the export command, and take backups from a shell script, or from cron background job.
exp userid=hradmin/mypassword tables=benefits indexes=n
You don’t have to specify a parameter if you are not changing it’s default value. For example, you don’t have to specify “grants=y”, because by default, it will export the grants.
In the above example, we’ve specified “indexes=n”, because by default indexes will be exported. But, in this example, we don’t want indexes to be exported.
Also, note that the data that you export using exp command, can be imported back to the Oracle database using imp command. But, if you want to load data into the Oracle system from a flat text file, then you have to use Oracle sqlldr command to upload the data.
3. Export using a Parameter File for Input Values
If you are repeatedly specifying the same parameters for your exp command, you can use a parameter file, instead of typing those repeatedly from the command line.
For example, create a parameter file called myexp.conf as shown below.
$ vi myexp.conf
tables=benefits
grants=n
triggers=n
Next, specify this parameter file in the PARFILE option of the exp command as shown below. This will export tables from Oracle database based on the parameter specified inside the myexp.conf file.
exp hradmin/mypassword PARFILE=myexp.conf
4. Change the Dump File Name
As we mentioned earlier, by default, the exported filename is always expdat.dmp. You can change this using the “file” parameter as shown below.
In this example, it will export benefits table to benefits.dmp file.
exp userid=hradmin/mypassword tables=benefits file=benefits.dmp
$ ls -l *dmp
-rw-r--r-- 1 oracle oinstall 20414464 Jan 31 14:50 benefits.dmp
5. Write Messages to a Log File
When you are exporting a full database, or when you running the exp command from a script, you want to store the output of the exp command to a log file to later view whether the exp command was successfully completed.
Use “log=” parameter as shown below to specify a log file. In this example, the export log (both success and failure error messages) will be stored in the myexp.log file.
exp userid=hradmin/mypassword tables=benefits log=myexp.log file=benefits.dmp
6. Export Full Database
If you like to take export of all the schemas, all the users, and all the objects in your database, use the full database export mode by specifying “full=y” as shown below.
The user who is exporting full database should have EXP_FULL_DATABASE role to perform this operation.
This will export everything from your Oracle database.
$ exp userid=hradmin/mypassword full=y
About to export the entire database ...
. exporting tablespace definitions
. exporting profiles
. exporting user definitions
. exporting roles
. exporting resource costs
. exporting rollback segment definitions
. exporting database links
. exporting sequence numbers
. exporting directory aliases
. exporting context namespaces
. exporting foreign function library names
. exporting PUBLIC type synonyms
. exporting private type synonyms
...
To perform a full database export, you should have DBA privilege, or at a minimum have EXP_FULL_DATABASE role. If not, you’ll get the following EXP-00023 error message.
$ exp sales/mypassword full=y
Export: Release 11.2.0.1.0 - Production on Sun Jan 31 11:12:51 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
EXP-00023: must be a DBA to do Full Database or Tablespace export
Also, by mistake if you’ve started a full export of a huge database, you can cancel it by pressing Ctrl-C. In this case, it will display the following error message, and terminate the export.
$ exp userid=hradmin/mypassword full=y
About to export the entire database ...
..
EXP-00008: ORACLE error 1013 encountered
ORA-01013: user requested cancel of current operation
EXP-00000: Export terminated unsuccessfully
Note: If your database is very big, the recommended way to backup a full database is using Oracle RMAN backup utility, as we discussed in one of our earlier tutorial.
7. Export All Objects from a Specific Schema or User
In the export user mode, you can export objects that belongs to a specific user (or schema) by specifying “owner=” option as shown below.
In this example, it will export all objects that belongs to hradmin user. If you have DBA privilege, you can also export objects that belongs to a different user.
$ exp userid=hradmin/mypassword owner=hradmin
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HRADMIN
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user HRADMIN
About to export HRADMIN's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export HRADMIN's tables via Conventional Path ...
. . exporting table BENEFITS 420454 rows exported
. . exporting table EXPENSE_DETAILS 347982 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.
If you try to export other users, but if you don’t have DBA privilege, you’ll get the EXP-00032 error message as shown below.
In this example, john doesn’t have DBA privilege to export hradmin’s objects.
$ exp john/mypassword owner=hradmin
EXP-00032: Non-DBAs may not export other users
EXP-00000: Export terminated unsuccessfully
8. Export Multiple Users Together
If you like to export multiple schemas, specify all those usernames in the “owner” parameter by separating them with commas as shown below.
This example will export all objects that belongs to both HRADMIN and SALES schema.
$ exp userid=hradmin/mypassword owner=hradmin,sales
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user HRADMIN
. exporting foreign function library names for user SALES
. exporting PUBLIC type synonyms
..
. exporting private type synonyms
. exporting object type definitions for user HRADMIN
. exporting object type definitions for user SALES
About to export HRADMIN's objects ...
. exporting database links
. exporting sequence numbers
..
About to export SALES's objects ...
. exporting database links
. exporting sequence numbers
..
Export terminated successfully without warnings.
9. Export One or More Specific Table
To export a specific table, use “tables=” option as shown below. This example will export only benefits table.
exp userid=hradmin/mypassword tables=benefits
To export multiple tables at the same time, specify the list of table names in the “tables” parameter by separating them with commas as shown below.
exp userid=hradmin/mypassword tables=benefits,expense_details
Some version of exp command may require you to list multiple tables inside ( ) as shown below.
exp userid=hradmin/mypassword tables="(benefits,expense_details)"
To export tables that belongs to a different schema (if you have access to it), you can specify the schema name in front of the table name in this format: tables=schema_name.table_name
In this example, hradmin user is exporting monthly_estimate table that belongs to sales user.
$ exp userid=hradmin/mypassword tables=sales.monthly_estimate
Export: Release 11.2.0.1.0 - Production on Sun Jan 31 14:06:42 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses UTF8 character set (possible charset conversion)
..
About to export specified tables via Conventional Path ...
Current user changed to SALES
. . exporting table MONTHLY_ESTIMATE 4931 rows exported
Export terminated successfully with warnings.
If you have implemented partitioning, you can also specify a specific partition or sub-partition by specifying in this format: tables=schema_name.table_name:partition_name
For example, the following will export only the partition “california” of the table “monthly_estimate” that belongs to sales user.
exp userid=hradmin/mypassword tables=sales.monthly_estimate:california
If there is no partition called “california”, then exp command will not thrown any error message, it will simply take an export of the whole monthly_estimate table.
10. Dump all objects in a particular Tablespace
Instead of specifying a user, or table, you can also specify a tablespace name using “tablespaces” parameter as shown below.
In this example, it will export all the objects that are on MIDWEST_SALES tablespace.
$ exp hradmin/mypassword TABLESPACES=MIDWEST_SALES
About to export selected tablespaces ...
For tablespace MIDWEST_SALES ...
. exporting cluster definitions
. exporting table definitions
. . exporting table ACTIVATE_CLIENTS 860 rows exported
. . exporting table ACTIVE_PIPELINE 799 rows exported
..
11. Export only Selected Rows from a Table
Instead of exporting all the rows from a table, you can also write a where condition for a table, and export will dump only those rows that matched the given where clause for that particular table.
For example, in the following example, benefits table has a column called v_status. This exp command will export only the rows that contains “INVALID” as value in the v_status column.
$ exp hradmin/mypassword TABLES=benefits query=\"where v_status=\'INVALID\'\"
..
About to export specified tables via Conventional Path ...
. . exporting table BENEFITS 20783 rows exported
As you see from the above output, eventhough BENEFITS table has 420454 rows, only 20783 were exported because only those matched the given where condition.
12. Exclude Triggers and Indexes during Export
By default triggers and indexes will be exported.
If you don’t want Triggers to be exported, use “tiggers=n” as shown below.
exp john/mypassword owner=hradmin triggers=n
If you don’t want Indexes to be exported, use “tiggers=n” as shown below.
exp john/mypassword owner=hradmin indexes=n
On a side note, if you have some syntax error in the parameter, you’ll get EXP-00019 and LRM-00108 error message as shown below.
$ exp sales/mypassword full-y
LRM-00108: invalid positional parameter value 'full-y'
EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully
13. Exclude Constraints and Grants during Export
By default all table constraints and grants will be exported.
If you don’t want table’s constraints to be exported, use “constraints=n” as shown below.
exp john/mypassword owner=hradmin constraints=n
If you don’t want object’s grants to be exported, use “grants=n” as shown below.
exp john/mypassword owner=hradmin grants=n
One thing to keep in mind about grants: When you specify “owner=hradmin” and without “grants=n”, by default, it will export only the grants that are granted by the owner of the table, and not the grants given by someone else.
But, when you take a full database export, using “full=y”, all the grants a particular table are exported, irrespective of who created the grant.
14. Export Multiple Tables using Wildcards
Similar to what you do in a SELECT command, you can use % as wildcards in some of the parameters of exp command.
The following example will export all tables that starts with HR.
exp userid=hradmin/mypassword tables=HR%
The following example will export all tables that ends with STATUS.
exp userid=hradmin/mypassword tables=%STATUS
The following example will export all tables that contains DEV anywhere in the tablename.
exp userid=hradmin/mypassword tables=%DEV%
On a side not, during an export you might see the following EXP-00091 error message. If your table statistics are old, and outdated, you’ll get this error message. You can safely ignore this message.
$ exp userid=hradmin/mypassword tables=HR%
..
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
15. Exclude Table Data (Rows) and Compression
By default, exp command will export all the rows of the tables. If you don’t want the rows to be exported, and want only the table definition, then specify rows=n as shown below.
$ exp hradmin/mypassword TABLES=benefits rows=n
Note: table data (rows) will not be exported
About to export specified tables via Conventional Path ...
. . exporting table BENEFITS
Export terminated successfully without warnings.
Eventhough BENEFITS table has 420454 rows, as you see in the above output, it doesn’t say “420454 rows exported”. Instead it says clearly “table data (rows) will not be exported”.
Regarding compression, by default, exp command will do the compression. If you don’t want compression, use “compress=n” as shown below.
exp john/mypassword owner=hradmin compress=n
It is very important to understand this flag. This is not compressing the exported *.dmp file.
By default, when compress is set to y, exp command will flag the table’s data to be consolidated; meaning when you do an import from a dump file, it will consolidate the table’s data into one initial extent. If your extend sizes are larger, but table data are less than the extend size, imp command will still allocated the initial extent.
But, when compress is set to n, exp command will use the storage parameter that are associated with the table; meaning the initial extent, next extent and other storage parameter will be used.
There is no de-compress option in imp utility when you import the data. So, be careful and decide before doing export itself, whether you want compress=y or compress=n depending on your particular situation.
