ORACLE常用查询

表被锁
+++++++++++++++++++++++++++++++++++++++++++++++++++
--锁表查询
SELECT object_name, machine, s.sid, s.serial#
  FROM gv$locked_object l, dba_objects o, gv$session s
 WHERE l.object_id　 = o.object_id
   AND l.session_id = s.sid;


--过程被锁
select va.sid,va.OBJECT from v$access va where object like 'PKG_MONTHLY_INVOICE%'    


   
--杀进程
ALTER system kill session '463, 27072'; 


+++++++++++++++++++++++++++++++++++++++++++++++++++




查询JOB运行与否
+++++++++++++++++++++++++++++++++++++++++++++++++++
--查询正在运行的JOB
SELECT d.WHAT, t.* FROM DBA_JOBS_RUNNING t, dba_jobs d where t.JOB = d.JOb;
   
--杀进程
ALTER system kill session '463, 27072'; 
 
+++++++++++++++++++++++++++++++++++++++++++++++++++ 




查询执行计划
+++++++++++++++++++++++++++++++++++++++++++++++++++


通过
--查SQL的历史快照
select t.*
  from dba_hist_snapshot t, dba_hist_sqlstat b
 where t.snap_id = b.snap_id
   and t.dbid = b.dbid
   and b.sql_id = 'b6b450bubr8t3'






--查历史
select * from table(dbms_xplan.display_awr('sql_id'));
 
--查当前
select * from table(dbms_xplan.display_cursor('sql_id'));
+++++++++++++++++++++++++++++++++++++++++++++++++++ 




--临时表空间大小
select tablespace_name, sum(bytes) / 1024 / 1024 mb
  from dba_temp_files
 group by tablespace_name;
-- 高水位
select tablespace_name, sum(bytes_cached)/1024/1024 mb
from v$temp_extent_pool
group by tablespace_name;




-- 当前使用表空间
select ss.tablespace_name,
       sum((ss.used_blocks * ts.blocksize)) / 1024 / 1024 mb
  from gv$sort_segment ss, sys.ts$ ts
 where ss.tablespace_name = ts.name
 group by ss.tablespace_name;






====================================


--查询字段里面是否有中文


select *
  from TT_EBIL_CONFIRM_FEE_FOR_BRIM T
 where asciistr(T.BG_CODE) like '%\%'
   and instr(T.BG_CODE, '\') <= 0;




========================================
--查询SQL绑定变量的值


select instance_number,  sql_id,name, datatype_string, last_captured,value_string from 
dba_hist_sqlbind where sql_id='fahv8x6ngrb50'order by LAST_CAPTURED,POSITION;






select sql_id,name, datatype_string, last_captured,value_string from 
v$sql_bind_capture where sql_id='dxfcacn4t4ppw' order by LAST_CAPTURED,POSITION;