Export hangs on last table in tablespace

[Vince Laurent]

(cross posted over at MetaLink)

RDBMS Version: 9.2.0.6
Operating System and Version: HP-UX 11.11
Product: Export

I am trying to export an SAP tablespace, PSAPFACTD. When I get to the end table the oracle process goes to 99% and the export sort of freezes.

#!/usr/bin/csh
export ORACLE_SID=BPR
$ORACLE_HOME/bin/exp user/password filesize=1800M \
tablespaces=PSAPFACTD,PSAPFACTI \
file='/megdisk/factd1.dmp,
/megdisk/factd2.dmp,
/megdisk/factd3.dmp,
/megdisk/factd4.dmp' \
compress=n consistent=y direct=y indexes=y \
buffer=10485760 feedback=5000 \
log=/megadisk/BPRfactd.log

From TOP:
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
0 ? 11976 orabpr 231 20 3263M 6604K run 31:24 99.10 98.93 oracleBPR
1 pts/0 11975 orabpr 154 20 30844K 14764K sleep 6:40 0.39 0.38 exp

This export worked fine on 3 other servers. This is the only server it hangs on. All the OS's are the same. All the oracle versions are the same.

Ideas?
Thanks!
Vince

[Hein van den Heuvel]

Hmm, that surely sounds like a corrupted tablespace.
At the end of the table is probably starts working on the indexes. Maybe one of those is bad? Are you sure you want to export those indexes and not just re-create after a future import?

You may want to export just the table(s) with indexes=n, export the D and I tablespace as transportable tablespace (this takes no time) in case you want to hook them back up. Re-create a fresh D and I tablespace, import that data dump, recreate indexes and re-try to original export.

fwiw, I suspect (with you) that an Oracle forum / metalink entry is probably more appropriate than this forum for help.

Hoep this help some,
Hein.

[Volker Borowski]

Hmm,

any reason for

CONSISTENT=YES ?

Check Transaction ST04 in the Oracle Session Overview to hunt down what process 11976 is actually doing on the DB ?

May be it is just a very big table.
In this case you could try to set parallel degree to a higher value before the export to use several PQX-proc for the full table scan.

Volker

[Vince Laurent]

Thanks for all the replies. What I did was leave it and go home. It finally finished 3 hours later BUT the last dump file size was NOT changed. Wonder what Oracle was doing. Thanks again.

Vince

[Vince Laurent]

I ended up doing nothing. I will dig into ST04 though.