# by ray

# 2016-05-19

export ORACLE_BASE=/u01/oracle

export ORACLE_HOME=$ORACLE_BASE/11g

export ORACLE_SID=RACDB1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:$ORACLE_HOME/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

[ -e /tmp/kill_session_temptablespace.sql ]&& rm -f /tmp/kill_session_temptablespace.sql

sqlplus /nolog < conn scott/tigger

@/home/oracle/shell/ora-1652_solvent.sql

@/tmp/kill_session_temptablespace.sql

EOF

[ -e /tmp/kill_session_temptablespace.sql ]&& rm -f /tmp/kill_session_temptablespace.sql

######################华丽分割线########################################

######################ora-1652_solvent.sql################################

set termout off;

set echo off;

set feedback off;

set verify off;

set heading off;

set wrap on;

set trimspool on;

set serveroutput on;

set escape on;

set pagesize 50000;

set long 2000000000;

set linesize 300;

spool /tmp/kill_session_temptablespace.sql;

SELECT 'alter system kill session '||chr(39)||S.sid || ',' || S.serial#||chr(39)||';' sid_serial

FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS

WHERE T.session_addr = S.saddr

   AND T.sqladdr = Q.address(+)

   AND T.tablespace = TBS.tablespace_name and T.blocks * TBS.block_size / 1024 / 1024 >=300

ORDER BY S.sid;