ORACLE Useful Script

1. Depending on pid find client 根据进程号定位客户机器：

select sid, serial#, username, machine, osuser, process

  from v$session s

 where s.paddr = (select addr from v$process p where p.spid = '&pid');

Windows: orakill <oracle sid> <oracle process>

Example: orakill thorevo 3296

 

2. Dependin on pid find sql 根据进程号定位SQL语句：

select sql_text,sql_fulltext

  from v$sql

 where (address, hash_value) in

       (select sql_address, sql_hash_value

          from v$session s

         where s.paddr =

               (select addr from v$process p where p.spid = '&pid'));

   

3. sql 全文本：

select sql_fulltext

  from v$sql

 where sql_id in

       (select sql_id from v$session where paddr in

 (select addr from v$process where spid = '553368'))

SELECT DISTINCT TO_CHAR(SUBSTR(b.sql_text, 1, 4000))

  FROM sys.WRH$_SQLTEXT b

 WHERE b.sql_id IN

       (SELECT sql_id

          FROM (SELECT a.sql_id

                  FROM sys.WRH$_SQLSTAT a

                 WHERE a.parsing_schema_name NOT IN ('SYS')

                   AND a.executions_total > 0

                   AND a.direct_writes_total > 0

                   AND a.SNAP_ID IN

                       (SELECT SNAP_ID

                          FROM sys.WRM$_SNAPSHOT

                         WHERE to_date('2013:12:23 09:30:00',

                                       'yyyy:mm:dd hh24:mi:ss') BETWEEN

                               begin_interval_time AND end_interval_time)

                 ORDER BY a.direct_writes_total / a.executions_total DESC)

         WHERE rownum <= 10);

select a.sql_text from v$sqltext a where (a.hash_value,a.address) in (

select decode (sql_hash_value,0,prev_hash_value,sql_hash_value),

decode (sql_hash_value,0,prev_sql_addr,sql_address)

from v$session b

where b.paddr=(select addr from v$process c where c.spid='&pid'))

order by piece asc;

4.检查是否存在正在执行未提交的大事务。

select a.sid,

a.serial#,

a.user#,

a.username,

b.addr,

b.USED_UBLK,

b.USED_UREC

from v$transaction b, v$session a

where /*b.addr in (select a.taddr from v$session a where a.sid = '') and*/ b.addr=a.taddr;

5.查看是否存在长时间等待的会话，但是sql_id值为空的非系统会话。

select ff.inst_id,ff.PADDR,'alter system kill session '''||ff.sid||','||ff.serial#||''';' kill,'kill -9 '||vv.spid osid,ff.sql_id,ff.last_call_et,ff.event,ff.PREV_SQL_ID,ff.MACHINE,ff.WAIT_CLASS

from gv$session ff,gv$process vv where ff.paddr=vv.addr and ff.inst_id=vv.inst_id and ff.last_call_et>0

and ff.username is not null and ff.sql_id is null

and ff.status='ACTIVE' and ff.event not like 'SQL%' and ff.event not like 'rdbms%' and ff.program like 'JDBC%' order by ff.last_call_et desc;

6.去生产库抓出index定义；

set long 9999

set pagesize 9999

set linesize 9999

SELECT dbms_metadata.get_ddl('INDEX', index_name) from user_indexes