Home » RDBMS Server » Server Utilities » why does an export of an empty table with DATAPUMP over 11 Seconds
why does an export of an empty table with DATAPUMP over 11 Seconds [message #247956] Wed, 27 June 2007 11:27 Go to next message
WolfgangKoenig
Messages: 1
Registered: June 2007
Junior Member
I use DATAPUMP to write an PL/SQL routine to export an table.
The procedure works but i don't know why an export of an empty table takes so long time.
It takes about 10 Seconds.

Then i suppose my routine is wrong and checked the command line utility expdp:
expdp userid=TEST/TEST@PHX dumpfile=tblusers.dmp logfile=tblusers.log tables=tblusers directory=DIR_AKQSICHERUNG CONTENT=DATA_ONLY

Here is the log:
Export: Release 10.2.0.1.0 - Production on Montag, 25 Juni, 2007 14:50:19

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Angemeldet bei: Oracle Database 10g Release 10.2.0.1.0 - Production
"TEST"."SYS_EXPORT_TABLE_01": userid=TEST/********@PHX dumpfile=tblusers.dmp logfile=tblusers.log tables=tblusers directory=DIR_AKQSICHERUNG CONTENT=DATA_ONLY wird gestartet
Sch§tzung erfolgt mit Methode BLOCKS...
Objekttyp TABLE_EXPORT/TABLE/TABLE_DATA wird verarbeitet
Gesamte Sch§tzung mit BLOCKS Methode: 0 KB
. . "SICHERUNG"."TLBUSERS" 0 KB 0 Zeilen exportiert
Master-Tabelle "SICHERUNG"."SYS_EXPORT_TABLE_01" erfolgreich geladen/entladen
******************************************************************************
F│r SICHERUNG.SYS_EXPORT_TABLE_01 festgelegte Dump-Datei ist:
D:\DATEN\XXX\TBLUSERS.DMP
Job "SICHERUNG"."SYS_EXPORT_TABLE_01" erfolgreich um 14:50:31 abgeschlossen

This jobs goes from 14:50:19-14:50:31 for an empty table tblusers !

When i use the old exp the export it takes 1-2 seconds...! Here the log:
exp userid=TEST/TEST@PHX file=tblusers.dmp log=tblusers.log tables=tblusers rows=yes ind
exes=no CONSTRAINTS=no triggers=no

Export: Release 10.2.0.1.0 - Production on Mo Jun 25 14:56:50 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Angemeldet bei: Oracle Database 10g Release 10.2.0.1.0 - Production
Exportieren in WE8MSWIN1252-Zeichensatz und AL16UTF16-NCHAR-Zeichensatz durchgef│hrt
Achtung: Die Tabellenindizes werden nicht exportiert
Achtung: Constraints auf Tabellen werden nicht exportiert

Angegebene Tabellen werden gleich exportiert │ber 'Conventional Path'
. . Export der Tabelle TBLUSERS 0 Zeilen exportiert
Export erfolgreich ohne Warnungen beendet.

Anyone knows why the !new! expdp is so much slower to export an empty table compare to exp?

I'm on a PE Oracle Database 10g Release 10.2.0.1.0 Database running under WINXP. The database and the exp and expdp tools are running on the same machine.
Re: why does an export of an empty table with DATAPUMP over 11 Seconds [message #247958 is a reply to message #247956] Wed, 27 June 2007 11:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
expdp is optimized for big export and do many things to be able to suspend/restart the export and so on.
Have a look at DataPump section in Utilities guide.

Regards
Michel

[Updated on: Wed, 27 June 2007 11:32]

Report message to a moderator

Re: why does an export of an empty table with DATAPUMP over 11 Seconds [message #248040 is a reply to message #247958] Wed, 27 June 2007 18:04 Go to previous message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
I guess EXPDP takes 11 sec because in the mean while it would create MASTER TABLE FOR DATA PUMP jobs.

Quote:
Every Data Pump operation has a master table that is created in the schema of the user running a Data Pump job. The master table maintains information about all aspects of the job, such as the current state of every object exported or imported and its location in the dump file set. In the event of a planned or unplanned job stoppage, Data Pump knows which objects were currently being worked on and whether or not they completed successfully. Therefore, all stopped Data Pump jobs can be restarted without loss of data as long as the master table and dump file set remain undisturbed while the job is stopped.
Previous Topic: Export issues
Next Topic: Privileges required for Datapump / DBMS_DATAPUMP
Goto Forum:
  


Current Time: Sat Dec 03 22:39:08 CST 2016

Total time taken to generate the page: 0.12462 seconds