khallas301 wrote:I'll hazard a guess, since it writing to only one file, it means it is still writing your largest tablel.
DB v: 188.8.131.52
expdp file estimation: 70GB
Excludes: 500 tables
Target: Schema refresh
I am trying to export schema excluding 500 odd tables with expdp estimation of 70GB dump file size..
created temp table listing all table which needed to exclude from export and using select query in EXCLUDE parameter... e.g. EXCLUDE=TABLE:'IN (SELECT tbl FROM list_of_tables)
also using FLASHBACK_TIME=TO_TIMESTAMP(TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS') in parfile..
Tried few different things:
1. Used expdp with 3 channels
error: export writes to 3 files till 50GB then stops and only writes to 1 file after 2 hours. First 2 hours writing speed is quick then suddenly it really really slows down... and ora-0155 after waiting for 5 hours..
2. Used expdp without any channels
error: same as above... performance issue occurs after 2 hours... after 5 hours many tables skipped and ora-0155 snapshot old happens....
Trace file mentions about wait on different block but it's not consistent on 1 block...
read 100's of metalink doco but all points different location..
any other ways to tackle this problem??
khallas301 wrote:I mentioned that because we had a situation where one table caused lot of grief for out backup, it just wasn't too big but expdp was too slow on that and it was hurting out EOD performance. Changing access_method basically fixed the issue, hence I suggested you try with it.
Couple of ideas,
1. try using access_method=external_table (it may or may not help but worth a try)
will give it a try...
if we analyze schema before export will that makes difference in export performance?Probably not. If you export and don't specify estimate=statistics, then nothing will change in how parallel is decided. If you specify estimate=statistics, then the statistics will be used to determine what tables get exported with what degree of parallelism. The block count would have to be way off in order for that to change though.