Connections oracle

Comment gérer les connections des utilisateurs a une base Oracle.

Liste des utilisateurs connectés

Comment récupérer la liste des utilisateurs connectés à un schéma donné sur une base Oracle Donnée. (Dans cet exemple on cherche les utilisateurs connectés au schema 'jean')

   export ORACLE_SID=dbname
   sqlplus / as sysdba

   select sid, serial# from v$session where upper(username)=upper('jean');
      SID    SERIAL#
   ---------- ----------
      733       6738

Ici une connexion existe. On peut également faire une requête sur les autres champs de la vue v$session:

   osuser     le nom de l'utilisateur au niveau du system d'expoitation
   machine    l'ordinateur utilisé pour la connexion
   status     l'état de la connexion
   program    le programme utilisé pour accéder à la base (sqlplus, une application...)

Déconnecter les utilisateurs

Principe

La connexion est classique:

   export ORACLE_SID=dbname
   sqlplus / as sysdba

Puis il faut trouver les couples "SID + SERIAL#" de chacune des connexions:

   select sid, serial# from v$session where upper(username)=upper('jean');
      SID    SERIAL#
   ---------- ----------
      733       6738

Ensuite on cloture les connexions :

   alter system kill session 'sid, serial#';

Dans le cas ci dessus:

   alter system kill session '733, 6738';

Script

Un script simple permet de faciliter cette déconnexion "forcée".

Fichier "forcelogoff.sh"

Usage: ./forcelogoff.sh schema database

   #!/bin/bash

   if [ $# -ne 2 ]
   then
       echo "Usage: $0 schema db"
   fi

   echo "-------------------------------------------------------------"
   ./list_logged.sh $1 $2
   echo "-------------------------------------------------------------"

   echo "DO YOU WANT DO FORCE LOGOFF Y/N [N]?"
   read n

   export n="${n}n"

   if [[ ( $n != 'yn' ) && ( $n != 'Yn' ) ]]
   then
       echo "Aborted"
       exit 0
   fi

   echo "Generating kill sql script..."

   export TMPFILE=`date +%Y%m%d%H%M%S.tmp`

   export ORACLE_SID=$2

   sqlplus -s /nolog << EOF > $TMPFILE
    connect / as sysdba;
    SET pagesize 0;

    SET FEEDBACK OFF
    SET HEADING OFF
    SET VERIFY OFF
    SET ECHO OFF
    SET TIMING OFF

    SET pause off
        SET LINESIZE 600
    SET underline off
    SET trimspool off

    SET sqlblanklines ON

    select 'alter system kill session '''||sid||', '||serial#||''';' from v\$session where upper(username) =upper('$1');
   EOF
   echo "commit;" >> $TMPFILE
   echo "-------------------------------------------------------------"
   cat $TMPFILE
   echo "-------------------------------------------------------------"

   echo "DO YOU WANT TO EXECUTE THIS SCRIPT Y/N [N]?"
   read n

   export n="${n}n"

   if [[ ( $n != 'yn' ) && ( $n != 'Yn' ) ]]
   then
       echo "Aborted"
       rm -f $TMPFILE
       exit 0
   fi

   sqlplus -s /nolog << EOF
    connect / as sysdba;

    @$TMPFILE
   EOF

   rm -f $TMPFILE

Ce script commence par faire la liste des connexion puis demande une conformation. Ensuite il construit la requête de déconnexion et demande une seconde confirmation pour l'exécuter.

C'est par ailleurs un bon exemple de sql générant du sql.