Oracle : counting the number of sessions used for each client

Retrieving the number of sessions used for each client :

select 
           username, osuser, machine, count(*) as sessions
from 
           v$session
group 
           by username, osuser, machine
order 
           by sessions desc

Oracle : deleting multiple sessions with certain conditions (like machine=...)

Generate queries for deleting all sessions with certain conditions (like machine=...) :

SELECT
            'ALTER SYSTEM DISCONNECT SESSION  '''||s.sid||','||s.serial#||''' IMMEDIATE;'

FROM 
            gv$session s
            JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE   
            s.type != 'BACKGROUND' and s.status='INACTIVE' ;




Other interesting conditions :

• s.username : the username used to connect to the DB
• s.schemaname : the name of the schema
• s.status : ACTIVE/INACTIVE
• s.osuser : the Windows/Linux/... username on the remote computer (the client)
• s.machine : the name of the remote computer (the client)

Oracle : order by, different results [problem]

When you execute a SELECT .... ORDER BY query from a sql client (like SQuirreL), you may have a result with an order that is not the same when you execute your query with another sql client or inside your Java application.

It's depending on some locale parameters (country and language) on the client side.

For a Java application, you can update your locale by adding parameters to your jvm. 
For example : -Duser.country=en -Duser.language=en

There are more solutions here (adding a NLSSORT() to your ORDER BY clause, etc...).

FYI, you can check the NLS_LANGUAGE and the NLS_SORT on the Oracle server side :

Query:
select parameter,value from NLS_DATABASE_PARAMETERS where parameter in('NLS_LANGUAGE','NLS_SORT');

Result:
NLS_LANGUAGE AMERICAN
NLS_SORT BINARY

Source : http://stackoverflow.com/questions/8818201/oracle-order-by-different

SQL : Trier dans l'ordre des valeurs présentes dans la clause IN()

Si vous avez une requête SQL avec une clause IN(<valeurs>), et que vous souhaitez récupérer les rows dans un ordre bien précis (par exemple dans l'ordre des valeurs présentes dans le IN()), il existe une solution :


Sous MySQL:

SELECT id FROM table1 WHERE id IN(1, 20, 45) ORDER BY FIELD(id, 1, 20, 45)

Sous Oracle:

SELECT id FROM table1 WHERE id IN(1, 20, 45) ORDER BY INSTR('1, 20, 45', id);

Source : http://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause



N'hésitez pas à me laisser un petit commentaire :)

Utiliser SQL pour connaitre l'IP et le hostname d'un serveur Oracle

Une fois connecté avec un client SQL sur un serveur Oracle,

- pour récupérer le hostname du serveur Oracle:

SELECT UTL_INADDR.get_host_name FROM dual;

- pour récupérer l'IP du serveur Oracle:
SELECT UTL_INADDR.get_host_address from dual;

Lister les contraintes sous Oracle

Lister les contraintes de l'utilisateur en cours :

SELECT count(*)  FROM USER_CONSTRAINTS

Lister les contraintes d'un autre utilisateur :

SELECT count(*)  FROM ALL_CONSTRAINTS WHERE OWNER='<THE_USER>';

Connaitre la version d'Oracle via SQL

select * from v$version;

select * from product_component_version;

Lister les séquences sous Oracle

select * from all_sequences;

Arrêter une requête sous Oracle

ALTER SYSTEM KILL SESSION 'sid,serial#';

Pour savoir à quoi correspondent le sid et le serial# : 

http://nabilbenyoussef.blogspot.com/2011/02/afficher-les-requetes-en-cours.html

Source :  

http://www.oracle-base.com/articles/misc/KillingOracleSessions.php 

Trouver les tables Oracle contenant une colonne s'appelant 'XXX'

select table_name from user_tab_columns where column_name='NOM_DE_LA_COLONNE'

Les propriétés des mots de passe Oracle

Pour connaitre les propriétés des mots de passe Oracle  (comme par exemple la durée de validité d'un mot de passe, le nombre de tentatives d'identifications autorisées) :

select profile,resource_name ,limit from dba_profiles

Les propriétés des mots de passe sont les suivantes :
FAILED_LOGIN_ATTEMPTS
PASSWORD_LIFE_TIME
PASSWORD_REUSE_TIME
PASSWORD_REUSE_MAX
PASSWORD_VERIFY_FUNCTION
PASSWORD_LOCK_TIME
PASSWORD_GRACE_TIME

Elles sont décrites ici :

http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#CHDEGBEG

Flashback Oracle

Un chef de projet est venu me voir un soir (juste avant de rentrer chez lui) en me disant "tiens, il faudrait que tu lances cet UPDATE sur la base de prod pour mettre à jour les emails de la table CONTACT.

Il resta derrière moi, attendant que je le lance en production (et histoire de me mettre la pression). Et moi, j'ai eu la maladresse de ne pas verifier sa requête et je l'ai lancée en production... 

Ce qui devait arriver arriva : la requête n'était pas bonne du tout, et au lieu de mettre à jour les emails ils furent tous supprimés :o(

"Bon je rentre chez moi" me dit le chef de projet (on sent l'esprit d'équipe).

Deux solutions se presentaient alors à moi : 

- reprendre une base de backup pour recuperer les emails (et pour ca je devais galérer pour contacter l'hébergeur, infogérance oblige...) 

- ou utiliser la fonctionnalité magique d'Oracle, le FLASHBACK !

Grossomodo, cette deuxième solution consiste à afficher la table CONTACT telle qu'elle etait à T-X minutes.

Donc:
- je commence par créer une vue permettant d'accéder à la table telle qu'elle était il y a 1 heure : 

create view hour_ago as (select * from contact as of  TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE));
=>Du coup, lorsque je fais select * from hour_ago where id=246 par exemple, j'affiche les données du contact (ayant pour id 246) telles qu'elles étaient il y a une heure

- je fais l'update de la dernière chance :
update contact set contact.email=(select hour_ago.email from hour_ago where hour_ago.id=contact.id);
=> cet update :
    - a donc pris les emails de la table CONTACT tels qu'ils etaient avant la gaffe (grace à la vue hour_ago, avec son intervalle à "60 minutes")
    - a mis à jour la table CONTACT à partir de ces emails 

C'est maaaaaagique :o)

Afficher les requêtes en cours d'execution sous Oracle

select sesion.sid,
       sesion.username,
       optimizer_mode,
       hash_value,
       address,
       cpu_time,
       elapsed_time,
       sql_text,       serial#
  from v$sqlarea sqlarea, v$session sesion
 where sesion.sql_hash_value = sqlarea.hash_value
   and sesion.sql_address    = sqlarea.address
   and sesion.username is not null 

Si jamais il y a des requêtes qui font plus de 1000 caractères, elles seront tronqués. Dans ce cas, pour afficher l'integralité :

select sesion.sid,
       sql_text
  from v$sqltext sqltext, v$session sesion
 where sesion.sql_hash_value = sqltext.hash_value
   and sesion.sql_address    = sqltext.address
   and sesion.username is not null
 order by sqltext.piece 

Source: http://www.squaredba.com/finding-currently-running-sql-135.html 

Afficher les locks sous Oracle

SELECT 

                o.owner, o.object_name, o.object_type, 

                o.last_ddl_time, o.status, l.session_id, 

                l.oracle_username, l.locked_mode
FROM 

                dba_objects o, gv$locked_object l
WHERE 

                o.object_id = l.object_id;

Lister les tablespaces sur Oracle

SELECT tablespace_name FROM dba_tablespaces;