DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Export Hanging at the point of 'exporting views'

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688

    Unhappy

    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.
    Best wishes!
    Dmitri

  2. #2
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  3. #3
    Join Date
    Nov 1999
    Location
    Cleveland, Ohio USA
    Posts
    22
    Check if you have stats on the "sys" objects, if so delete the stats.
    Jeffrey Beckstrom
    Database Administrator
    Greater Cleveland Regional Transit Authority
    1240 W. 6th Street
    Cleveland, Ohio 44113

  4. #4
    Join Date
    Nov 1999
    Posts
    226

    Just a small question

    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;

  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688

    Re: Just a small question

    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;
    Best wishes!
    Dmitri

  6. #6
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    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.
    Best wishes!
    Dmitri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width