topic Re: Oracle Database Export in Operating System - HP-UX

Oracle Database Export

Mickael_2 — Thu, 22 May 2003 08:44:20 GMT

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

Re: Oracle Database Export

Vicente Sanchez_3 — Thu, 22 May 2003 09:37:42 GMT

Hello,

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

Regards, Vicente.

Re: Oracle Database Export

Massimo Bianchi — Thu, 22 May 2003 09:43:37 GMT

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

Re: Oracle Database Export

Yogeeraj_1 — Thu, 22 May 2003 10:10:29 GMT

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

Re: Oracle Database Export

Brian Crabtree — Thu, 22 May 2003 10:18:30 GMT

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

Re: Oracle Database Export

Yogeeraj_1 — Thu, 22 May 2003 10:24:33 GMT

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

Re: Oracle Database Export

Yogeeraj_1 — Thu, 22 May 2003 11:52:27 GMT

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