Oracle Database Export

[Mickael_2]

Hello all !

I would like to export a Oracle database:
Server : N4000
OS : HPUX 11.11
size : 400 Go ( 200 Go Data + 200 Go indexes)

I want to only export the data to reduce the export time.
How long does it take to export only the data with the Oracle standard tools ?
Anybody has an idea ?
Thanks

Regards
Mickael

[Vicente Sanchez_3]

Hello,

I've tested about 1Gb/8min full export.

Regards, Vicente.

[Massimo Bianchi]

Hello,
indexes are never exported, they are rebuild at import time.

You will need approximatively as much space as your data, if you want to be flat, and more spaces for rollback segments and temporary tablespace during import.


In my system, i got about 12Gb/h.

Massimo

[Yogeeraj_1]

hi

It depends on how you do it!

You may wish to break down the export into several bits (group of tables A, group of tables B, etc) and launch the them seperately in parallel!! You will thus reduce total time taken significantly compared with a single script to export the whole database.

hope this helps!

regards
Yogeeraj

[Brian Crabtree]

Also, you can use the "DIRECT=Y" option in Oracle 8i+ which should help increase your speed of the export on non-LOB tables.

Brian

[Yogeeraj_1]

Hi again,

to add to my previous reply, the general guidelines for large exports are:
a. use the filesize parameter
b. export smaller pieces
c. export to a device that does not support seeking

Hence, you gain by Exporting table by table (in parallel, just fire off export more then once).

One small trick i often use is as follows:
Just create a parameter file using:

select decode( rownum, 1, 'tables=(', ',' ), table_name
from user_tables
where table_name like 'E%'
union all
select ')', null
from dual


DECODE(R TABLE_NAME
-------- ------------------------------
tables=( E
, EMP
, EMP2
, EMPLOYEE
, EMP_DEMO
, EMP_DEPT
, EMP_SNAPSHOT
)

(spool that to a file) and use parfile= on the EXP command line.

hope this helps!

regards
Yogeeraj

[Yogeeraj_1]

hi,

Brian has a good point about using "DIRECT=Y" to speed up this process!

But you should note that there are 2 cases (from the server utilities guide) where direct path export either will not be used by export OR export will just abort:

a. You cannot export some tables using direct path. For example, you cannot export tables using object features on LOBs. If you specify direct path for export, tables containing objects and LOBs will be exported using conventional path.

b. Direct path Export exports in the database server character set only. If the character set of the export session is not the same as the database character set when an export is initiated, Export displays a warning and aborts. Using the NLS_LANG
parameter, specify the session character set to be the same as that of the database before retrying the export.

Export in direct path mode simply bypasses the SQL evaluation buffer (where clause processing, column formatting and such). 90% of the path is the same, however, EXP is still reading buffers into the buffer cache, doing the same consistent read processing and so on.

The speedup of the direct path export can be large however. The 10% of the processing that it cuts out, accounts for a much larger percentage of the run-time. For example, I have just exported about 100meg of data, and 1.2 million records. The direct path export took about one minute. The conventional path export on the other hand took three minutes.

hope this helps too!

regards
Yogeeraj