Are you looking for an easy way to export your data from Oracle? If yes, then you are in the right place. This blog focuses on exporting a complete database, a schema, a tablespace, and a table from Oracle using the expdp utility. Oracle 10g or a higher version of Oracle uses Data Pump for exporting and importing data. Previous versions of Oracle used ‘exp’ for exporting and ‘imp’ for importing. Oracle prefers exporting data using ‘expdp’ because it is a fast and flexible process compared to ‘exp’ utility. It also supports PL/SQL API.
So, if you are an Oracle user who wants to export their data effortlessly, then this blog will answer all your queries.
This blog will take you through the following topics:
Table of Contents
Prerequisites
- Oracle database 10g or above.
- Knowledge about RDBMS (relational database).
- Need to have access to a privileged user.
Introduction To Oracle
Image Source: Pinterest
Oracle Corporation introduced the Oracle database as a multi modelled relational database management system. They used to store the data in a single unit, retrieve the data, and manipulate it according to your needs. It provides a logical structure and a physical structure of your data. It was the first database based on enterprise grid computing. The latest Oracle database version is Oracle 11c.
Various features of Oracle database are listed below:
- Oracle offers recovery in case of failure.
- Oracle offers Business Intelligence features such as data warehousing, ETL, etc.
- Oracle offers high security by preventing unauthorized access and auditing user actions.
- Oracle offers portability on all platforms.
Introduction To Data Pump
Image Source: KTExperts
Oracle’s Data Pump offers export and import utilities. It is available only on Oracle 10g and later. It is a server-based technology of Oracle. The Data Pump provides high-speed movement of data and metadata.
Data Pump includes two utilities:
Exporting data from the Oracle database to another destination is done using expdp. Importing data from the Oracle database to another destination is done using impdp. All the files which have been exported can only be imported back using impdp. The data which can be exported, can either be a complete database or a subset of the database such as tables, tablespace, etc. Data Pump is used to add another layer of security to your data.
expdp: Tool Overview
Oracle database uses expdp utility to export data from the database. There are various reasons why we export data from one platform to another.
You can also read our article about ‘Oracle Data Pump Export‘.
Let’s look at some of the advantages of expdp:
- The process of exporting data and metadata using expdp is to add another layer of security.
- It also provides a backup of your data.
- They are 15-50% faster than traditional exports.
- It offers enhanced performance as it is a server-based technology.
- The data that is exported using expdp is always encrypted.
Hevo is a No-code Data Pipeline. It is a fully automated platform. Data is brought from 100+ sources in real-time to your data warehouse. It offers some necessary features that make it the best solution for exporting data from Oracle.
Get Started with Hevo for Free
Let’s discuss some unbeatable features of Hevo Data:
- Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
- Easy To Use: As it is a fully managed system, hence the setup and implementation are an easy process.
- Auto Mapping: It can automatically create the schema by mapping the data from the source schema to the destination schema.
- Security: It offers end to end encryption and two-factor authentication for security.
- 24×7 Support: Hevo has a dedicated team to resolve your issues and provide a helping hand whenever you need it.
- Fault-Tolerant: Hevo automatically identifies any anomaly in the source data and notifies you simultaneously.
- Scalability: Hevo can handle a large number of records in minutes without latency and ensures that the data pipeline is scaled as per your requirement.
Give Hevo a try by signing up for a 14-day free trial and experience hassle-free data export from Oracle.
Sign up here for a 14-Day Free Trial!
Download the Ultimate Guide on Database Replication
Learn the 3 ways to replicate databases & which one you should prefer.
Steps To Export Data
Data can be exported from Oracle database in the following ways:
1. Export A Complete Database
Let’s see what you will cover here:
- Create A Directory
- Create A Directory Object
- Grant Permission To The User
- Export Database
Step 1: Create A Directory
This step should be done by a privileged user only on the server. For example, I have used this location for my directory:
‘E:OracleExportCompleteExport’
Step 2: Create A Directory Object
A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.
To create a directory object follow these steps:
a. Open command prompt and open sqlplus as ‘sys’ user.
sqlplus / as sysdba
b. Create an object for the directory by executing the following command.
create directory directory_object_name as 'location’;
Step 3: Grant Permission To The User
Grant permission, such as read and write on the directory to the user who wants to export the data.
grant read,write on directory directory_object_name to user_name;
Grant export privilege to the user using ‘datapump_exp_full_database’.
grant datapump_exp_full_database to user_name;
Step 4: Export Database
‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt.
expdp user_name/user_password DIRECTORY=directory_object_name DUMPFILE=dump_file_name.dmp LOGFILE=log_file_name.log full=y
In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.
2. Export A Schema
Schema can be termed as a collection of the logical structure of data or objects. For example, views.
Let’s see what you will cover here:
- Create A Directory
- Create A Directory Object
- Grant Permission To The User
- Export Schema
Step 1: Create A Directory
This step should be done by a privileged user only on the server.
‘E:OracleExportExport Schema’
Step 2: Create A Directory Object
A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.
To create a directory object follow these steps:
a. Open command prompt and open sqlplus as ‘sys’.
sqlplus / as sysdba
b. Create an object for the directory by executing the following command.
create directory directory_object_name as 'location’;
Step 3: Grant Permission To The User
Grant permission, such as read and write on the directory to the user who wants to export the data.
grant read,write on directory directory_object_name to user_name;
Grant export privilege to the user using ‘datapump_exp_full_database’.
grant datapump_exp_full_database to user_name;
Step 4: Export Schema
‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt.
expdp user_name/user_password DIRECTORY=directory_object_name DUMPFILE=dump_file_namel.dmp LOGFILE=log_file_name.log schemas=schema_name
In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.
3. Export Tablespace
Tablespace refers to the storage area where the database stores data logically. So, when you say exporting a tablespace, you mean exporting all the tables in that storage area along with all the dependent objects of that table.
Let’s see what you will cover here:
- Create A Directory
- Create A Directory Object
- Grant Permission To The User
- Export Tablespace
Step 1: Create A Directory
This step should be done by a privileged user only on the server.
‘E:OracleExportExport Tablespace’
Step 2: Create A Directory Object
A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.
To create a directory object follow these steps:
a. Open command prompt and open sqlplus as ‘sys’.
sqlplus / as sysdba
b. Create an object for the directory by executing the following command.
create directory directory_object_name as 'location';
Step 3: Grant Permission To The User
Grant permission, such as read and write on the directory to the user who wants to export the data.
grant read, write on directory directory_object_name to user_name;
Grant export privilege to the user using ‘datapump_exp_full_database’.
grant datapump_exp_full_database to user_name;
Step 4: Export Tablespace
‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt.
expdp user_name/user_password directory=directory_object_name dumpfile=dump_file_name.dmp log=log_file_name.log tablespace=tablespace_name
In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.
4. Export Table
Tables can be exported using the ‘expdp’ command.
Let’s see what you will cover here:
- Create A Directory
- Create A Directory Object
- Grant Permission To The User
- Export Table
Step 1: Create A Directory
This step should be done by a privileged user only on the server.
‘E:OracleExportExport Tables’
Step 2: Create A Directory Object
A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.
To create a directory object follow these steps:
a. Open command prompt and open sqlplus as ‘sys’.
sqlplus / as sysdba
b. Create an object for the directory by executing the following command.
create directory directory_object_name as 'location';
Step 3: Grant Permission To The User
Grant permission, such as read and write on the directory to the user who wants to export the data.
grant read,write on directory directory_object_name to user_name;
Grant export privilege to the user using ‘datapump_exp_full_database’.
grant datapump_exp_full_database to user_name;
Step 4: Export Table
‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt.
expdp user_name/user_password directory=directory_object_name dumpfile=dump_file_name.dmp logfile=log_file_name.log tables=table_name
In the given command, ‘expdp’ performs the export operation and then loads the data in an operating system file called a dump file. ‘user_name/user_password’ is used to login to the database. Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_namel.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation.
5. Export A Table Using PAR file
PAR file is a parameter file with .par extension. You can use this file in your ‘expdp’ command.
Let’s see what you will cover here:
- Create A Directory
- Create A Directory Object
- Grant Permission To The User
- Create A PARFILE
- Export Table
Step 1: Create A Directory
This step should be done by a privileged user only on the server.
E:OracleExportExport Table
Step 2: Create A Directory Object
A directory object is like a pointer pointing to the directory. It will be used by expdp for reference. This step should be done by privileged users only, for example, sys user. Also, this step includes granting some privileges to the object.
To create a directory object follow these steps:
a. Open command prompt and open sqlplus as ‘sys’.
sqlplus / as sysdba
b. Create an object for the directory by executing the following command.
create directory directory_object_name as 'location';
Step 3: Grant Permission To The User
Grant permission, such as read and write on the directory to the user who wants to export the data.
grant read,write on directory directory_object_name to user_name;
Grant export privilege to the user using ‘datapump_exp_full_database’.
grant datapump_exp_full_database to user_name;
Step 4: Create A PARFILE
First, identify the location of your parfile. You can choose your desired location.
‘E:OracleExport’
Create a text document and save the name with .par extension.
‘par_file_name.par’
In the par file, specify the directory object name, dump filename, log filename, and the name of the tables that you want to export.
directory=directory_object_name
dumpfile=dump_file_name.dmp
logfile=log_file_name.log
tables=table_name
Directory parameter ‘directory_object_name’ indicates the location where exported data (dump and log file) will be placed. ‘dump_file_name.dmp’ is the dump file containing the data. ‘log_file_name.log’ is the log file that contains the information regarding the export operation. Tables are used to define the name of the tables which you want to export.
Step 5: Export Table
‘expdp’ is a command prompt operation, hence exit from SQL and perform the ‘expdp’ command in command prompt.
expdp user_name/user_password parfile='location’;
In the given command, ‘expdp’ performs the export operation. ‘user_name/user_password’ is used to login to the database. Parfile determines the location of the parameter file from where they can fetch the directory object, dump file, log file, and table names.
Conclusion
The Data Pump export utility ‘expdp’ is used to make a copy of the data and metadata and store them in operating system files, called as dump files (extension .dmp). These dump files have disks that contain table data, database metadata, etc. But using expdp utility to export data from the Oracle database is a complex and time-consuming process. To avoid all these challenges, you can directly opt for a fully automated No-code Data Pipeline, Hevo. Hevo will not only migrate your data from the Oracle database to your desired location but will also make sure that your data is safe and consistent.
Visit our Website to Explore Hevo
Give Hevo a try by and Sign Up up for a 14-day free trial today.
Let’s know about your experience of exporting your data from Oracle in the comment section below.