表不存在，但是可以查询、删除（没有返回结果，一直hang住）

最新推荐文章于 2024-01-08 15:21:44 发布

blue_ruth

最新推荐文章于 2024-01-08 15:21:44 发布

阅读量918

点赞数 1

CC 4.0 BY-SA版权

分类专栏： Oracle

本文链接：https://blog.youkuaiyun.com/blue_ruth/article/details/22407763

Oracle 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种Oracle数据库中表操作挂起的问题及其解决方法。问题表现为无法通过常规方式查询或删除某张表，但执行相关操作时会无限期挂起。通过对系统状态的调试和分析，发现大量残留会话未被PMON进程清理，导致资源占用，最终通过手动杀死这些会话解决了问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

问题：用户log_user下用一张表存在如下情况：

C$_0JCZL_CB这张表在user_tables里不存在，查询不到，元数据也查不到，user_segments，user_extents都查不到这张表。

但是select * from log_user.C$_0JCZL_CB;或者drop这张表确实是可以执行的，只不过会被hang住。

一、做systemstate

SQL> oradebug setmypid

Statement processed.

SQL> oradebug unlimit

Statement processed.

SQL> 
oradebug dump systemstate 10

Statement processed.

SQL> 
oradebug tracefile_name

/u01/app/oracle/diag/rdbms/ytyzx/ytyzx1/trace/ytyzx1_ora_76117.trc

#vi /u01/app/oracle/diag/rdbms/ytyzx/ytyzx1/trace/ytyzx1_ora_76117.trc

Session Wait History:
        elapsed time of 0.000789 sec since current wait
     0: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653893 seq_num=32496 snap_id=1
        wait times: snap=2.999524 sec, exc=2.999524 sec, total=2.999524 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000552 sec of elapsed time
     1: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653892 seq_num=32495 snap_id=1
        wait times: snap=2.999279 sec, exc=2.999279 sec, total=2.999279 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000785 sec of elapsed time
     2: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653891 seq_num=32494 snap_id=1
        wait times: snap=2.999487 sec, exc=2.999487 sec, total=2.999487 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000599 sec of elapsed time
     3: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653890 seq_num=32493 snap_id=1
        wait times: snap=3.001268 sec, exc=3.001268 sec, total=3.001268 sec
        wait times: max=3.000000 sec
        wait counts: calls=4 os=4
        occurred after 0.000827 sec of elapsed time
     4: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653889 seq_num=32492 snap_id=1
        wait times: snap=3.000434 sec, exc=3.000434 sec, total=3.000434 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000628 sec of elapsed time

    5: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653888 seq_num=32491 snap_id=1
        wait times: snap=3.000275 sec, exc=3.000275 sec, total=3.000275 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000825 sec of elapsed time
     6: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653887 seq_num=32490 snap_id=1
        wait times: snap=3.000400 sec, exc=3.000400 sec, total=3.000400 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000669 sec of elapsed time
     7: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653886 seq_num=32489 snap_id=1
        wait times: snap=3.000020 sec, exc=3.000020 sec, total=3.000020 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.011046 sec of elapsed time
     8: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653885 seq_num=32488 snap_id=1
        wait times: snap=3.000554 sec, exc=3.000554 sec, total=3.000554 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000509 sec of elapsed time
     9: waited for 'pmon timer'
        duration=0x12c, =0x0, =0x0
        wait_id=2653884 seq_num=32487 snap_id=1
        wait times: snap=3.000333 sec, exc=3.000333 sec, total=3.000333 sec
        wait times: max=3.000000 sec
        wait counts: calls=1 os=1
        occurred after 0.000752 sec of elapsed time
    Sampled Session History of session 99 serial 1
    ---------------------------------------------------
    The sampled session history is constructed by sampling

    the target session every 1 second. The sampling process
    captures at each sample if the session is in a non-idle wait,
    an idle wait, or not in a wait. If the session is in a
    non-idle wait then one interval is shown for all the samples
    the session was in the same non-idle wait. If the
    session is in an idle wait or not in a wait for
    consecutive samples then one interval is shown for all
    the consecutive samples. Though we display these consecutive
    samples in a single interval the session may NOT be continuously
    idle or not in a wait (the sampling process does not know).

    The history is displayed in reverse chronological order.

    sample interval: 1 sec, max history 120 sec
    ---------------------------------------------------
      [121 samples,                                        14:56:10 - 14:58:10]
        idle wait at each sample
    temporary object counter: 0
      ----------------------------------------
      Virtual Thread:
      kgskvt: 0x63ca2e7c8, sess: 0x6387044b0 sid: 99 ser: 1
      vc: (nil), proc: 0x63c6127f8, id: 99
      consumer group cur: _ORACLE_BACKGROUND_GROUP_ (upd? 0), mapped: _ORACLE_BACKGROUND_GROUP_, orig:
      vt_state: 0x100, vt_flags: 0x4030, blkrun: 0, numa: 1
      inwait: 0, posted_run=0
      location where insched last set: kgskthrexit
      location where insched last cleared: kgskthrexit3
      location where inwait last set: NULL
      location where inwait last cleared: NULL
      is_assigned: 0, in_sched: 0 (0)
      qcls: (nil), qlink: FALSE
      vt_active: 0 (pending: 0)
      vt_pq_active: 0, dop: 0
      used quanta: 0 (cg: 0) usec, num penalty: 0
      cpu start time: 0
      idle time: 0, active time: 0 (cg: 0)
      cpu yields: 0 (cg: 0), waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
      io waits: 0 (cg: 0), wait time: 0 (cg: 0) usec
      ASL queued time outs: 0, time: 0 (cur 0, cg 0)
      PQQ queued time outs: 0, time: 0 (cur 0, cg 0)
      Queue timeout violation: 0
      calls aborted: 0, num est exec limit hit: 0
      undo current: 0k max: 0k
      I/O credits acquired:small=0 large=0
      I/O credits waiting for:small=0 large=0
      KTU Session Commit Cache Dump for IDLs:
      KTU Session Commit Cache Dump for Non-IDLs:
    ----------------------------------------

二、分析

查看等待事件都有waited for 'pmon timer' -->竟然是等待PMON TIMER，想想PMON 进程监视器，主要作用1.清理与实例非法断开的server_procese残留的资源非正常终止的用户进程产生的垃圾资源2.负责重启以外死掉的调度器(网络监听中使用的) 3.将实例的信息注册到监听程序。初步怀疑是资源没有清理干净。

在没有人连接log_user用户情况下，查看连接状态

SQL>select a.username,a.sid ,a.serial#,b.spid from v$session a,v$process b where a.paddr=b.addr and a.username='LOG_USER';

USERNAME                  SID    SERIAL# SPID
------------------------------ ---------- ---------- ------------------------
LOG_USER                  248      47495 24190
LOG_USER                  857      52515 76015
LOG_USER                 1054      22901 76017
LOG_USER                 1251      31647 76012
LOG_USER                 1443      47651 76021
LOG_USER               9   3375 76002
LOG_USER                  258      56833 76023
LOG_USER                 1109      32727 52422
LOG_USER                 1133      20557 75990
LOG_USER                 1249   7221 76008
LOG_USER                 1107      55071 32057

.......足足36个残存的资源没有清理。

The database iswaiting for pmon to clean up processes, but pmon is unable to clean them. Theclient connections to the server are causing the shutdown immediate or normalto hang. Killing them allows pmon toclean up and release the associated Oracle processes and resources.

--数据库等待PMON 进程清理这些LOCAL=NO的进程，但是PMON 进程不能清理他们，表的操作就会hang住，kill 掉这些进程，然后让PMON 来清理和释放其他的数据库进程和资源，完成数据库关闭操作。

三、解决办法

就是将这些残存的会话，PMON没有清理掉的会话kill掉。

SQL>set lines 250

SQL>set pages 100

SQL> select 'alter system kill session '||chr(39)||SID||','||SERIAL#||chr(39)||' ;' from v$session where username='LOG_USER';

alter system kill session '9,3375' ;
alter system kill session '171,21175' ;
alter system kill session '179,16085' ;
alter system kill session '221,53417' ;
alter system kill session '226,16347' ;
alter system kill session '258,56833' ;
alter system kill session '269,38591' ;
alter system kill session '274,699' ;
alter system kill session '471,3245' ;
alter system kill session '547,42781' ;
alter system kill session '590,40265' ;
alter system kill session '616,14941' ;
alter system kill session '662,52641' ;
alter system kill session '816,51301' ;
alter system kill session '849,11757' ;
alter system kill session '857,52515' ;
alter system kill session '909,21525' ;

......

因为会话多所以这样写，直接执行下生成的脚本就可以了。

这回查询 select a.username,a.logon_time,a.status,a.sid ,a.serial#,b.spid from v$session a,v$process b where a.paddr=b.addr and a.username='LOG_USER';

发现已经清理完毕。

在处理那个C$_0JCZL_CB这张表，发现就正常了。