Kill blocking sessions

-- ------------
-- Kill blocking sessions RAC aware
-- ------------

set serveroutput on

declare
blockers   VARCHAR2 (1000);
BEGIN
FOR x IN (SELECT lka.SID sessid, ses.serial# serial,
      lka.inst_id instance_id
      FROM gv$lock lka, gv$lock lkb, gv$session ses
      WHERE (lka.id1, lka.id2) IN
      (SELECT id1, id2
      FROM gv$lock
      WHERE request = 0
      INTERSECT
      SELECT id1, id2
      FROM gv$lock
      WHERE lmode = 0)
      AND lka.id1 = lkb.id1
      AND lka.id2 = lkb.id2
      AND lka.request = 0
      AND lkb.lmode = 0
      AND lka.SID = ses.SID
      AND lka.inst_id = ses.inst_id)
LOOP
blockers :=
'ALTER SYSTEM KILL SESSION '''
|| x.sessid
|| ','
|| x.serial
|| ',@'
|| x.instance_id
|| '''';
DBMS_OUTPUT.new_line;
DBMS_OUTPUT.put_line ('Looking for blocking sessions:');
DBMS_OUTPUT.put_line (blockers);
END LOOP;
END;
/

11g RAC KILL remote sessions

-- -------------------------------------
--
-- 11g RAC KILL remote sessions
--
-- -------------------------------------

column username format a12
column Kill format a55
column "Log on" format a25



select  s.inst_id "Inst",
 s.username
, to_char(s.LOGON_TIME, 'MM-MON-RRRR HH24:MI:SS') "Log on"
, 'alter system kill session '''||s.sid||','||s.serial#||',@'||s.inst_id||''' IMMEDIATE;' "Kill"
FROM  gv$session s
WHERE  s.username IS NOT NULL
AND s.username NOT IN ('DBSNMP', 'SYSMAN', 'SYS','PUBLIC')
ORDER BY s.username, s.inst_id, s.LOGON_TIME, substr(s.program,18,10)
/

Import Data pump Generation script

#############################################
# Import Data pump Generation script
#############################################

for i in 1
do

PWD=`pwd`
DPAREX=`sqlplus -s /nolog<<END
connect / as sysdba
set pages 100 lines 220 heading off feedback off
select DIRECTORY_NAME
 from dba_directories
where DIRECTORY_PATH like '${PWD}%'
/
END`
echo -e "The datapump directory is called: = \t${DPARE}\n\n"

if [ -z ${DPAREX} ]
  then echo -e "Incorrect directory - change\n\tABORTING!!!"
else
  echo "Correct directory"
  DPARE=`echo ${DPAREX}`
  export DPARE
  echo ${DPARE}
  echo "Enter schema: "
  read SCHEMA
  TO_SCHEMA=${SCHEMA}
  echo "Enter remap schema from: (press return if not required)"
  read FROM_SCHEMA
  export ORACLE_OWNER=${LOGNAME}
  echo "Enter remap tablespace from: (press return if not required)"
  read F_TSPACE
  echo "Enter remap tablespace to: (press return if not required)"
  read T_TSPACE
  # DT=`date '+%Y%d%m%H%M'`
  DT=`date '+%Y%m%d_%H%M'`
  EXPDF=`ls expdp*.dmp | cut -f-4 -d"_" | uniq`
  TRUNCF=`ls expdp*.dmp | cut -f-4 -d"_" | uniq | cut -f1-3 -d"_" | uniq`
  FBDT=`date "+\"TO_TIMESTAMP('%d/%m/%Y %H:%M:%S','DD-MM-YYYY HH24:MI:SS')\""`
  PCNT=`ls expdp*.dmp | cut -f-4 -d"_" | uniq | wc -l`
  #
  # Test Script
  #
  echo -e "  nohup impdp \"/\" \\"
  if [[ -z ${FROM_SCHEMA} ]]
  then
  echo -e "  schemas=${SCHEMA} \\"
  else
  echo -e "  remap_schema=${FROM_SCHEMA}:${TO_SCHEMA} \\"
  fi
  if [[ -n ${F_TSPACE} ]]
  then
  echo -e "  remap_tablespace=${F_TSPACE}:${T_TSPACE} \\"
  fi
  echo -e "  directory=${DPARE} \\
  dumpfile=${TRUNCF}_%U.dmp \\
  logfile=impdp${SCHEMA}_${DT}.log \\
  JOB_NAME=IMP_${SCHEMA}_${DT} \\
  EXCLUDE=INDEX_STATISTICS \\
  EXCLUDE=TABLE_STATISTICS \\
  EXCLUDE=STATISTICS \\
  TABLE_EXISTS_ACTION=TRUNCATE or REPLACE \\
  CONTENT=DATA_ONLY or METADATA_ONLY \\
  PARALLEL=${PCNT} \\
  metrics=y &"
  #
fi
echo -e "\n\n"
done