Skip to Main Content
  • Questions
  • Export datapump takes a long time with GUID

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

Asked: May 06, 2020 - 4:00 am UTC

Answered by: Connor McDonald - Last updated: July 30, 2020 - 12:23 am UTC

Category: Database Administration - Version: 18.6

Viewed 100+ times

You Asked

Hello, Ask TOM Team.

I have a full export data pump backup which took a little more of 2 minutes to finish. Recently I added a GUID column to one of the schema table and updated 7M rows on that column. Now the backup takes 11 minutes (9m just on that table when full backup is executed). If a make a backup to only that table it takes 34 seconds.

1. Why this?

full expdp log (partially copied here):
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
27-APR-20 12:00:05.898: FLASHBACK automatically enabled to preserve database integrity.
27-APR-20 12:00:07.166: Starting "ORACLE"."SYS_EXPORT_FULL_02": /******** DIRECTORY=BK_dbprod DUMPFILE=FULL_dbprod%U_DB_%T_%L.dmp
_DB_20200427_1200.dmp FULL=Y LOGFILE=FULL_dbprod_DB_20200427_1200.log LOGTIME=ALL REUSE_DUMPFILES=Y PARALLEL=2 CLUSTER=NO
27-APR-20 12:02:34.994: . . exported "USER2"."FACT_TRANS" 0 KB 0 rows
27-APR-20 12:11:11.931: . . exported "USER"."TABLE1" 1.149 GB 7176625 rows --> here takes 9 minutes
27-APR-20 12:11:16.686: Master table "ORACLE"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
27-APR-20 12:11:16.793: ******************************************************************************
27-APR-20 12:11:16.795: Dump file set for ORACLE.SYS_EXPORT_FULL_02 is:
27-APR-20 12:11:16.799: /backups/dbprod/FULL_dbprod01_DB_20200427_1200.dmp
27-APR-20 12:11:16.800: /backups/dbprod/FULL_dbprod02_DB_20200427_1200.dmp
27-APR-20 12:11:16.875: Job "ORACLE"."SYS_EXPORT_FULL_02" successfully completed at Mon Apr 27 12:11:16 2020 elapsed
0 00:11:12

table backup log:
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/****@dbprod DIRECTORY=BK_DB_DIR DUMPFILE=table_test.dmp logfile=table_test.log tables=user.table1 parallel=2
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "USER"."TABLE1" 1.149 GB 7176625 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/backups/dbprod/table_test.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon Apr 27 18:16:21 2020 elapsed 0 00:00:34

Another test on May 11, 2020--------------------------------------
full expdp log (partially copied here):
Export: Release 18.0.0.0.0 - Production on Mon May 11 13:10:28 2020
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
11-MAY-20 13:10:31.682: FLASHBACK automatically enabled to preserve database integrity.
11-MAY-20 13:10:33.199: Starting "ORACLE"."SYS_EXPORT_FULL_02": /******** DIRECTORY=BK_DB_DIR DUMPFILE=FULL_dbprod01_DB_%U_DB_20200511_1310.dmp FULL=Y LOGFILE=FULL_dbprod_DB_20200511_1310.log LOGTIME=ALL REUSE_DUMPFILES=Y PARALLEL=2 CLUSTER=NO
.........
11-MAY-20 13:12:35.374: . . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
11-MAY-20 13:12:48.037: . . exported "USER"."TABLE2" 2.386 GB 10132692 rows
11-MAY-20 13:12:52.061: . . exported "USER"."TABLE3" 3.357 GB 42173276 rows
11-MAY-20 13:12:55.245: . . exported "USER"."TABLE4" 792.6 MB 10139317 rows

Note these larger tables that takes a few seconds
.......
11-MAY-20 13:13:10.295: . . exported "USER2"."FACT_TRANS" 0 KB 0 rows

11-MAY-20 13:21:27.756: . . exported "USER"."TABLE1" 1.634 GB 10140305 rows
11-MAY-20 13:21:32.211: Master table "ORACLE"."SYS_EXPORT_FULL_02" successfully loaded/unloaded
11-MAY-20 13:21:32.308: ******************************************************************************
11-MAY-20 13:21:32.310: Dump file set for ORACLE.SYS_EXPORT_FULL_02 is:
11-MAY-20 13:21:32.314: /backups/dbprod/FULL_dbprod01_DB_20200511_1310.dmp
11-MAY-20 13:21:32.315: /backups/dbprod/FULL_dbprod02_DB_20200511_1310.dmp
11-MAY-20 13:21:32.367: Job "ORACLE"."SYS_EXPORT_FULL_02" successfully completed at Mon May 11 13:21:32 2020 elapsed 0 00:11:02


table backup log:
Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/****@dbprod DIRECTORY=BK_DB_DIR DUMPFILE=table_test.dmp logfile=table_test.log tables=user.table1 parallel=2 cluster=no
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "USER"."TABLE1" 1.634 GB 10140305 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/backups/dbprod/table_test.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Mon May 11 13:27:03 2020 elapsed 0 00:00:34

Results: same behavior.

Thanks in advanced.

Regards,

and we said...

Thanks for your patience.

I could not reproduce under 18.3 or 19.7

SQL> create table t_guid tablespace largets
  2  as
  3  select d.*  from dba_objects d,
  4   ( select 1 from dual
  5     connect by level <= 100 );

Table created.

SQL>
SQL> select count(*) from t_guid;

  COUNT(*)
----------
   8226000


Export: Release 19.0.0.0.0 - Production on Mon Jul 6 14:56:48 2020
Version 19.7.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
06-JUL-20 14:56:53.485: Starting "SYSTEM"."SYS_EXPORT_FULL_01":  userid=system/********@db19_pdb1 dumpfile=full.dmp FULL=Y LOGFILE=full.log LOGTIME=ALL REUSE_DUMPFILES=Y directory=d_drive 

...

06-JUL-20 15:00:02.225: . . exported "MCDONAC"."TX"                              1.202 GB 10129860 rows
06-JUL-20 15:00:09.567: . . exported "MCDONAC"."T_GUID"                          1.049 GB 8226000 rows

...
06-JUL-20 15:00:29.130: Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
06-JUL-20 15:00:29.186: ******************************************************************************
06-JUL-20 15:00:29.187: Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
06-JUL-20 15:00:29.188:   D:\ORACLE\ORADATA\FULL.DMP
06-JUL-20 15:00:29.221: Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 1 error(s) at Mon Jul 6 15:00:29 2020 elapsed 0 00:03:39



SQL> alter table t_guid add guid raw(16);

Table altered.

SQL> update t_guid set guid = sys_guid();

8226000 rows updated.

SQL> commit;

Commit complete.

SQL>
SQL> select count(guid) from t_guid;

COUNT(GUID)
-----------
    8226000


06-JUL-20 15:42:53.865: . . exported "MCDONAC"."TX"                              1.202 GB 10129860 rows
06-JUL-20 15:43:02.056: . . exported "MCDONAC"."T_GUID"                          1.210 GB 8226000 rows



You might need to take this one up with Support

and you rated our response

  (3 ratings)

Reviews

Follow Up

July 07, 2020 - 12:58 pm UTC

Reviewer: A reader

Thanks for the answer, Connor.

I will open a SR to Oracle.

Regards,
Connor McDonald

Followup  

July 08, 2020 - 6:40 am UTC

Sorry we couldnt be more help. Please keep us posted - I'd love to find out what the root cause is.

Follow Up

July 08, 2020 - 11:53 am UTC

Reviewer: A reader

No problem. You've helped me a lot.

I'll keep you posted.

Follow Up

July 29, 2020 - 12:39 pm UTC

Reviewer: Geraldo

Hello, Connor. I got an update from Oracle Support.

They told me to re-create the table. I did it and worked.

Just that they did not tell me the root cause. They told me that *maybe* it could happen because the table was created in a prior version. But it was not.

Regards,
Connor McDonald

Followup  

July 30, 2020 - 12:23 am UTC

Hmmmm....

Could you please email the SR# to asktom_us@oracle.com.

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.