Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, I will give information about the Data Export and Export processes.
In order for us to export with the expdp tool:
We should create a database directory object in our database.
We must give read/write rights in this directory to the user who will perform the export operation.
We can learn the directories created in the database by querying the DBA_DIRECTORIES view.
1 | SQL> select directory_name from dba_directories; |
We need to specify which database directory object we will use when defining the export/import operation on the command line.
If we don’t specify it, the default directory object is used. This directory is DATA_PUMP_DIR. You can find out where this directory object is on the operating system by clicking dba_directories.
EXPORT PROCESS
1. If the default directory is not used, the dump_dir directory is created under the ORACLE_HOME directory.
2. The database directory is created.
3. The user who will export is given the right to read/write on this directory. We created a user from scratch and gave the privileges.
4. Before starting the export process, either dba authorization is given to the user ”emre” or a new table is created under the ”emre” user in order to avoid any authorization problems.
We are moving forward by giving DBA authority to ”emre”.
a. Login with sysdba user.
1 2 3 | SQL> grant select on scott.emp to emre; SQL> conn emre SQL> create table emre as (select * from scott.emp); |
b. Login with sysdba user.
1 | SQL> grant dba to emre; |
5. Before running Export, we learn how big the export will be. (Two kinds of syntax can be used when requested to be received with SYS user)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | -bash-3.2$ expdp "'/ as sysdba'" tables=scott.emp estimate_only=y -bash-3.2$ expdp \"/ as sysdba\" tables=scott.emp estimate_only=y Export: Release 11.2.0.4.0 - Production on Fri Aug 28 13:21:12 2015 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=scott.emp estimate_only=y Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . estimated "SCOTT"."EMP" 64 KB Total estimation using BLOCKS method: 64 KB Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Aug 28 13:21:15 2015 elapsed 0 00:00:02 |
6. We learn whether there is a place in the directory where the Export will be taken or not.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | Ø SQL> select * from dba_directories; OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS ACCOUNT /kurulum/account SYS GECICI /gecici SYS GG_GECICI /igoldeng OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS EXPIMP_DUMP /goldengate SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/hosts/mwrapordb01.webtv.local/state OWNER DIRECTORY_NAME ------------------------------ ------------------------------ DIRECTORY_PATH -------------------------------------------------------------------------------- SYS DATA_PUMP_DIR /u01/app/oracle/product/11.2.0/db_1/rdbms/log/ SYS ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/11.2.0/db_1/ccr/state 8 rows selected. |
1 2 3 4 5 6 7 8 | Ø SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options [oracle@mwrapordb01 ~]$ df -h /u01/app/oracle/product/11.2.0/db_1/rdbms/log/ Filesystem Size Used Avail Use% Mounted on /dev/sda3 117G 35G 76G 32% / [oracle@mwrapordb01 ~]$ |
7. The size of the export and the directory where the export will be exported are compared and it is checked whether there is enough space.
If there is enough space:
8. Export is run.
1 | -bash-3.2$ expdp emre/EMRE.123 directory=dump_dir tables=scott.emp dumpfile=emre.dmp logfile=emre.log reuse_dumpfiles=y |
WELL:
With Emre user, we export the scott.emp table to the dump_dir directory with the name emre.dmp and save the logs as emre.log in the same directory.
