| why does an export of an empty table with DATAPUMP over 11 Seconds [message #247956] |
Wed, 27 June 2007 11:27  |
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 #248040 is a reply to message #247958] |
Wed, 27 June 2007 18:04  |
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.
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
