oracle-databaseexpdporacle-export-dump

Does expdp remove data in Oracle?


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.


Solution

  • 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>