Oracle Rescue

#######################################查看执行时间长的SQL########################################################
--查询耗时长的sql
select 'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';' as kill_command,
s.sid,
s.blocking_session,
s.machine,
s.osuser,
s.program,
s.username,
s.last_call_et,
a.sql_id,
s.logon_time,
a.sql_text,
a.sql_fulltext,
w.event,
a.disk_reads,
a.buffer_gets
from v$process p, v$session s, v$sqlarea a, v$session_wait w
where 1 = 1
and p.addr = s.paddr
and s.sql_id = a.sql_id
and s.sid = w.sid
and s.status = 'ACTIVE'
and s.program != 'plsqldev.exe'
and s.osuser != 'oracle'
-- and a.sql_text like 'select%'
order by s.last_call_et desc;
#######################################数据库表锁解决方案##########################################################
1)Oracle数据库表锁查询语句(可根据锁的时间推断是否死锁，是否需要清理)
SELECT SESS.SID,
SESS.SERIAL#,
LO.ORACLE_USERNAME,
LO.OS_USER_NAME,
AO.OBJECT_NAME 被锁对象名,
LO.LOCKED_MODE 锁模式,
sess.LOGON_TIME 登录数据库时间,
'ALTER SYSTEM KILL SESSION ''' || SESS.SID || ',' || SESS.SERIAL# || '''' FREESQL
FROM V$LOCKED_OBJECT LO, DBA_OBJECTS AO, V$SESSION SESS
WHERE AO.OBJECT_ID = LO.OBJECT_ID
AND LO.SESSION_ID = SESS.SID
ORDER BY sid, sess.serial#;

2)数据库SQL级清除表锁语句
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

3)特殊情况，数据库SQL级清除表锁语句报错,则需在--数据库所在服务器--执行kill命令
ORA-00031:session marked for kill

4)查询Oracle执行SQL语句所在会话所对应的操作系统进程
select spid, osuser, s.program
from v$session s, v$process p
where s.paddr = p.addr
and s.sid = 1851;

5)在数据库所在服务器--执行kill命令
kill -9 spid

#######################################数据库无法扩展表空间问题#######################################################
ORA-01652（unable to extend temp segment by num in tablespace name）
1)查询临时表空间使用情况
select tablespace_name,
current_users,
total_blocks,
used_blocks,
free_blocks
from v$sort_segment;
2)查询占用临时表空间的session
select DISTINCT se.username,
se.sid,
se.SERIAL#,
su.extents,
su.blocks,
su.blocks * to_number(rtrim(p.value)) / 1024 / 1024 as Space,
tablespace,
segtype,
se.status,
se.logon_time,
se. last_call_et,
to_char(sysdate - (se.last_call_et / (60 * 60 * 24)),
'yyyy-mm-dd hh24:mi:ss') last_work_time,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr;
3)查杀session
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';

4)查询session执行的历史sql
select distinct session_id,
y.sql_id,
l.sql_text,
l.elapsed_time / 1000000,
x.CPU_TIME / 1000000,
x.executions,
x.CPU_TIME / x.executions / 1000000 per_cpu
from v$active_session_history y, v$sql l, v$sqlarea x
where y.sql_id = l.sql_id
and l.hash_value = x.hash_value
and session_id =455
order by per_cpu desc;