delete 删除2600万数据

查看cpu时间最长的sql语句

set line 120

col program for al3;

col username for al0;

select * from (select s. sid, s. serial#, s. sql_id, s.username,s.program,t.cpu_time/1000000 as cpu time_seconds,t.executions

from v$session s, V$sql t where s.sql_id=t.sql_id and s.status='ACTIVE’ order by t.cpu time desc) where romumk=10;

sql_id:0f9m4kd23y237

select sql_text from v$sqltext where hash_value in (select sql_hash value from v$session where spid='&pid')) order by piece

select sid, serial#, username, start_time,ELAPSED_SECONDS, TIME_REMAINING from gv$session_longops where sid=2019

select sid, serial#, usernamE, star_timE,ELAPSED SECONDS, TIME_REMAINING from gv$session_longops where sql id='0f9m4kd23y237'

select sid, serial#, sql_id, username, start_time,ELAPSED_SECONDS, TIIE_REMAINING from v$session_longops where ELAPSED_SECONDS>10 and TIME_REMAINING>O order by ELAPSED_SECONDS;

delete from resuck.ima..sk ri where ri.status=7 and kand ri.createtime<sysdate -5

该语句运行超过4小时，依然没有结束

select * (select ri. createtime from resu..getask ri order by ri.createtime desc ) where romum<=10

SQL> select createtime from pa..1.res..getask where createtime<sysdate-2500

查看执行计划

explain plan for select createtime from pac..1.resu..getask where createtime<sysdate-2500

set autotrace traceonly explain

select createtime from pac..1.res..getask where createtime<sysdate-2500

结束sql

SQL>col osuser for al8

SQL>select p.spid,s. sid, s.serial#,s.username,s. osuser from v$session s, v$process p2where s.paddr=p.addr and s.sid=2019;

SPID  SID  SERIAL# USERNAME OSUSER

6408   2019 44579 PA..1 Administrato

SQL> alter system kill session'2019,44579'

alter system kill session'2019,44579ERROR at line 1:0RA-00031:session marked for kill

 alter system kill session'2019,44579' immediate;

ps -ef |grep -i local=no |grep 6408

kill -9 6408

ps -ef |grep -i local=no |grep 6408

转载原地址： https://blog.itpub.net/25846553/viewspace-3054367/