Skip to Main Content
  • Questions

  • Export datapump takes a long time with GUID

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Geraldo.

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

Last updated: July 30, 2020 - 12:23 am UTC

Version: 18.6

Viewed 1000+ 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 Connor 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

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow Up

A reader, July 07, 2020 - 12:58 pm UTC

Thanks for the answer, Connor.

I will open a SR to Oracle.

Regards,
Connor McDonald
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

A reader, July 08, 2020 - 11:53 am UTC

No problem. You've helped me a lot.

I'll keep you posted.

Follow Up

Geraldo, July 29, 2020 - 12:39 pm UTC

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
July 30, 2020 - 12:23 am UTC

Hmmmm....

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Utilities

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