Home » RDBMS Server » Server Utilities » export dump striping (10g,Linux Itanium)
export dump striping [message #376852] Thu, 18 December 2008 21:50 Go to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi all,
I have a doubt in export. I want to export a schema of around 128GB size . But I want it to be done as 64 dump files of 2GB each. Is it possible ? If so can anyone help me with this?

Thanks in advance,
Arun.
Re: export dump striping [message #376861 is a reply to message #376852] Thu, 18 December 2008 22:50 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Use FILESIZE parameter with expdp command.
Re: export dump striping [message #376907 is a reply to message #376861] Fri, 19 December 2008 02:02 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi Ebrian,
Thanks for replying. I did as you said for a single table of 20G size , the striping of the dump file was successful. But while doing the same for a group of tables as shown below,striping was not successful instead a single dump file was created. Pls help me out


expdp system/systeM4d3v DUMPFILE=DataForJohnCurtin%U.dmp DIRECTORY=DATA_PUMP_DIR  LOGFILE=DataForJohnCurtin1.log  filesize=2G 
tables=AEP.ACAT_BUSINESSRULECONTROL,AEP.ACCOUNTALLOCATIONMAPPING,AEP.ACCOUNTALLOCATIONMAPPINGSTGING,AEP.ACCOUNTCODEMAPPING,AEP.ACCOUNTCO
DEMAPPINGSTAGING,AEP.ACCOUNTDIM,AEP.ACCOUNTNUMBERSTRUCTURE,AEP.ACCOUNTNUMBERSTRUCTURESTAGING,AEP.ACCOUNTSTOCKRECORDSTAGING,AEP.ACCOUNTST
RUCTURERULE,AEP.ALLOCATIONCATEGORIESSTAGING,AEP.ALLOCATIONCATEGORY,AEP.ALLOCATIONCATEGORYSUM,AEP.ALLOCATIONCATEGORYSUMSTAGING,AEP.ALLOCA
TIONDETAIL,AEP.ALLOCATIONEXCEPTIONS,AEP.ALLOCATIONPAIROFFHIERACHYSTGIN,AEP.ALLOCATIONPAIROFFHIERARCHY,AEP.ALLOCATIONPAIROFFS,AEP.ALLOCAT
IONPAIROFFSUM,AEP.ALLOCATIONPAIROFFSUMSTAGING,AEP.ALLOCATIONSOURCE,AEP.ALLOCATIONSUMMARY,AEP.ALLOCATIONTARGET,AEP.BOFA_DUMMY_CUSIPS,AEP.
CATEGORIZEDSTOCKRECORD,AEP.CATEGORIZED_SUMMARY,AEP.CHARTOFACCOUNTCATEGORY,AEP.DUMP_DATA,AEP.EXCEPTIONCATEGORY,AEP.EXCEPTIONLOG,AEP.EXCLU
DERULEJOIN,AEP.FILINGCONTROL,AEP.FILINGPROCESS,AEP.FILINGTYPE,AEP.FINANCIAL1,AEP.FINANCIAL2,AEP.FINANCIAL4,AEP.FINANCIAL5,AEP.FIRM,AEP.F
IRMCHARTOFACCOUNT,AEP.FIRMCHARTOFACCOUNTSTAGING,AEP.FIRMPROFILE,AEP.FIRMSECURITYTYPE,AEP.FIRMSECURITYTYPE1,AEP.FIRM_LNA,AEP.HOLIDAY,AEP.
LOADCONTROL,AEP.MEMOFIELDMETADATA,AEP.MEMOFIELDS_LKP,AEP.NYSEACCT,AEP.NYSEACCT_06,AEP.NYSEACCT_07,AEP.NYSECOA,AEP.NYSECOALOV,AEP.NYSECOA
STAGING,AEP.NYSESECURITYTYPE,AEP.NYSE_SCOA_REPORT_MAPPINGS,AEP.OVERRIDESOURCE,AEP.PLAN_TABLE,AEP.PLSQL_PROFILER_DATA,AEP.PLSQL_PROFILER_
RUNS,AEP.PLSQL_PROFILER_UNITS,AEP.QUERYSQL,AEP.REPORTHDR,AEP.RULEJOIN,AEP.RULERESULTSQL,AEP.RULRESULTSQL_BOFA,AEP.SECURITIES_2CATS,AEP.S
ECURITYALLOCATIONDETAIL,AEP.SECURITYALLOCATIONDETAILSTAGIN,AEP.SECURITYDIM,AEP.SECURITYSTAGING,AEP.SRFOCUS_SUMMARY,AEP.STOCKCOATEMP,AEP.
STOCKCOA_SUMMARY,AEP.STOCKRECORD,AEP.STOCKRECORDEXCEPTIONRULE,AEP.STOCKRECORDGRANDTOTAL,AEP.STOCKRECORDGRANDTOTALSTAGING,AEP.STOCKRECORD
OVERRIDERULE,AEP.STOCKRECORDRULE,AEP.STOCKRECORDSUPPLEMENT,AEP.STOCKRECORDSUPPLEMENTSTAGING,AEP.STOCKRECORD_EXCEPTION,AEP.STOCKRECORD_HR
,AEP.STOCKRECORD_SUMMARY,AEP.STOCKSEC_ACC_CONC_SUMMARY,AEP.STOCKSEC_ACC_SUMMARY,AEP.STOCKSEC_SUMMARY,AEP.SYB_RPTREQUEST,AEP.TEMPALLOCATI
ONPAIROFFS,AEP.TMP_CATEGORIZEDSTOCKRECORD_001,AEP.TMP_FINANCIAL1,AEP.TMP_FINANCIAL2,AEP.TMP_FINANCIAL4,AEP.TMP_FINANCIAL5,AEP.TMP_REPORT
HDR,AEP.TMP_STOCKSEC_ACC_SUMMARY,AEP.TOAD_PLAN_SQL,AEP.TOAD_PLAN_TABLE,AEP.TOP_CUST_LONGSETTLE_SUMMARY,AEP.TOP_CUST_SHORTSETTLE_SUMMARY,
AEP.UNALLOCATED,AEP.XA_FILE_STATUS,AEP.XA_PROCESS_CTRL 


Edited by Moderator to fix formatting.

[Updated on: Fri, 19 December 2008 13:54] by Moderator

Report message to a moderator

Re: export dump striping [message #377065 is a reply to message #376907] Fri, 19 December 2008 13:55 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Not sure why it's not working. This is a small sample from 10.2.0.1 on Linux.

SQL> select segment_name, bytes/1024/1024
  2  from dba_segments
  3  where segment_name in ('TEMP_OBJS', 'TEMP2_OBJS');

SEGMENT_NAME                   BYTES/1024/1024
------------------------------ ---------------
TEMP2_OBJS                                  56
TEMP_OBJS                                   56


SQL> host expdp test dumpfile=temp%U.dmp logfile=temp.log tables=temp_objs,temp2_objs filesize=10m

Export: Release 10.2.0.1.0 - Production on Friday, 19 December, 2008 14:06:03

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security and Data Mining options
Starting "TEST"."SYS_EXPORT_TABLE_02":  test/******** dumpfile=temp%U.dmp logfile=temp.log tables=temp_objs,temp2_objs filesize=10m
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 112 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "TEST"."TEMP2_OBJS"                         47.32 MB  506076 rows
. . exported "TEST"."TEMP_OBJS"                          47.32 MB  506076 rows
Master table "TEST"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_02 is:
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp01.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp02.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp03.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp04.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp05.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp06.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp07.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp08.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp09.dmp
  /oracle/app/oracle/product/10.2.0/rdbms/log/temp10.dmp
Job "TEST"."SYS_EXPORT_TABLE_02" successfully completed at 14:06:23


SQL> host ls -l /oracle/app/oracle/product/10.2.0/rdbms/log/temp*.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp01.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp02.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp03.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp04.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp05.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp06.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp07.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp08.dmp
-rw-r----- 1 oracle dba 10485760 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp09.dmp
-rw-r----- 1 oracle dba  4993024 Dec 19 14:06 /oracle/app/oracle/product/10.2.0/rdbms/log/temp10.dmp


Re: export dump striping [message #377117 is a reply to message #377065] Sat, 20 December 2008 07:32 Go to previous messageGo to next message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Hi Ebrian,
It worked at last!! Actually my colleague ran the expdp command with the filesize parameter after tables parameter initially and it didnt work. Later he ran the same command issuing filesize parameter before the tables parameter and it worked this time. I dont know the reason for this can you explain it?


Thanks,
Arun.
Re: export dump striping [message #377118 is a reply to message #377117] Sat, 20 December 2008 07:57 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks for the update.

I can't explain why that occurred. When I ran it above, I put the FILESIZE parameter after the TABLES parameter.

If the placement of FILESIZE is indeed the problem, then maybe it has to do with the number of tables specified. If you can provide a repeatable test case, then it would appear to be a bug and you could submit an SR with Oracle.

Re: export dump striping [message #377120 is a reply to message #377118] Sat, 20 December 2008 09:00 Go to previous message
arunshrish
Messages: 74
Registered: May 2008
Location: Chennai
Member
Ok Ebrian, Thanks for helping me out.
Previous Topic: SQL Loader and control file changes for different users
Next Topic: import using sql loader in russian character
Goto Forum:
  


Current Time: Fri Dec 02 22:44:52 CST 2016

Total time taken to generate the page: 0.11388 seconds