一、查询正在执行的存储过程/相关表语句:
select * from v$db_object_cache where type like '%PROCE%' and locks >0 and pins >0;
二、终止正在执行的存储过程/相关语句:
1、找到存储过程/相关表sessionID:
select b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session ' || '''' || b.sid || ',' ||b.SERIAL# || ''';' kill_command
from SYS.V_$ACCESS a, SYS.V_$session b
where a.type = 'PROCEDURE'
and (a.OBJECT like upper('%PROC_NAME%') or
a.OBJECT like lower('%PROC_NAME%'))
and a.sid = b.sid
and b.status = 'ACTIVE';
--PROC_NAME 存储过程名
select b.sid,b.SERIAL#,b.status,a.OBJECT, 'alter system kill session ' || '''' || b.sid || ',' ||b.SERIAL# || ''';' kill_command
from SYS.V_$ACCESS a, SYS.V_$session b
where (a.OBJECT like upper('%TABLE_NAME%') or
a.OBJECT like lower('%TABLE_NAME%'))
and a.sid = b.sid
and b.status = 'ACTIVE';
--TABLE_NAME 相关表名
2、执行上述sql获取的kill_command语句;
alter system kill session '1624,2892';
3、若提醒marked巴拉巴拉失败,表示从Oracle终止进程失败,要去服务器根据spid终止进程,执行下述语句获取spid即服务器进程id
select spid, osuser, s.program from v$session s, v$process p where s.paddr = p.addr and s.sid =1624;
去相关服务器(Linux系统)执行kill -9 8896;
三、其他
1、获取Oracle表所有字段
select obj#, col#, name from sys.col$ where obj# in
(
select object_id from all_objects
where (
object_name like upper('%TABLE_NAME%')
or object_name like lower('%TABLE_NAME%'))
)
order by col#
2、查看所有job
select * from dba_jobs
四、oracle报错
oracle-00904:创建表时报这个错误需要检查下字段是否跟oracle关键字发生冲突,建议重命名字段