Oracle : Activer la trace du Listener

Parfois pour identifier un problème de connexion à la base de données on à besoin d'activer la trace de listener, pour cela on ajoute les lignes suivantes au fichier listtener.ora :

TRACE_DIRECTORY_LISTENER = C:\oracle\product\10.2.0\db_1\NETWORK\trace
TRACE_FILE_LISTENER = listener.trc
TRACE_LEVEL_LISTENER = support

Les deux premières lignes indique l'emplacement et e nom de fichier de trace, le troisième paramètre TRACE_LEVEL_LISTENER spécifie le niveau de trace, il y a quatre niveau de trace :
0 ou OFF est le niveau par défaut pas de trace.
4 ou USER est un niveau pour détecter les erreurs utilisateur.
6 ou ADMIN pour détecter des problèmes d'installation ou de configuration d'Oracle Net Services.
16 ou SUPPORT c'est le niveau le plus exhaustif il est destiné au support ORACLE.

On recharge le fichier avec :
c:\>lsnrctl reload

La sortie de fichier trace semble à quelque chose comme :
[31-JANV.-2010 17:56:04:500] nsopen: opening transport...
[31-JANV.-2010 17:56:04:500] nttcnp: getting sockname
[31-JANV.-2010 17:56:04:500] nttcnp: getting peername
[31-JANV.-2010 17:56:04:500] nttcnr: waiting to accept a connection.
[31-JANV.-2010 17:56:04:500] nttcnr: getting sockname
[31-JANV.-2010 17:56:04:500] snlinGetNameInfo: Using numeric form of host's address 127.0.0.1
[31-JANV.-2010 17:56:04:500] nttcnr: connected on ipaddr 127.0.0.1
[31-JANV.-2010 17:56:04:500] nttcon: set TCP_NODELAY on 228
[31-JANV.-2010 17:56:04:500] nsopen: transport is open
[31-JANV.-2010 17:56:04:500] nsnainit: inf->nsinfflg[0]: 0xd inf->nsinfflg[1]: 0xd
[31-JANV.-2010 17:56:04:500] nsopen: global context check-in (to slot 5) complete
[31-JANV.-2010 17:56:04:500] nsanswer: deferring connect attempt; at stage 5
[31-JANV.-2010 17:56:04:500] ntt2err: soc 228 error - operation=5, ntresnt[0]=524, ntresnt[1]=997, ntresnt[2]=0
[31-JANV.-2010 17:56:04:500] snttcallback: op = 5, bytes = 87, err = 0
......

la méthode de modification du fichier listener.ora n'est qu'une méthode parmi d'autres, on peut faire la même chose avec les commandes SET TRC_DIRECTORY, SET TRC_FILE, and SET TRC_LEVEL de l'utilitaire lsnrctl, ou à travers l'OEM.

SQL : Group BY ROLLUP

Une fonction bien pratique dans ORACLE depuis la version 9i est la fonction ROLLUP, cette fonction permet d'avoir des agrégations a certain niveaux que group by ne permet pas; analysons l'instruction suivante :
SQL> select deptno,job,sum(sal)
2 from emp group by deptno,job
3 order by deptno;

DEPTNO	JOB	SUM(SAL)
----------	---------	----------
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	5600

9 ligne(s) sélectionnée(s).
L'agrégation avec GROUP BY dans l'instruction précédente n'est utile que si on veut la somme en fonction des deux colonnes DEPTNO et JOB, si on veut aussi la somme en fonction de chaque DEPTNO nous auront deux choses soit faire une interrogation a part et la joindre avec un l'operateur UNION, soit utilisé la fonction ROLLUP :

SQL> select deptno,job,sum(sal)
2 from emp group by rollup(deptno,job)
3 order by deptno;

DEPTNO	JOB	SUM(SAL)
----------	---------	----------
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10		8750
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
20		10875
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	5600
30		9400
		29025

13 ligne(s) sélectionnée(s).

Remarquer aussi que la fonction ROLLUP permet de donner un total global de tous les départements, l'utilisation de ROLLUP est équivalent a group by() union group by deptno union group by deptno,job :

SQL> select deptno,job,sum(sal)
2 from emp group by deptno,job
3 union
4 select deptno,to_char(null),sum(sal)
5 from emp group by deptno
6 union
7 select to_number(null),to_char(null),sum(sal)
8 from emp;

DEPTNO	JOB	SUM(SAL)
----------	---------	----------
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10		8750
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
20		10875
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	5600
30		9400
		29025

13 ligne(s) sélectionnée(s).

Une autre fonction utile est la fonction CUBE.

DATA PUMP : Les paramétres REMAP_* dans

Une des fonctionnalités intéressantes de SQL loader c'est qu'il permet de charger des données à partir d'un schéma utilisateur vers un autre schéma utilisateur grâce aux paramètres fromuser et touser, mais avec DATA PUMP dans oracle 11g, c'est encore plus puissant avec les paramètres du genre REMAP_*.

Un équivalent de fromuser touser est le paramètre REMAP_SCHEMA, d'abord un export du schéma HR :

C:\>impdp system/sbd11 DIRECTORY=dpump_dir DUMPFILE=hr.dmp

Puis un import avec le paramètre REMAP_SCHEMA=hr:oe pour charger les objets du schéma HR vers le schéma Scott:

C:\>impdp system/sbd11 DIRECTORY=dpump_dir DUMPFILE=hr.dmp REMAP_SCHEMA=hr:scott

Autre fonctionnalité avec les paramètres de la famille REMAP est REMAP_DATA, et oui un mappage aux niveaux donnés; cela s'avère utile lors d'un chargement des données du prod vers une plateforme de test, voila un exemple :

C:\>expdp hr/hr tables=departments directory=dpump_DIR dumpfile=hrdept.dmp logfile=expdept.log
remap_data:departments.location:remap_pkg.map_loc
remap_data:departments.department_id:remap_pkg.map_id

L'exemple précédent montre un changement les valeurs de colonnes location et departement_id par des valeurs arbitraires (mais de même type!) retournées par des fonctions utilisateur.
Il y a aussi REMAP_TABLE, REMAP_DATAFILE ET REMAP_TABLESPACE

Oracle : Limter l'accés à la base avec le listener

Lorsque on veut limiter les machine à partir de laquelle on veut se connecter, généralement on deux choix soit en le fait avec un déclencheur sur l'événement DATABASE LOGON, soit en le fait au niveau listener est c'est plus simple à implémenter.
On ajoute les deux lignes suivantes dans le fichier sqlnet.ora :

tcp.validnode_checking=YES
tcp.invited_nodes= (hosta1,10.130.1.54,cronix,servpl)

La première ligne active la vérification de la source de connexion, la deuxième spécifie les machines à partir de quelles la connexion est autorisée, vous devez recharger le listener pour que ca prend effet :

C:\>lsnrctl reload

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 31-JANV.-2010 16:43:13

Copyright (c) 1991, 2005, Oracle. All rights reserved.

Connexion à (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
La commande a réussi

Si vous essayez la connexion à partir d'une autre machine vous aurez le message suivant :
ERROR:
ORA-12537: TNS:connection closed

SQL : Foncion Cube

La fonction CUBE est comme sa petite sœur ROLLUP est une fonction d'agrégation qui permet d'avoir tout les combinaisons entre les composants d'une agrégation, un exemple :
SQL> select deptno,job,sum(sal)
2 from emp group by cube(deptno,job)
3 order by deptno;

DEPTNO	JOB	SUM(SAL)
----------	---------	----------
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10		8750
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
20		10875
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	5600
30		9400
	ANALYST	6000
	CLERK	4150
	MANAGER	8275
	PRESIDENT	5000
	SALESMAN	5600
		29025

18 ligne(s) sélectionnée(s).

Le résultat montre quatre agrégations par les deux colonnes (deptno,job), par deptno seulement, par job seulement et le total dans toute la table ce que revient à faire mais avec quatre passages de la table emp alors que la précédente le fait dans un seule passage :
SQL> select deptno,job,sum(sal) from emp group by deptno,job
2 union
3 select to_number(null),job,sum(sal) from emp group by job
4 union
5 select deptno,to_char(null),sum (sal) from emp group by deptno
6 union
7 select to_number(null),to_char(null),sum(sal) from emp;

DEPTNO	JOB	SUM(SAL)
----------	---------	----------
10	CLERK	1300
10	MANAGER	2450
10	PRESIDENT	5000
10		8750
20	ANALYST	6000
20	CLERK	1900
20	MANAGER	2975
20		10875
30	CLERK	950
30	MANAGER	2850
30	SALESMAN	5600
30		9400
	ANALYST	6000
	CLERK	4150
	MANAGER	8275
	PRESIDENT	5000
	SALESMAN	5600
		29025

18 ligne(s) sélectionnée(s).

Le nombre d'agrégation de CUBE est 2^nombre de colonnes dans le select, par contre avec ROLLUP on seulement n+1 ou n est le nombre de colonnes dans le select.

SQL : Foncion Rank()

la fonction Rank() est une fonction ananlytique pemettant d'assigner un numero d'ordre à un resultat un exemple simple :
SQL> conn scott/tiger
Connecté.
SQL> select empno,ename,sal from emp order by sal;

EMPNO	ENAME	SAL
7369	SMITH	800
7900	JAMES	950
7876	ADAMS	1100
7521	WARD	1250
7654	MARTIN	1250
7934	MILLER	1300
7844	TURNER	1500
7499	ALLEN	1600
7782	CLARK	2450
7698	BLAKE	2850
7566	JONES	2975
7788	SCOTT	3000
7902	FORD	3000
7839	KING	5000

14 ligne(s) sélectionnée(s).
maintenant je veux assigné un numero d'ordre pour chaque employé en se basant sur son salaire :
SQL> select empno,ename,sal,rank() over(order by sal) as ord from emp;

EMPNO	ENAME	SAL	ORD
----------	----------	----------	----------
7369	SMITH	800	1
7900	JAMES	950	2
7876	ADAMS	1100	3
7521	WARD	1250	4
7654	MARTIN	1250	4
7934	MILLER	1300	6
7844	TURNER	1500	7
7499	ALLEN	1600	8
7782	CLARK	2450	9
7698	BLAKE	2850	10
7566	JONES	2975	11
7788	SCOTT	3000	12
7902	FORD	3000	12
7839	KING	5000	14

14 ligne(s) sélectionnée(s).

on le meme numero d'ordre pour les employés ayant le meme salaire.

voire le post fonctions analytiques