Technical Notes

Fonctions Analytiques ROW_NUMBER et DENSE_RANK

2011-12-07T07:06:00.000-08:00

Les fonctions analytiques sont très puissantes et très utiles, elles permettent d’alléger les requêtes SQL d’une façon élégante et performante.

Une fonction que j’ai utilisée récemment est la fonction ROW_NUMBER, elle permette de donner un ordre numérique pour les lignes, la pseudo-colonne ROWNUM existe mais son utilisation nécessite l’écriture de plus d’instruction.

Voila un exemple qui illustre l’utilité de cette fonction ; supposons que vous voulez faire un ordre sur la colonne salaire de la table EMP du schéma SCOTT :

SQL> select EMPNO,ENAME,SAL
  2  from scotty.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
     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7902 FORD             3000
      7839 KING             5000
14 ligne(s) selectionnee(s).

Ajoutons une colonne pour le classement de ces lignes selon le salaire, si on utilise directement ROWNUM ça marcherai pas :

SQL> select EMPNO,ENAME,SAL, rownum classement from scotty.emp order by SAL;

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7369 SMITH             800          1
      7900 JAMES             950         12
      7876 ADAMS            1100         11
      7521 WARD             1250          3
      7654 MARTIN           1250          5
      7934 MILLER           1300         14
      7844 TURNER           1500         10
      7499 ALLEN            1600          2
      7782 CLARK            2450          7
      7698 BLAKE            2850          6
      7566 JONES            2975          4

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7788 SCOTT            3000          8
      7902 FORD             3000         13
      7839 KING             5000          9

14 ligne(s) selectionnee(s).

Certes il a classé les donner mais c’est donne l’ordre de l’extraction des lignes mais pas dans l’ordre des valeurs des salaires, pour corriger ça il faut écrire :

SQL> select EMPNO,ENAME,SAL,rownum classement from 
 2 (select EMPNO,ENAME,SAL from scotty.emp order by SAL) ;

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7369 SMITH             800          1
      7900 JAMES             950          2
      7876 ADAMS            1100          3
      7521 WARD             1250          4
      7654 MARTIN           1250          5
      7934 MILLER           1300          6
      7844 TURNER           1500          7
      7499 ALLEN            1600          8
      7782 CLARK            2450          9
      7698 BLAKE            2850         10
      7566 JONES            2975         11

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7788 SCOTT            3000         12
      7902 FORD             3000         13
      7839 KING             5000         14

14 ligne(s) selectionnee(s).

C’est la que devienne l’utilisation des fonctions analytiques intéressante, voila le même résultat en utilisant ROW_NUMBER :

SQL> select EMPNO,ENAME,SAL, row_number() over(order by SAL) classement
  2  from scotty.emp
  3  order by SAL;

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7369 SMITH             800          1
      7900 JAMES             950          2
      7876 ADAMS            1100          3
      7521 WARD             1250          4
      7654 MARTIN           1250          5
      7934 MILLER           1300          6
      7844 TURNER           1500          7
      7499 ALLEN            1600          8
      7782 CLARK            2450          9
      7698 BLAKE            2850         10
      7566 JONES            2975         11

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7788 SCOTT            3000         12
      7902 FORD             3000         13
      7839 KING             5000         14

14 ligne(s) selectionnee(s).

Le résultat est bien trier selon les valeurs des salaires et il est classé mais il y a un problème sur les salaires égaux (par exemple SCOTT et FORD) ont le même salaire mais un classement différent, la aussi c’est les fonctions analytiques qui viennent au secours :

SQL>  select EMPNO,ENAME,SAL, DENSE_RANK() over(order by SAL) classement
  2  from scotty.emp
  3  order by SAL;

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7369 SMITH             800          1
      7900 JAMES             950          2
      7876 ADAMS            1100          3
      7521 WARD             1250          4
      7654 MARTIN           1250          4
      7934 MILLER           1300          5
      7844 TURNER           1500          6
      7499 ALLEN            1600          7
      7782 CLARK            2450          8
      7698 BLAKE            2850          9
      7566 JONES            2975         10

     EMPNO ENAME             SAL CLASSEMENT
---------- ---------- ---------- ----------
      7788 SCOTT            3000         11
      7902 FORD             3000         11
      7839 KING             5000         12

14 ligne(s) selectionnee(s).

La fonction DENSE_RANK permet de donner le même classement pour les mêmes valeurs dans un résultat trié.

Chaque fois que vous écrivez des instructions SQL ou des blocs PL/SQL afin d’analyser les données, jeter un coup d’œil sur les fonctions analytiques vous pouvez y trouver un contournement.

WebSphere : JDWP error in VM_INIT: TransportException

2011-11-30T05:50:00.000-08:00

J’ai reçu l’erreur suivant en essayant de redémarrer un serveur applicatif en Websphere .

L’erreur s’affiche dans SystemErr.log, dans SystemOut.log rien d’indicatif :

08:43:21.820 ERROR: [Agent.cpp:164] JDWP error in VM_INIT: TransportException [510/201] binding to port failed (error code: -212

En cherchant un peu cela vient de l’échec de Websphere d’obtenir le port renseigné dans server.xml, en particulier celui de debug JVM - c’est ce qui est indiqué par le code message JDWP – j’ai fait un grep sur la valeur debugArgs :

grep -i debugArgs ./config/cells/aix61devNode01Cell/nodes/aix61devNode01/servers/server1/server.xml
  < services xmi:type="debugservice:DebugService" xmi:id="DebugService_1319801562654" enable="true" jvmDebugPort="7777" jvmDebugArgs="-agentlib:jdwp=transport=dt_socket,server=y,suspend=n,address=7777" BSFDebugPort="4444" BSFLoggingLevel="0"/ >
    < jvmEntries xmi:id="JavaVirtualMachine_1183122130078" verboseModeClass="false" verboseModeGarbageCollection="false" verboseModeJNI="false" runHProf="false" hprofArguments="" debugMode="true" debugArgs="-agentlib:jdwp=transport=dt_socket,server=y,suspend=n,address=7777" genericJvmArguments="" >

Apparemment il cherche à allouer le port 7777 c’est le port par défaut pour le debugging JVM en Websphere , je fait un recherche sur le port voulu et effectivement il est déjà allouer par un autre serveur :

lsof -Pnl +M -i4 | grep 7777

je modifie le fichier server.xml pour lui indiqué un autre port libre et voila que cela redémarre :

    < jvmEntries xmi:id="JavaVirtualMachine_1183122130078" verboseModeClass="false" verboseModeGarbageCollection="false" verboseModeJNI="false" runHProf="false" hprofArguments="" debugMode="true" debugArgs="-agentlib:jdwp=transport=dt_socket,server=y,suspend=n,address=7788" genericJvmArguments="" >
      < systemProperties xmi:id="Property_1" name="com.ibm.security.jgss.debug" value="off" required="false"/ >

La modification peut aussi se faire au niveau console :

Un peu plus bas :

Souvenez-vous qu’en activant certains options sur WebSphere cela peut activer l’utilisation de certains ports qui ne sont pas forcement libres ou autorisé par le par-feu.

Le parametre DDL_LOCK_TIMEOUT

2011-11-24T08:26:00.000-08:00

A partir de la version 11g d’oracle un nouveau paramètre permettant de contrôler le timeout sur l’instruction DDL qui sont en attente des verrous DML ; ce parametre est par défaut à zero qui est le comportement existant dans les version 10g et avant, il s’agit de ddl_lock_timeout voila un cas expliquant sont comportement :

L’état de ce parametre avant modification :

SQL> show parameter ddl
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout                     integer     0
enable_ddl_logging                   boolean     FALSE

SQL>-- créons maintenant un table de test  et simulons une modification :

SQL> create table usr(name varchar2(10),tr number(4));
Table creee.
SQL> insert into usr values('polo',20);
1 ligne creee.
SQL> insert into usr values('folo',30);
1 ligne creee.
SQL> insert into usr values('solo',40);
1 ligne creee.
SQL> commit;
Validation effectuee.
SQL> --
SQL> update usr set name='lolo' where name='folo';
1 ligne mise a jour.

On ouvre une autre session et on simule une suppression de la table, la première tentative renvoi le message d’erreur sur le champ :

[oracle@sysagvm ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 7 04:58:18 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connecte a :
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL>
SQL>
SQL> set tim on
04:58:31 SQL> drop table usr;
drop table usr
           *
ERREUR a la ligne 1 :
ORA-00054: ressource occupee et acquisition avec NOWAIT ou temporisation
indique

On modifie le paramètre au niveau session à 10 secondes, et on réessaye cette fois en attend la période spécifier dans le paramètre avant qu’Oracle nous renvoi le message d’erreur :

04:58:32 SQL> alter session set ddl_lock_timeout=10;

Session modifiee.

04:58:40 SQL>
04:58:41 SQL> drop table usr;
drop table usr
           *
ERREUR a la ligne 1 :
ORA-00054: ressource occupee et acquisition avec NOWAIT ou temporisation
indique

04:58:53 SQL>

La valeur maximale de ce paramètre est 1000000 secondes ce qui signifie une attente infinie.

Did not find JAVA_HOME under the WebSphere installer directory

2011-11-23T08:07:00.000-08:00

Lors de l’installation du Websphere j’ai rencontré l’erreur suivante :

root@AIX53:/tmp/inst_ihs/IHS>./install

Did not find JAVA_HOME under the WebSphere installer directory.
Exiting

Cela vient du fait que l’installateur de Websphere ne trouve pas les binaires JAVA. Pour contourner ce problème il suffit juste de mettre un SDK compatible dans le répertoire parent du répertoire de l’installation dans mon cas : /tmp/inst_ihs/.

IHS : Installation IBM HTTP Server 7.0 sous AIX 5.3

2011-11-23T07:04:00.000-08:00

L’installation est effectué sur un system AIX 5.3 64bits , pré requis pour cette installation sont les suivants :

Ø Service Pack 5300-07-01 (recommandé mais pas nécessaire).

Ø 2Go de libre dans /tmp ou nous allons extraire les archives d’installation.

Ø 700 Mo de libre dans le FS de l’installation.

Ø Une version SDK compatible dans niveau supérieur du répertoire d’installation.

Ø Les archives de l’Install.

On decompresses les archives :

root@AIX53:/tmp/inst_ihs> ls -l
total 836448
-rw-r--r--    1 root     system    173557760 Nov 22 09:20 JDk.tar
-rw-r--r--    1 root     system    181483520 Nov 22 09:52 ihs.tar
root@AIX53:/tmp/inst_ihs> tar –xvf  JDk.tar
root@AIX53:/tmp/inst_ihs> tar –xvf  ihs.tar
root@AIX53:/tmp/inst_ihs> ls -l
total 836448
dr-xr-xr-x   12 root     system         4096 Sep  1 2008  IHS
drwxr-xr-x    3 root     system          256 Sep  1 2008  JDK
-rw-r--r--    1 root     system    173557760 Nov 22 09:20 JDk.tar
-rw-r--r--    1 root     system    181483520 Nov 22 09:52 ihs.tar

Lancer un serveur X depuis le poste ou vous allez effectuer l’installation (Xming, Xmanager), et exporter l’affichage vers la machine en question :

root@AIX53:/tmp/inst_ihs> export DISPLAY=10.100.136.249 :0.0

Et en lance l’installation depuis :

root@AIX53:/tmp/inst_ihs/IHS> ./install

Voila les différentes étapes de l’installation :

L’avertissement concerne le patch recommandé :

Casser les mots de passe Oracle

2011-11-21T03:16:00.000-08:00

Il existe plusieurs utilitaires pour casser les mots de passe Oracle ; un des plus rapides est woraauthbf téléchargeable sur le site Soonerorlater .

J’ai fait un petit test et ça fonctionne rapidement tant que le mot de passe ne depasse pas les six caractères, voila l’exemple que j’ai fait sur un poste avec un processeur Core2 Duo 3Ghz cela à donner une vitesse de 2,5 million de mot de passe par seconde :

SQL> create user user1 identified by PLM0RT;

Utilisateur cree.

SQL> select u.name||':'||u.password||':'||substr(u.spare4,3,63)||':'||d.name||':'||
      sys_context('USERENV','SERVER_HOST')||':'
      from sys.user$ u, sys.V_$DATABASE d where u.type#=1;
...
USER1:B033B534947A8CCF:F2A24D8B1AA518827F9721561BD9970492684425AACD4465B86C1AA646B3:DB11GR2:sysagvm:
...

On lance la session en specifiant le caracteset "alphanum" et le type de l'algoritme - ça depend selon la version de la base - 11g10g; pour les differents arguments et option consulter le fichier README.

C:\woraauthbf_0.22R2\woraauthbf_0.22R2\woraauthbf.exe -p C:\woraauthbf_0.22R2\woraauthbf_0.22R2\pfile.txt -c "alphanum" -t "11g10g"
Usernames will be permuted!
The number of processors: 2
Number of pwds to check: 2238976116
Number of pwds to check by thread: 1119488058
Password file: C:\woraauthbf_0.22R2\woraauthbf_0.22R2\pfile.txt, charset: alphanum, maximum length: 6, type: 11g10g
Could not open default.txt file! Deafult passwords will not be checked!
Start array thread with 1 number of passwords!
Start: 0 End: 1119488058
Start: 1119488058 End: 2238976116
Elpased time: 37s Checked passwords: 93950368 Speed: 2539199/s
Writing session files...
Writing session files...
Elpased time: 127s Checked passwords: 318483485 Speed: 2507743/s
Writing session files...
Elpased time: 235s Checked passwords: 588187535 Speed: 2502925/s
Writing session files...
Writing session files...
Elpased time: 333s Checked passwords: 832099138 Speed: 2498796/s
Writing session files...
Writing session files...
Writing session files...
Elpased time: 507s Checked passwords: 1262426203 Speed: 2489992/s
Writing session files...
Writing session files...
Elpased time: 657s Checked passwords: 1634275841 Speed: 2487482/s
Writing session files...
Writing session files...
Elpased time: 757s Checked passwords: 1885363683 Speed: 2490572/s
Writing session files...
Password found: USER1:PLM0RT:DB11GR2:sysagvm
Elpased time: 794s
Checked passwords: 1978225547
Password / Second: 2491467

Presque deux milliards de mots de passe essayer en 794 seconde ; si vous avez une machine avec plus de puissance CPU cela va minimiser le temps. Une version pour linux existe aussi.

ORA-01555 lors d'un Export - Corruption des données LOB

2011-11-14T07:53:00.001-08:00

Aujourd’hui j’ai rencontré une erreur un peu particulière lors d’un export d’une base 8.1.7 , voila un extrait :

. . exporting table PLFRQRS

EXP-00056: ORACLE error 1555 encountered

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-22924: snapshot too old

A première vue j’ai soupçonné les RBS de la base j’ai ajouté un grand Rollback Segment (2 Go) et j’ai réessayé l’export en surveillant l’état d’utilisation des segments d’annulation, mais bizarrement rien ne se passe après un certain temps la taille utilisée sur les segments reste la même. Et l’erreur est la même.

En vérifiant l’erreur je remarque que le message d’erreur ne comporte aucun nom du segment d’annulation !! . charchant un peu sur Metalink je trouve la note suivante:

Export Fails With ORA-2354 ORA-1555 ORA-22924 and How To Confirm LOB Segment Corruption Using Export Utility? [ID 833635.1]

Et effectivement il y a bien une corruption dans la table, en lançant les scripts suivants :

declare
error_1555 exception;
pragma exception_init(error_1555,-1555);
num number;
begin
for cursor_lob in (select rowid r, &&lob_column from &table_owner.&table_with_lob) loop
begin
num := dbms_lob.instr (cursor_lob.&&lob_column, hextoraw ('889911')) ;
exception
when error_1555 then
insert into corrupted_lob_data values (cursor_lob.r);
commit;
end;
end loop;
end;
/

Enter value for lob_column : PLFCLOB
Enter value for table_owner : PLF
Enter value for table_with_LOB: PLFRQRS

SQL> select * from corrupted_lob_data;

CORRUPTED_ROWID
--------------------------------
AAABBvAAIAAA39VAAE

J’ai pu faire l’export en supprimant la ligne, une mise à jour de la colonne aurait suffit mais dans mon cas j’ai déjà cette ligne sur une autre table.

SEC_SENSITIVE_LOGON dans Oracle 11g

2010-02-05T02:23:00.000-08:00

Dans Oracle 11g les mots de passe utilisateurs sont par défaut sensibles à la casse sauf s'il s'agit d'une mise à niveau et cela tant qu'une instruction ALTER USER n'est pas utilisé ce qui restaure le comportement èar défaut, mais les noms utiliateurs ne sont pas concerné.
on peut desactiver la sensibilte à la casse on utilisant le nouveau parametere SEC_CASE_SENSITIVE_LOGON , par exemple :

SQL>alter system set SEC_SENSITIVE_LOGON = FALSE;

mais Oracle recommande de garder le comportement par défaut puisque la desactivation augmente les risques des attaques de type brute force contre les mots de passe non sensibles à la casse.

Oracle : Activer la trace du Listener

2010-02-01T08:20:00.000-08:00

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

2010-02-01T08:12:00.000-08:00

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

2010-02-01T08:05:00.000-08:00

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

2010-02-01T08:01:00.000-08:00

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

2010-02-01T07:57:00.000-08:00

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()

2010-02-01T07:40:00.000-08:00

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

Vmstat, Top, free...

2010-01-25T01:55:00.000-08:00

Pour superviser son system sous linux pas besoin d'utiliser des logiciels tierces une panoplie de commandes est la pour vous servir; une des commandes utilitaires dans est vmstat, celle-ci permet de donner des statistiques sur les process, les IO, l'utilisation de la mémoire, l'utilisation du processeur utiliser sans arguments ca donne la sortie suivantes :oracle@HOST1:~> vmstat

La sortie est divisée en six colonnes chacune contienne des informations spécifiques.
Process :
b pour l'attente de libération de CPU, cette colonne ne doit pas prendre constamment une valeur supérieure à 0.

swap :
si et so pour swap in et swap out ces deux valeurs indique l'échange de blocks SWAP entre la mémoire et les disques ces deux valeurs doivent rester à 0 le plus longtemps possible.

CPU :
CS pour contexte switch indique le nombre de changements de contexte pour le CPU.
id pour idle time le temps d'inactivité du processeur.
wa temps d'attente pour les IO, il ne doit pas être très élevé.

system :
in nombre d'interruption system par seconde.

io :
bi et bo nombre de blocks écrits et lus du disque par seconde.

memory :
Donne des statistiques globales sur la mémoire, taille de mémoire libre, cache, swap.

Une autre commande utile est la commande TOP qui donne une vue en temps réelle sur l'utilisation mémoire, cpu, priorité... pour chaque process et d'autre information utile, la sortie est par défaut trié selon la consommation des ressources la plus couteuse :
oracle@HOST1:~> top

D’autres commandes plus ou moins utiles :
mpstat, iostat, free, sar, ps, netstat...pour plus de détails consulter l'aide en ligne :

oracle@HOST1:~> man free

PL/SQL : La clause AUTHID CURRENT_USER

2010-01-25T01:51:00.000-08:00

Quand on appelle une procédure, une fonction ou un package il y a deux modes de privilèges d'exécution :
Soit avec les droits de définition; le code utilisé permet d'accéder aux objets qu'il appelle avec les droits utilisateur qui a écrit ce code.
Soit avec les droits d'invocation; le code est alors appelé avec les droits de l'utilisateur qui appelé le code stocké.
Explication :
L’utilisateur Scott se connecte et fait une select sur la table employees qui est dans le schéma hr :
SQL> select first_name from HR.employees where employee_id=190;
select first_name from HR.employees where employee_id=190
*
ERREUR Ó la ligne 1 :
ORA-00942: Table ou vue inexistante
Il obtient une erreur; alors l'utilisateur HR crée une procédure qui fait la même chose et donne le droit d'exécution a Scott :
SQL> create or replace procedure normale_proc is
2 nom varchar2(20);
3 begin
4 select first_name into nom from employees where employee_id=190;
5 dbms_output.put_line('le nom est :'||nom);
6 end;
7 /
Procédure créée.
SQL> grant execute on normale_proc to scott;
Autorisation de privilèges (GRANT) accepte.
SQL>
Maintenant pour Scott ça donne le résultat suivant :
SQL> set serveroutput on
SQL> exec hr.normale_proc
le nom est :Timothy

Procédure PL/SQL terminée avec succès.
SQL>
Ce genre de fonctionnement peut s'avérer très pratiques si on ne veut pas donner des privilèges sur les objets auxquels il accède notre code, mais il peut s'avérer tres dangereux si notre code contient une quelconque vulnérabilité, par exemple une injection SQL alors les droits d'exécution seront ceux du propriétaire du code.
Pour activer les droits d'invocation pour un code il suffit d'ajouter la clause AUTHID CURRENT_USER avant IS, le même exemple :
SQL> create or replace procedure aut_proc AUTHID CURRENT_USER is
2 nom varchar2(20);
3 begin
4 select first_name into nom from employees where employee_id=190;
5 dbms_output.put_line('le nom est :'||nom);
6 end;
7 /
Procédure créée.

SQL> grant execute on aut_proc to scott;
Autorisation de privilèges (GRANT) acceptée.
SQL>
Maintenant pour Scott ca donne un 942:
SQL> exec hr.aut_proc
BEGIN hr.aut_proc; END;
*
ERREUR Ó la ligne 1 :
ORA-00942: Table ou vue inexistante
ORA-06512: Ó "HR.AUT_PROC", ligne 4
ORA-06512: Ó ligne 1

pour savoir quelle mode d'exécution pour une procédure on consulte la vue user_procedures :
SQL> select OBJECT_NAME,AUTHID from user_procedures
2 where OBJECT_NAME in ('AUT_PROC','NORMALE_PROC');

OBJECT_NAME AUTHID
------------------------------ ------------
AUT_PROC CURRENT_USER
NORMALE_PROC DEFINER

PL/SQL : Le package DBMS_RANDOM

2010-01-25T01:47:00.000-08:00

Le package dbms_rnadom dans oracle permet de générer des valeurs aléatoires entres -2^31 et 2^31, pour ce faire vous devez initialiser le générateur pseudo-aléatoire (il n'y a pas de génération aléatoire à 100% dans l'informatique) avec une valeur numérique :
SQL> exec dbms_random.initialize(34);

Procédure PL/SQL terminée avec succès.

La procédure initialize ne prend que des valeurs numériques comme paramètre, une autre procédure qui fait la même chose mais qu'est obsolète est la procédure SEED, le seul avantage de SEED est l'utilisation de chaine de caractère pour initialiser le générateur :
SQL> exec dbms_random.seed('bla bla bla..');

Procédure PL/SQL terminée avec succès.

Une fois le générateur initialisé vous pouvez générer des nombres en appelant la fonction value :
SQL> select dbms_random.value(100,150) from dual;

DBMS_RANDOM.VALUE(100,150)
--------------------------
106,250729

SQL>
La fonction value prend deux paramètres qui constituent la plage dans laquelle les nombres seront générés

Oracle : injection du code PL/SQL

2010-01-25T01:45:00.000-08:00

L’attaque du code PL/SQL par une injection SQL est toujours d'actualité malgré son apparition depuis plusieurs années, cela est du généralement aux mauvaises habitudes des développeurs, en gros l'attaque consiste à forgé une autre requête a partir d'une requête qui accepte des paramètres, exemple :
SQL> create or replace procedure vul_proc(param varchar2) is
2 type fir_name is table of hr.employees.first_name%type;
3 fname fir_name := fir_name();
4 stmt varchar2(300);
5 begin
6 stmt:='select first_name from hr.employees where last_name='''||param||'''';
7 execute immediate stmt bulk collect into fname;
8 for i in fname.first..fname.last loop
9 dbms_output.put_line(fname(i));
10 end loop;
11 end;
12 /

Procédure crée.

L’utilisation légitime de cette procédure est :
SQL> set serveroutput on
SQL> exec vul_proc('King')
Janette
Steven
Procédure PL/SQL terminée avec succès.
SQL>
Mais le paramètre param est concaténé à l'instruction stmt sans aucun contrôle ce qui peut donner des idées :
SQL> exec vul_proc('King'' or ''1''=''1 ')
Ellen
Sundar
Mozhe
David
Hermann
Shelli
....

ou pire encore :
SQL> exec vul_proc('King'' union select password from dba_users where ''1''=''1 ')
2BE6F80744E08FEB
2CA614501F09FCCC
2D594E86F93B17A1
3DF26A8B17D0F29F
3FB8EF9DB538647C
4A3BA55E08595C81
4C6D73C3E8B0F0DA
66F4EF5650C20355
71E687F036AD56E5
72979A94BAD2AF80
72E382A52E89575A
7C9BA362F8314299
7EFA02EC7EA6B86F
84B8CBCA4D477FA3
88A2B2C183431F00
88D8364765FCE6AF
8A8F025737A9097A
9793B3777CD3BD1A
9C30855E7E0CB02D
BFBA5A553FD9E28A
CE4A36B8E06CA59C
DF02A496267DEE66
F25A184809D6458D
F894844C34402B67
FA1D2B85B70213F3
FFF45BB2C0C327EC
Janette
Steven
anonymous

Procédure PL/SQL terminée avec succès.
Pour éviter ce genre de situation le mieux sera d'utiliser des variables attachées dans la chaine passer à execute immediate et une clause USING pour utiliser le paramètre :
SQL> create or replace procedure vul_proc(param varchar2) is
2 type fir_name is table of hr.employees.first_name%type;
3 fname fir_name := fir_name();
4 stmt varchar2(300);
5 begin
6 stmt:='select first_name from hr.employees where last_name= :param ';
7 execute immediate stmt bulk collect into fname using param;
8 for i in fname.first..fname.last loop
9 dbms_output.put_line(fname(i));
10 end loop;
11 end;
12 /

Procédure crue.

L’attaque par une injection SQL ne marchera pas, puisque ORACLE précompile l'instruction STMT et il ne permettra qu'un seul paramètre valide qui ne contiendra aucun mot clé SQL :
SQL> exec vul_proc('King'' union select password from dba_users where ''1''=''1 ')
BEGIN vul_proc('King'' union select password from dba_users where ''1''=''1 '); END;

*
ERREUR à la ligne 1 :
ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur
ORA-06512: à "SYS.VUL_PROC", ligne 8
ORA-06512: à ligne 1

Configurer SSH entre deux machines

2010-01-18T06:01:00.000-08:00

la communication entre les machines se fait naturellement sur un réseau qui sert de canal de communication en utilisant un protocole, la sécurité de cette communication est affectée par la sécurité de ce protocole, passer les anciens protocoles comme Telnet, rlogin et rsh le plus utilisé en ce moment est ssh, certes ca ne garantie pas une sécurité totale de la communication des failles spécifiques à ce protocole existe; mais il reste le plus sécurisé, ce que je présente ici c'est la configuration de ssh entre deux machines linux, les étapes ci-dessous sont à exécuter dans les deux machines (host1 et host2).
1) en premier lieu en va vérifier si ssh est installé généralement il est les packages ssh sont installés par défaut:
$rpm -qa | grep -i ssh

2)maintenant il faut vérifier si le démon ssh (sshd) est démarré :
$service sshd status

S’il n'est pas démarré il faut le lancer avec :
$service sshd start

Configurer aussi le démarrage automatique de sshd avec :
$chkconfig --level 2345 sshd on

3) maintenant on crée un répertoire /home/root/.ssh :
$mkdir /home/root/.sshd

4) on génère les clés de cryptage RSA et DSA :
$/usr/bin/ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/root/.ssh/id_rsa):<-- accepte="" chemin="" cl="" d="" faut="" la="" le="" on="" ou="" par="" sera="" span="" stock="" style="color: #cc6600; font-family: verdana; font-style: italic;">Enter passphrase (empty for no passphrase): <-- celui="" de="" diff="" entre="" mot="" on="" passe="" rent="" root="" span="" style="color: #cc6600; font-family: verdana; font-style: italic;" un="">Enter same passphrase again:
Your identification has been saved in /home/root/.ssh/id_rsa.
Your public key has been saved in /home/root/.ssh/id_rsa.pub.
The key fingerprint is:
5c:d9:d8:c5:3d:8d:0b:10:33:42:9c:83:45:a1:d0:61 root@host1.com

$/usr/bin/ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/root/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/root/.ssh/id_dsa.
Your public key has been saved in /home/root/.ssh/id_dsa.pub.
The key fingerprint is:
5c:d9:d8:c5:3d:8d:0b:10:33:42:9c:83:45:a1:d0:61 root@host1.com

Si vous lister /home/root/ssh vous aurez quatre fichiers de deux types:
id_dsa et id_rsa : contiennent les clés privées DSA et RSA, il ne faut jamais les distribuer.
id_dsa.pub et id_rsa.pub : contiennent les clés publics DSA et RSA.

5) on copie les clés publics dans un fichier qu'on appel authorized_keys :
$cd /home/root/.ssh
$cat /home/root/.ssh/id_rsa.pub >>/home/root/.ssh/authorized_keys
$cat /home/root/.ssh/id_dsa.pub >>/home/root/.ssh/authorized_keys

7) pour l'échange des clés on copie de host1 vers host2 avec :
$scp authorized_keys host2 /home/root/.ssh/authorized_keys_host1
Enter passphrase for key '/home/root/.ssh/id_dsa':<-- cl="" de="" des="" entrer="" et="" g="" host2="" le="" mot="" n="" passe="" ration="" root="" s="" span="">

Dans host2 maintenant vers host1 :
$cat /home/root/.ssh/authorized_keys_host1 >> /home/root/.ssh/authorized_keys
$scp /home/root/.ssh/authorized_keys host1:/home/root/.ssh/authorized_keys

Maintenant le fichier /home/root/.ssh/authorized_keys est le même, jusqu'ici si vous faites à partir de host1:
$ssh host2 uname -a

Vous devez entrer le mot de passe entrer lors de la génération des clés, pour éviter d'entrer ce mot de passe chaque fois;pour cela il suffit de taper dans chaque machine :
$exec /usr/bin/ssh-agent $SHELL
$/usr/bin/ssh-add

EMCA : Creation de l'OEM repository

2010-01-18T05:18:00.000-08:00

À partir de la version 10g la base de données Oracle s'est dirigé vers le WEB pour fournir une nouvelle version d'Entreprise Manager à la place de celui de la 9i basé sur java; ainsi que des variantes selon l'utilisation Dbcontrol pour une seule Bd ou grid control pour centraliser la gestion de plusieurs cibles.
La plupart du temps l'installation d'entreprise manager se fait lors de la création de la base (si vous n'opter pas pour le gride control), mais vous pouvez toujours laisser l'installation d'entreprise manager après la création de la base.
Pour l'installer il y de méthodes soit l'outil graphique DBCA, soit un programme en ligne de commande EMCA (pour Entreprise Manager Configuration Assistant) c'est celui dont nous allons parler.
Sans parler de tous les arguments d’EMCA sacher que la syntaxe générale est :
Emca operation mode flag parameters
Et si vous pouvez voire la liste complète en tapant :
>emca -h
Alors pour créer votre console d'administration rien de plus simple après la création de la base tapez :
$ emca -config dbcontrol db -repos create

STARTED EMCA at jun 06, 2010 9:21:39 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: db10
Listener port number: 1521
Password for SYS user: change_on_install
Password for DBSNMP user: manager
Password for SYSMAN user: manager
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /u01/app/oracle/product/10.2.0/db_1

Database hostname ................ dbserver
Listener port number ................ 1521
Database SID ................ db10
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
jun 06, 2010 10:00:12 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/db10/emca_2010-01-06_09-21-39-PM.log.
jun 06, 2010 10:00:15 PM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
jun 06, 2010 10:05:51 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
jun 06, 2010 10:06:01 PM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
jun 06, 2010 10:07:49 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
jun 06, 2010 10:07:49 PM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is http://dbserver:1158/em <<<<<<<<<<<
Enterprise Manager configuration completed successfully
FINISHED EMCA at jun 06, 2010 10:07:49 PM

Vous devez fournir quelques informations comme le SID le port listner.., cela prends quelques minutes et vous pouvez suivre les étapes de création, une fois le programme terminé noter l'adresse à la fin (sous forme de http(s)://nommachine:port/em) est lancer la avec votre navigateur
Pour le recréer il suffit de changer create avec recreate dans la commande précédente mais si vous êtes amené à désinstaller entreprise manager la commande suivante suffira :
$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at jun 06, 2006 9:53:55 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.

Enter the following information:
Database SID: db10
Listener port number: 1521
Password for SYS user: change_on_install
Password for SYSMAN user: manager

Do you wish to continue? [yes(Y)/no(N)]: Y
jun 06, 2006 9:54:15 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/db10/emca_2006-01-06_09-53-55-PM.log.
jun 06, 2006 9:54:16 PM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
jun 06, 2006 9:54:35 PM oracle.sysman.emcp.EMReposConfig dropRepository
INFO: Dropping the EM repository (this may take a while) ...
jun 06, 2006 9:56:48 PM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at jun 06, 2006 9:56:48 PM

Il y une autre commande utile pour vérifier le statut d'entreprise manager c'est :
>emctl status dbconsole --qui fournit le statut de la console web.

Et :
>emctl status agent --pour le status de l'agent d'entreprise manager

Vous pouvez arrêter la console avec :
>emctl stop dbconsole

Ou la démarrer avec :
>emctl start dbconsole

SQL : Fonctions LAG et LEAD

2010-01-18T05:12:00.000-08:00

Les fonctions LAG et LEAD sont deux fonctions analytiques qui permettent d'accéder à une ligne avant ou après la ligne courante, la syntaxe de ces fonctions est simple :
LAG|lead(colonne, nombre de ligne avant|après la ligne courante, valeur par default pour la première|dérniere ligne).

La valeur par défaut n'est pas obligatoire si vous l'omettez Oracle renvoie NULL à la place, un petit exemple :
SQL> select empno,sal,deptno,sal-lag(sal,1)
2 over (order by empno) as trou
3 from emp order by empno;

EMPNO	SAL	DEPTNO	TROU
7369	800	20
7499	1600	30	800
7521	1250	30	-350
7566	2975	20	1725
7654	1250	30	-1725
7698	2850	30	1600
7782	2450	10	-400
7788	3000	20	550
7839	5000	10	2000
7844	1500	30	-3500
7876	1100	20	-400
7900	950	30	-150
7902	3000	20	2050
7934	1300	10	-1700

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

L’exemple précédent calcul la différence de salaire entre un employé et celui qui le précède directement dans empno, le suivant calcule l'inverse :

SQL> select empno,sal,deptno,sal-lead(sal,1)
2 over (order by empno) as trou
3 from emp order by empno;

EMPNO	SAL	DEPTNO	TROU
7369	800	20	-800
7499	1600	30	350
7521	1250	30	-1725
7566	2975	20	1725
7654	1250	30	-1600
7698	2850	30	400
7782	2450	10	-550
7788	3000	20	-2000
7839	5000	10	3500
7844	1500	30	400
7876	1100	20	150
7900	950	30	-2050
7902	3000	20	1700
7934	1300	10

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

voire le post fonctions analytiques

L'utilitaire Oerr

2010-01-18T03:37:00.000-08:00

Oerr est un utilitaire pratique qui permet de donner la cause des erreurs oracle et les solutions possibles, l'utilisation est facile :
>oerr module_erreur num_erreur

exemples des erreurs ORA:

oracle@host:~> oerr ORA 257
00257, 00000, "archiver error. Connect internal only, until freed."
// *Cause: The archiver process received an error while trying to archive
// a redo log. If the problem is not resolved soon, the database
// will stop executing transactions. The most likely cause of this
// message is the destination device is out of space to store the
// redo log file.
// *Action: Check archiver trace file for a detailed description
// of the problem. Also verify that the
// device specified in the initialization parameter
// ARCHIVE_LOG_DEST is set up properly for archiving.

des erreurs du listner :
oracle@host:~> oerr TNS 201
00201, 00000, "Unable to read answer connection data :%s:\n"
// *Cause: Improperly formed connect data sent to the Interchange listener.
// *Action: If problem persists, check the application using the
// Interchange (it may be closing down or crashing). Call Oracle Customer
// Support if the problem cannot be located.

Hints PARALLEL et APPEND

2010-01-18T01:54:00.000-08:00

Pour charger des données d'une table à une autre on utilise généralement :
Insert into table1
select * from table2;

Mais cette forme de chargement peut prendre du temps si les données à chargé sont trop volumineux, pour accélérer le chargement on utilise deux choses :
1) le hint APPEND qui permet d'insérer les données au delà du High-water mark ce qui évite de chercher des blocs de données libérés après utilisation :
Insert /*+ APPEND */ into grande_table1
NOLOGGING
select * from grande_table2;

La clause NOLOGGING permet d'éviter de générer des données de journalisation en masse lors du chargement.

2) le hint PARALLEL qui permet d'activer le chargement en parallèle, le parallélisme peut être activé lors de l'insertion ou l'affecter comme attribut à la table :
Insert /*+ PARALLEL (grande_table2,5)*/ into grande_table1
NOLOGGING
select * from grande_table2;

Ou :
ALTER TABLE grande_table2 (DGREE 5);

Oracle : les fonctions analytiques

2010-01-18T01:41:00.000-08:00

Les fonctions analytiques disponible dans ORACLE depuis 8.1 pour répondre à certains problèmes qu'il est difficile d'aborder avec SQL, comme les requêtes pour trouver les n premiers enregistrements dans un group; ou faire un calcul par rapport à la nième ligne, leurs utilisation est plus facile et plus performante que de faire des petites fonctions en PL/SQL, la syntaxe générale :
>fonction_analytique(argument) OVER ()

La clause partition :
Comme son nom l'indique permet de partitionner (pas au sens des tables partitionnées) un résultat en un ensemble de groups selon une valeur.

Exemple:
SQL> select ename,sal,deptno,avg(sal)
over(partition by deptno) from emp ;

ENAME	SAL	DEPTNO	AVG(SAL)OVER(PARTITIONBYDEPTNO)
CLARK	2450	10	2916,66667
KING	5000	10	2916,66667
MILLER	1300	10	2916,66667
SMITH	800	20	2175
ADAMS	1100	20	2175
FORD	3000	20	2175
SCOTT	3000	20	2175
JONES	2975	20	2175
ALLEN	1600	30	1566,66667
BLAKE	2850	30	1566,66667
MARTIN	1250	30	1566,66667
JAMES	950	30	1566,66667
TURNER	1500	30	1566,66667
WARD	1250	30	1566,66667

14 ligne(s) sélectionné(s).
SQL>

La clause d'ordre :
Cette clause sert pour spécifier l'ordre dans lequel le groupe sera traité, attention ici l'ordre by à un comportement spécial il ajoute une fenêtre par défaut qui définit par la ligne en cours est tous les lignes précédentes dans le groupe.
Exemple:
SQL> select ename,sal,deptno,avg(sal)
over(partition by deptno order by ename) as avg_an from emp ;

ENAME	SAL	DEPTNO	AVG_AN
CLARK	2450	10	2450
KING	5000	10	3725
MILLER	1300	10	2916,66667
ADAMS	1100	20	1100
FORD	3000	20	2050
JONES	2975	20	2358,33333
SCOTT	3000	20	2518,75
SMITH	800	20	2175
ALLEN	1600	30	1600
BLAKE	2850	30	2225
JAMES	950	30	1800
MARTIN	1250	30	1662,5
TURNER	1500	30	1630
WARD	1250	30	1566,66667

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

Ici comme vous le constater le résultat est partitionné par deptno est le tri par ENAME dans chaque département, mais la moyenne du salaire est calculé par rapport aux lignes précédentes en même département, mais si vous ne spécifiez pas PARTITION BY alors le groupe pris est toute la table.

SQL> select ename,sal,deptno,avg(sal) over(order by ename) as avg_an from emp ;

ENAME	SAL	DEPTNO	AVG_AN
ADAMS	1100	20	1100
ALLEN	1600	30	1350
BLAKE	2850	30	1850
CLARK	2450	10	2000
FORD	3000	20	2200
JAMES	950	30	1991,66667
JONES	2975	20	2132,14286
KING	5000	10	2490,625
MARTIN	1250	30	2352,77778
MILLER	1300	10	2247,5
SCOTT	3000	20	2315,90909
SMITH	800	20	2189,58333
TURNER	1500	30	2136,53846
WARD	1250	30	2073,21429

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

La clause de fenêtrage :
La clause de fenêtrage sert à définir une fenêtre dans le même groupe à lequel sera appliquée la fonction, exemple:

SQL> select ename,sal,deptno,avg(sal)
2 over(partition by deptno order by ename rows 2 preceding) as avg_an
3 from emp ;

ENAME	SAL	DEPTNO	AVG_AN
CLARK	2450	10	2450
KING	5000	10	3725
MILLER	1300	10	2916,66667
ADAMS	1100	20	1100
FORD	3000	20	2050
JONES	2975	20	2358,33333
SCOTT	3000	20	2991,66667
SMITH	800	20	2258,33333
ALLEN	1600	30	1600
BLAKE	2850	30	2225
JAMES	950	30	1800
MARTIN	1250	30	1683,33333
TURNER	1500	30	1233,33333
WARD	1250	30	1333,33333

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

Ici la fenêtre est de 3 lignes et la moyenne est calculée à partir de la ligne courante et les deux lignes précédente (ex: scott (3000+2975+3000)/3 = 2991,6667), il y a autres spécificateurs de fenetrage pour la list complète consulter la reference SQL.

Si vous omettez toutes les clauses alors la pas de notion d'ordre mais la fenêtre est la partition ne font qu'un toute la table :

SQL> select ename,sal,deptno,avg(sal) over() as avg_an from emp;

ENAME	SAL	DEPTNO	AVG_AN
SMITH	800	20	2073,21429
ALLEN	1600	30	2073,21429
WARD	1250	30	2073,21429
JONES	2975	20	2073,21429
MARTIN	1250	30	2073,21429
BLAKE	2850	30	2073,21429
CLARK	2450	10	2073,21429
SCOTT	3000	20	2073,21429
KING	5000	10	2073,21429
TURNER	1500	30	2073,21429
ADAMS	1100	20	2073,21429
JAMES	950	30	2073,21429
FORD	3000	20	2073,21429
MILLER	1300	10	2073,21429

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

Oracle : WRAP obsfucation PL/SQL

2010-01-14T04:01:00.000-08:00

parfois on a besoin de distribuer du code PL/SQL sans exposer le code source, pour cela Oracle propose un utilitaire autonome qui masque le code source en le convertissant en code portable, le code resultant est independant de la plate forme,permet un chargement et une liaison dynamique, la syntaxe est simple :
C:\>wrap iname=proc.sql oname=proc.plb
PL/SQL Wrapper: Release 9.2.0.1.0- Production on Lu Jan 11 15:18:35 2010
Copyright (c) Oracle Corporation 1993, 2001. All Rights Reserved.
Processing proc.sql to proc.plb
C:\>

le fichier proc.sql contient le code suivant :
create or replace procedure hello is
begin
dbms_output.put_line('Appel de procedure');
end;
/

une fois le fichier converti on l'appel comme n'importe quel fchier sql :
SQL> @c:\proc.plb
Procédure créée.
SQL> set serveroutput on
SQL> exec hello
Appel de procedure

Procédure PL/SQL terminée avec succés.
SQL>

vous pouvez consulter la vue user_source pour voire le code source converti de la procedure,c'est la meme chose que vous trouverez dans le fichier plb.
SQL> col name format a10
SQL> set pagesize 1000
SQL> col text format a30
SQL> set linesize 1000
SQL> select NAME,TYPE,TEXT from user_source where NAME='HELLO';

NAME	TYPE	TEXT
HELLO	PROCEDURE	procedure hello wrapped
		0
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		abcd
		3
		7
		9200000
		1
		4
		0
		4
		2 :e:
		1HELLO:
		1DBMS_OUTPUT:
		1PUT_LINE:
		1Appel de procedure:
		0

		0
		0
		14
		2
		0 9a b4 55 6a :2 a0 6b 6e
		a5 57 b7 a4 b1 11 68 4f
		1d 17 b5
		14
		2
		0 3 17 18 1c 20 24 28
		2b 30 31 36 38 3c 3e 4a
		4e 50 51 5a
		14
		2
		0 b 0 :3 1 :2 d 16 :b 1
		14
		4
		0 1 0 :2 1
		:6 3 :2 2 :7 1
		5c
		4
		:3 0 1 :a 0 f
		1 :8 0 2 :2 0
		f 1 3 :2 0
		2 :3 0 3 :3 0
		5 6 0 4
		:4 0 3 7 9
		:2 0 b 5 e
		:3 0 e 0 e
		d b c :6 0
		f :2 0 1 3
		e 12 :3 0 11
		f 13 :8 0
		7
		4
		:3 0 1 8 1
		a
		1
		4
		0
		12
		0
		1
		14
		1
		1
		0 0 0 0 0 0 0 0
		0 0 0 0 0 0 0 0
		0 0 0 0
		1 0 1
		0