Export Hanging at the point of 'exporting views'

[kgb]

Hello,
This weekend I analyzed database by executing
execute DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');
but I got error messge 'ORA-25153 Temporary Tablespace is Empty'
I checked database and I found that there was a missed file in the temporary tablespace 'TEMP'.
in the same time file exists on the disk. I fixed that problem by executing

ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/nuance/temp01.dbf' REUSE;

After that I tried to make a full export of database, but export began hanging at the point
of 'exporting views'. And I got an error message of Server Process bellow.
At the same time analyzing database is hanging as well, but I can analyze schema and make an export at user level without problems.

execute DBMS_UTILITY.ANALYZE_SCHEMA ('BSTORAGE','COMPUTE');

===================================================================

Dump file /usr/local/oracle/admin/nuance/bdump/s000_9296.trc
Oracle8i Enterprise Edition Release 8.1.7.0.1 - Production
With the Partitioning option
JServer Release 8.1.7.0.1 - Production
ORACLE_HOME = /usr/local/oracle/product/8.1.7
System name: Linux
Node name: db03or01
Release: 2.2.18
Version: #2 SMP Thu Jul 12 15:43:45 IST 2001
Machine: i686
Instance name: nuance
Redo thread mounted by this instance: 1
Oracle process number: 8
Unix process pid: 9296, image: oracle@db03or01 (S000)

*** 2002-06-29 13:01:05.083
*** SESSION ID:(10.30136) 2002-06-29 13:01:05.083
Exception signal: 11 (SIGSEGV)
*** 2002-06-29 13:01:05.212
ksedmp: internal or fatal error
Current SQL statement for this session:
SELECT VOBJID, VNAME, VTEXT, VLEN, VOWNER, VOWNERID, VAUDIT, VCOMMENT, VCNAME, PROPERTY, DE
FER, FLAGS, OIDLEN, OIDCLAUSE, TYPEOWNER, TYPENAME, SQLVER FROM SYS.EXU8VEW WHERE VOWNERI
D != 0 ORDER BY VLEVEL, VOWNER
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+142 CALL ksedst()+0
ssexhd()+198 CALL ksedmp()+0 0 ?
pthread_sighandler CALLr 00000000 975F488 ? BFFF9B2C ?
BFFF9AE4 ? 404C61CC ? 0 ?
96C0BC0 ? 404C50F0 ? E ? 6 ?
4042FEBE ? 23 ? 10202 ?
BFFF9AE4 ? 2B ? BFFF9A68 ?
80000000 ? 214C ? 404C61CC ?
975F488 ? 2140 ? 403D2920 ?
0 ? BFFF9B2C ?
sigaction()+616 CALLs ssexhd()+0
===================================================================

I will appreciate it very much for any advice on how to fix that problem.

[Sureshy]

Exception signal: 11 (SIGSEGV)

Can mean many things.

I think you'll have to get Oracle Involved in this one, send them the trace file produced.

[jbeckstrom]

Check if you have stats on the "sys" objects, if so delete the stats.

[puneet]

ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/nuance/temp01.dbf' REUSE;

and not

ALTER TABLESPACE TEMP ADD DATAFILE '/home/oracle/oradata/nuance/temp01.dbf' REUSE;

[kgb]

First answer is correct in case of temp file.

Originally posted by puneet
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/nuance/temp01.dbf' REUSE;
and not
ALTER TABLESPACE TEMP ADD DATAFILE '/home/oracle/oradata/nuance/temp01.dbf' REUSE;

[kgb]

Hi, guys
thanks for replies!
There are 2 session which hanging since last Saturday, they are locking sys objects, and when I trying to execute:

ANALYZE TABLE sys.view$ DELETE STATISTICS;
ANALYZE TABLE sys.user$ DELETE STATISTICS;
ANALYZE TABLE sys.cdef$ DELETE STATISTICS;
ANALYZE TABLE sys.con$ DELETE STATISTICS;
ANALYZE TABLE sys.com$ DELETE STATISTICS;
ANALYZE TABLE sys.typed_view$ DELETE STATISTICS;
ANALYZE TABLE sys.dependency$ DELETE STATISTICS;
ANALYZE TABLE sys.obj$ DELETE STATISTICS;
ANALYZE TABLE sys.sql_version$ DELETE STATISTICS;
ANALYZE TABLE sys.FET$ DELETE STATISTICS;
ANALYZE TABLE sys.UET$ DELETE STATISTICS;this

Any of this statements is hanging.

Is there any other way to kill session in MTS? My dispatcher takes up to 95% CPU, but I can not kill that process, becuase of it will effect on other sessions.

That statement does not really kill session, it is marked for kill several days.

alter system kill session '10,30136';
ORA-00031: session marked for kill

Thanks for any advice.

Originally posted by jbeckstrom
Check if you have stats on the "sys" objects, if so delete the stats.