EXPDP Filesize : Split or Slice the Dump file into Multiple Directories

Suppose if you database size is big or the size targeted data for the export is too high, you cannot accommodate the dump file in single file. Sometimes the OS will not allow you to create such big files. Also, the available space in one file system or directory is not enough to hold the dump file you can use the FILESIZE parameter to split the dumpfile into multiple pieces and spread across different file systems during the export itself.

Filesize parameter limits the size of the dump file into particular limit.

FILESIZE=integer[B | K | M | G] where B is bytes, K is KB, M is MB and G is GB

The minimum size of the file should be ten times of the default block size.

How to execute this?

  1. You have to create multiple database directory objects in the database as per your requirement.
  2. You don’t need to specify directory option during export as directory will not allow multiple values. You will get following error when you specify multiple values to directory.

LRM-00112: multiple values not allowed for parameter 'directory'

  1. You have to use multiple values for the dumpfile with %u option. Example shown below.

Example

In the following example it created 7 dumpfiles across 2 directories.

expdp job_name=exp_job dumpfile=EXP_DIR:dumpDIR1%u.dmp,DATA_PUMP_DIR:dumpDIR2%u.dmp logfile=dump.log schemas=STHOMAS filesize=8m

Export: Release 11.2.0.3.0 - Production on Thu May 22 08:41:43 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "SYS"."EXP_JOB":  /******** AS SYSDBA job_name=exp_job dumpfile=EXP_DIR:dumpDIR1%u.dmp,DATA_PUMP_DIR:dumpDIR2%u.dmp logfile=dump.log schemas=STHOMAS filesize=8m
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64.25 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "STHOMAS"."HINT_TEST"                       52.38 MB  542392 rows
. . exported "STHOMAS"."OBJ_LIST"                        905.3 KB    9999 rows
. . exported "STHOMAS"."DEPT"                            5.945 KB       4 rows
. . exported "STHOMAS"."EMP"                             8.578 KB      14 rows
. . exported "STHOMAS"."MV_DEPT"                         5.085 KB       4 rows
. . exported "STHOMAS"."SALGRADE"                        5.875 KB       5 rows
Master table "SYS"."EXP_JOB" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.EXP_JOB is:
 /u07/exp_dir/dumpDIR101.dmp
  /u00/app/oracle/admin/mrtst/dpdump/dumpDIR201.dmp
  /u07/exp_dir/dumpDIR102.dmp
  /u00/app/oracle/admin/mrtst/dpdump/dumpDIR202.dmp
  /u07/exp_dir/dumpDIR103.dmp
  /u00/app/oracle/admin/mrtst/dpdump/dumpDIR203.dmp
  /u07/exp_dir/dumpDIR104.dmp
Job "SYS"."EXP_JOB" successfully completed at 08:41:58
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google