• 关于会话、进程、请求的几个常用SQL


    1.检查自己的SID

    SELECT sid FROM v$session WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);



    2. 几个ID之间的关系

    SELECT s.sid session_id, p.spid os_process_id, p.pid oracle_process_id
      FROM v$process p, v$session s
     WHERE p.addr = s.paddr;



    3.杀死Session和进程

    SELECT s.sid session_id,
           p.spid os_process_id,
           p.pid oracle_process_id,
           'alter system kill session ''' || to_char(s.sid) || ',' || to_char(s.serial#) || ''' immediate;' kill_db_session,
           'kill -9 ' || p.spid kill_os_session
      FROM v$process p, v$session s
     WHERE p.addr = s.paddr
       AND s.sid = &sid;
       


    4.正在执行的SQL

       SELECT sql_text
      FROM v$sqltext_with_newlines sqlt, v$session s
     WHERE sqlt.address = s.sql_address
       AND sqlt.hash_value = s.sql_hash_value
       AND s.sid = &session_id
     ORDER BY piece
    



    5.引用对象的Session,锁表session

     SELECT acc.*,
           'alter system kill session ''' || to_char(ses.sid) || ',' || to_char(ses.serial#) ||
           ''' immediate'
      FROM v$access acc, v$session ses
     WHERE acc.OBJECT LIKE upper('TABLE_NAME%')
       AND acc.sid = ses.sid;
    

    6.请求的Session和SQL

    <span style="font-size:18px;">SELECT to_char(s.sid) || ',' || to_char(s.serial#), sql_text
      FROM applsys.fnd_concurrent_requests r,
           v$process                       p,
           v$session                       s,
           v$sqltext_with_newlines         sqlt
     WHERE r.oracle_process_id = p.spid
       AND p.addr = s.paddr(+)
       AND s.sql_address = sqlt.address(+)
       AND s.sql_hash_value = sqlt.hash_value(+)
       AND r.request_id = &request_id
     ORDER BY piece;
    </span>



  • 相关阅读:
    Oracle使用正则表达式拆分字段里多行分布式值
    Oracle--创建TRIGGER实现跟踪用户登录信息
    Oracle传输数据到Sqlserver
    Oracle---智斗ORA01427
    区别集合之间的不同
    maven
    集合与数组的区别
    调用系统命令实现删除文件的操作
    JAVA列出某文件夹下的所有文件
    JVM加载类的原理机制
  • 原文地址:https://www.cnblogs.com/wanghang/p/6299247.html
Copyright © 2020-2023  润新知