Does expdp remove data in Oracle?

Question

I am going to perform an upgrade on a Hadoop cluster but am wanting to backup the Ambari metastore schema first in case anything goes wrong.

Oracle is used to store the data, so I looked at using expdp to make a quick backup of the schema in its current state. However, I see in several different documents it is mentioned this is used to "unload" data. Does that mean the data will be removed from the database during the dump process? I want to keep everything in place and just make a quick backup, similar to the Postgres command pg_dump.

When you get conflicting information about a feature you can always go the route of actually testing it for yourself against dummy or test schema. However, I haven't had data deleted/unloaded when using it. — BriteSponge, Mar 05 '18 at 16:16
Right, unfortunately right now I don't have a good place to test this. — Josh, Mar 05 '18 at 16:17
Well a good principle is you don't stuff against a production dataset unless you can test it first. So don't do it until you can test it. The alternative can mean lost/corrupted data, an angry boss and possibly a job search. — BriteSponge, Mar 05 '18 at 16:23
Yes I'm aware. The issue is our RnD environment is configured for Hadoop and if data is removed it would ruin that environment. I was asking here to get an idea before I attempted it and spoke to my team with the options. — Josh, Mar 05 '18 at 16:26
Off topic for this site, but no, `expdp` doesn't remove data from the DB; interesting that 'unload' is ambiguous though. It does create and remove a few things internally as it runs, not not your data. But I would agree with BriteSponge, test it first in another environment - not so much to see if it removes data, though check that for peace of mind too; but more to make sure you know how to re-import it properly. — Alex Poole, Mar 05 '18 at 17:58

score 2 · Accepted Answer · answered Mar 05 '18 at 20:23

Don't worry, your data will stay where it is.

Here's a simple example: I'm exporting Scott's DEPT table. You'll see that data is in the table before and after EXPDP was executed.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> $expdp scott/tiger@xe tables=dept directory=ext_dir

Export: Release 11.2.0.2.0 - Production on Pon O×u 5 21:21:24 2018

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/********@xe tables=dept directory=ext_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.929 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\EXPDAT.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 21:21:34


SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

Does expdp remove data in Oracle?

1 Answers1