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

问题：用户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这张表，发现就正常了。