特殊的SQL
<ul>
<li>查看最后一次数据</li>
</ul>
<pre><code>select * from (
select sn,
proc_name,
test_time,
pn,
wo,
row_number() over(partition by sn order by test_time desc) rn
from nvt_process_status
where sn in (select * from pdm2))
where rn=1</code></pre>
<ul>
<li>查看system表空间哪个段使用最多</li>
</ul>
<pre><code>SELECT *
FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSTEM'
GROUP BY SEGMENT_NAME
ORDER BY 2 DESC)
WHERE ROWNUM < 10;</code></pre>
<ul>
<li>运行天数</li>
</ul>
<pre><code>select to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') started_at,
TRUNC(sysdate - (startup_time))||'day(s),'||TRUNC(24*((sysdate-startup_time) -TRUNC(sysdate-startup_time)))
||'hour(s),'||MOD(TRUNC(1440*((SYSDATE-startup_time)-
TRUNC(sysdate-startup_time))),60)
||'minutes(s),'||MOD(TRUNC(86400*((SYSDATE-STARTUP_TIME)-
TRUNC(SYSDATE-startup_time))),60)
||'seconds' uptime
from v$instance;</code></pre>
<h2>杀进程，表锁死</h2>
<ul>
<li>查看锁表信息</li>
</ul>
<pre><code>select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;</code></pre>
<ul>
<li>输入t2.sid查看对应的sql</li>
</ul>
<pre><code>select sql_text from v$session a ,v$sqltext_with_newlines b
where
decode(a.sql_hash_value,0,prev_hash_value,sql_hash_value)=b.hash_value
and a.sid=&sid order by piece;</code></pre>
<ul>
<li>输入't2.sid,t2.serial#'杀进程</li>
</ul>
<pre><code>alter system kill session '168,15753';</code></pre>