查询Oracle版本
select * from v$version
查询当前会话语言
select * from nls_session_parameters where parameter=‘NLS_DATE_LANGUAGE‘;
更改当前会话语言
alter session set nls_date_language=‘AMERICAN‘;
查看一个存储过程上有多少个锁
SELECT * FROM V$DB_OBJECT_CACHE WHERE NAME=upper(‘DIST_BDC_ST_NEW‘) AND LOCKS!=‘0‘;
查询使用存储过程的会话
SELECT VS.SID, VS.SERIAL#, VS.PADDR, ‘ALTER SYSTEM KILL SESSION ‘‘‘ || VS.SID || ‘,‘ || VS.SERIAL# || ‘‘‘ IMMEDIATE;‘ FROM V$ACCESS VA, V$SESSION VS WHERE 1 = 1 AND VA.SID = VS.SID AND VA.OBJECT = UPPER(‘DIST_BDC_ST_NEW‘)
执行命令杀死会话
ALTER SYSTEM KILL SESSION ‘1711,5785‘ IMMEDIATE;
关闭job，命令模式执行，多一个换行符保证两个命令一起执行
exec dbms_scheduler.stop_job(‘DJ_PUBLISHDATA‘); exec dbms_scheduler.disable(‘DJ_PUBLISHDATA‘);
查看死锁，通过杀死会话关闭死锁
SELECT OBJECT_NAME, MACHINE, S.SID, S.SERIAL# FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID;
通过会话id查询会话进程，对于关闭不鸟的会话采用杀进程的方式
SELECT SPID, OSUSER, S.PROGRAM FROM V$SESSION S, V$PROCESS P WHERE S.PADDR = P.ADDR AND S.SID = 24;
杀死进程
kill -9 12345； 或者orakill sid thread;
查看表最后修改记录
SELECT * FROM DBA_OBJECTS T WHERE T.OBJECT_NAME IN (‘BDCQL_DJXX‘)
查看所有oracle视图
select * from dict;

Oracle运维SQL整理