{"id":2043,"date":"2020-10-12T07:00:00","date_gmt":"2020-10-12T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2043"},"modified":"2025-06-12T18:10:52","modified_gmt":"2025-06-12T15:10:52","slug":"pos-exagoyme-mazika-dedomena-apo-vasi","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-exagoyme-mazika-dedomena-apo-vasi\/","title":{"rendered":"How to export bulk data from Oracle database using Oracle Data Pump (expdp)"},"content":{"rendered":"

Many times we will need to keep the data of tables and of metadata <\/strong>(packages, procedures, functions, etc.) them so that we can then import them into the same or a different system. The data can be extracted using the tool Oracle Data Pump Export (expdp)<\/strong> while the introduction by using it Oracle Data Pump Import (impdp)<\/strong>.<\/p>\n\n\n\n

The extraction of this information can be said to constitute one "logical backup"<\/em> of this data but in no case is it considered to replace a database backup.<\/p>\n\n\n\n

How Oracle Data Pump Export (expdp) works<\/h2>\n\n\n\n

Calling it Data Pump Export (expdp) <\/strong>a job is created and starts running whose job is to export the data of the tables and their metadata to physical files located on the server called dump files.<\/p>\n\n\n\n

We can refine what will be exported and how by using parameters. These parameters can be declared either on the command line or in a separate parameter file.<\/p>\n\n\n\n

The preliminary work<\/h2>\n\n\n\n

For starters we'll need a user that has the role DATAPUMP_EXP_FULL_DATABASE<\/strong><\/em> or also DATAPUMP_IMP_FULL_DATABASE <\/em><\/strong>if we enter data with the same user. Alternatively we can use user defined SYSDBA <\/strong>although not recommended.<\/p>\n\n\n\n

grant DATAPUMP_EXP_FULL_DATABASE to stratos;<\/pre>\n\n\n\n
We'll need one too logical directory<\/strong><\/em> which will point to a physical directory on the server. In this directory will be exported the dump files of the export or import that we have defined in the parameters to use it.<\/p>\n\n\n\n
create directory fakelos as '\/home\/oracle\/Documents';<\/pre>\n\n\n\n
To see the existing directories:<\/p>\n\n\n\n
select owner,directory_name,directory_path from all_directories;<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
To see the existing tablespaces and the tables they contain:<\/p>\n\n\n\n
select tablespace_name,status,max_size from dba_tablespaces;\n\nselect owner,segment_name,tablespace_name,bytes from dba_segments where tablespace_name = 'USERS' and segment_type = 'TABLE';\n<\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The example<\/h2>\n\n\n\n
The simplest example we can try is to export a small array. The only parameters needed are the logical directory we made earlier, the name of the dumpfile and the name of the table (we have set metrics and logfile to be exported to the same directory as well).<\/p>\n\n\n\n
[opc@dp-gr ~]$ expdp stratos\/stratos directory=FAKELOS dumpfile=pinakas2_dept.DMP logfile=export.log metrics=y TABLES = SCOTT.DEPT<\/pre>\n\n\n\n
Alternatively if we would like to use a parameter file. We would create a file named e.g. params.par which would contain all the parameters one below the other and we would set it with the parameter in expdp parfile<\/strong>.<\/p>\n\n\n\n
directory=FAKELOS\ndumpfile=pinakas2_dept.DMP\nlogfile=export.log\nmetrics=y \nTABLES = SCOTT.DEPT<\/code><\/pre>\n\n\n\n
[opc@dp-gr ~]$ expdp stratos\/stratos parfile=\/home\/oracle\/Documents\/params.par<\/pre>\n\n\n\n
[opc@dp-gr ~]$ expdp stratos\/stratos directory=FAKELOS dumpfile=pinakas_dept.DMP logfile=export.log metrics=y TABLES = SCOTT.DEPT\n\n...\nConnected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production\nWith the Partitioning, OLAP, Advanced Analytics and Real \nTotal estimation using BLOCKS method: 64 KB\nProcessing object type TABLE_EXPORT\/TABLE\/TABLE\n     Completed 1 TABLE objects in 10 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/INDEX\/INDEX\n     Completed 1 INDEX objects in 3 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/CONSTRAINT\/CONSTRAINT\n     Completed 1 CONSTRAINT objects in 4 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS\n     Completed 1 INDEX_STATISTICS objects in 0 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS\n     Completed 1 TABLE_STATISTICS objects in 2 seconds\nProcessing object type TABLE_EXPORT\/TABLE\/STATISTICS\/MARKER\n     Completed 1 MARKER objects in 25 seconds\n. . exported \"SCOTT\".\"DEPT\"                              6.023 KB       4 rows in 0 seconds\n     Completed 1 TABLE_EXPORT\/TABLE\/TABLE_DATA objects in 0 seconds\nMaster table \"STRATOS\".\"SYS_EXPORT_TABLE_01\" successfully loaded\/unloaded\n******************************************************************************\nDump file set for STRATOS.SYS_EXPORT_TABLE_01 is:\n  \/home\/oracle\/Documents\/pinakas_dept.DMP\nJob \"STRATOS\".\"SYS_EXPORT_TABLE_01\" successfully completed \n<\/code><\/pre>\n\n\n\n
To see the Data Pump jobs in progress:<\/p>\n\n\n\n
select * from dba_datapump_jobs <\/pre>\n\n\n\n
\"\"<\/figure>\n\n\n\n
We also have the possibility to do it by having the name of the job (using the previous query). attach <\/strong>in another window and see lots of useful information like completion rate and bytes written.<\/p>\n\n\n\n
[opc@dp-gr ~]$ expdp stratos\/stratos attach=SYS_EXPORT_TABLE_01<\/pre>\n\n\n\n
Alternatively if we use the sysdba account.<\/p>\n\n\n\n
[oracle@dp-gr opc]$ expdp \"'\/as sysdba'\" attach=SYS_EXPORT_TABLE_01<\/pre>\n\n\n\n
\nExport> status\n\nJob: SYS_EXPORT_TABLE_01\n  Operation: EXPORT\n  Mode: TABLE\n  State: COMPLETING\n  Bytes Processed: 6,169\n  Percent Done: 100\n  Current Parallelism: 1\n  Job Error Count: 0\n  Dump File: \/home\/oracle\/Documents\/pinakas_dept.DMP\n    bytes written: 49,152\n\nWorker 1 Status:\n  Instance ID: 1\n  Instance name: ORCL\n  Host name: dp-gr\n  Process Name: DW00\n  State: WORK WAITING\n<\/code><\/pre>\n\n\n\n
This information is updated by writing the command status<\/strong>.<\/p>\n\n\n\n
If for some reason the job stops, e.g. there is not enough space, a message will also appear in the following dynamic view. If we fix it in a reasonable time it will continue automatically.<\/p>\n\n\n\n
select name,status,timeout,error_number,error_msg from DBA_RESUMABLE;<\/pre>\n\n\n\n
The result<\/h2>\n\n\n\n
After the task is completed, we will see that the dump file and the log file have been created in the physical directory.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
<\/p>\n\n\n\n
Useful parameters<\/h2>\n\n\n\n
Let's also look at some useful parameters that we can set in Data Pump Export<\/strong> so that we have better control over the data we will export.<\/p>\n\n\n\n
FULL<\/h5>\n\n\n\n
With this parameter we can export the entire database.<\/p>\n\n\n\n
FULL=y <\/pre>\n\n\n\n
TABLESPACES<\/h5>\n\n\n\n
With this parameter we can export selected tablespaces.<\/p>\n\n\n\n
TABLESPACES=USERS<\/pre>\n\n\n\n
TABLES<\/h5>\n\n\n\n
With this parameter we can export selected tables.<\/p>\n\n\n\n
TABLES=SCOTT.DEPT,SCOTT.SALGRADE<\/pre>\n\n\n\n
DUMPFILE<\/h5>\n\n\n\n
With this parameter we can give a name to the dumpfile that Data Pump Export will produce. However, with the characters "%u" several sequentially numbered files (up to 99) can be created. <\/p>\n\n\n\n
dumpfile=export_pinaka%u.DMP <\/pre>\n\n\n\n
FILESIZE<\/h5>\n\n\n\n
With this parameter we define the maximum size of each dump file. We can e.g. let's set it to 1 GB.<\/p>\n\n\n\n
filesize=1024m <\/pre>\n\n\n\n
SCHEMES<\/h5>\n\n\n\n
With this parameter we can export entire specific user.<\/p>\n\n\n\n
schemas=SCOTT <\/pre>\n\n\n\n
EXCLUDE<\/h5>\n\n\n\n
With this parameter we can exclude specific objects such as schema, tables, etc.<\/p>\n\n\n\n
EXCLUDE=SCHEMA:\"=SYS\"<\/pre>\n\n\n\n
INCLUDE<\/h5>\n\n\n\n
Accordingly, with this parameter we can add specific objects such as schema, tables, etc.<\/p>\n\n\n\n
INCLUDE=SCHEMA:\"=SCOTT\"\n\nINCLUDE=TABLE:\"IN (select table_name from dba_tables where table_name like 'DEP%')\"<\/pre>\n\n\n\n
QUERY<\/h5>\n\n\n\n
With this parameter we can write queries to extract specific data only from one table.<\/p>\n\n\n\n
query=SCOTT.SALGRADE:\"where HISAL > '2000'\"<\/pre>\n\n\n\n
CONTENT<\/h5>\n\n\n\n
We use this parameter when we want to export only the data or only metadata.<\/p>\n\n\n\n
content=data_only \n\ncontent=metadata_only <\/pre>\n\n\n\n
PARALLEL<\/h5>\n\n\n\n
With this parameter we define the simultaneous parallel channels that will perform the task. We usually define the number of CPU cores by two.<\/p>\n\n\n\n
Parallel=8<\/pre>\n\n\n\n
FLASHBACK_TIME<\/h5>\n\n\n\n
According to undo_retention <\/strong>and its size undo tablespace<\/strong> using technology flashback query<\/strong> we can export data with past tense. <\/p>\n\n\n\n
FLASHBACK_TIME=\"TO_TIMESTAMP('20-09-2020 10:00:00', 'DD-MM-YYYY HH24:MI:SS')\"\n<\/pre>\n\n\n\n
LOGFILES<\/h5>\n\n\n\n
In this parameter we define the name of the logfile that will contain information about the process.<\/p>\n\n\n\n
CLUSTER<\/h5>\n\n\n\n
With this parameter we define whether Data Pump will use other nodes in Oracle Real Application Clusters (Oracle RAC) by creating workers on them as well.<\/p>\n\n\n\n
METRICS<\/h5>\n\n\n\n
With this parameter, additional information is displayed in the log file.<\/p>\n\n\n\n
metrics=y<\/pre>\n\n\n\n
VERSION<\/h5>\n\n\n\n
With this parameter we can export data that may be in an older version, e.g. 11.2 in order to import them into a newer e.g. 19.3. If we have not exported them with this parameter, an error will appear during the import process.<\/p>\n\n\n\n
version=11.2<\/pre>\n\n\n\n
<\/p>\n\n\n\n
In the article we analyzed its process Oracle Data Pump Export (expdp)<\/strong>. In the next article we will see how to import this dump file with the process Data Pump Import (impdp)<\/strong>. .<\/a><\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n