Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Export hangs on last table in tablespace

Vince Laurent
Respected Contributor

Export hangs on last table in tablespace

(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
4 REPLIES
Hein van den Heuvel
Honored Contributor

Re: Export hangs on last table in tablespace

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
Honored Contributor

Re: Export hangs on last table in tablespace

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
Respected Contributor

Re: Export hangs on last table in tablespace

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
Respected Contributor

Re: Export hangs on last table in tablespace

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