Utilisation de SET UNUSED en Oracle.

Une suppression de colonne dans une table existante et possédant de nombreuses lignes (données volumineuses) peut s'avérer une opération très longue. Dans ce cas, il est souhaitable de rendre la colonne inutilisable à l'aide de la clause SET UNUSED. Cette option de permet pas de libérer l'espace disque occupé par la colonne, mais elle permet de planifier l'opération de suppression de la colonne à un moment.

Exemple:
Rendre inutilisable la colonne TEL de la table EMPLOYEES. Dans un premier temps, la colonne est masquée comme inutilisée.

SQL> ALTER TABLE EMPLOYEES SET UNUSED (TEL);
Table altered

Pour connaître les tables qui contiennent des colonnes inutilisées, il faut interroger la vue du dictionnaire de données DBA_UNUSED_COL_TABS.

Connecter avec le compte sys as sysdba:
select * from dba_unused_col_tabs;

Dans un deuxième temps, il faut supprimer toutes les colonnes inutilisées de la table EMPLOYEES. Lors de cette opération, on demande à Oracle de réaliser un point de synchronisation (CHECKPOINT) toutes les 100 suppressions.

SQL> ALTER TABLE EMPLOYEES DROP UNUSED COLUMNS CHECKPOINT 100;
Table altered

Connect By Prior Oracle.

En utilisant le schéma SCOTT fourni par Oracle, voici une requête hiérarchique de la fonction CONNECT BY PRIOR:

    SELECT lpad(' ',2*level)||ename as "Arbre"
    FROM emp
    CONNECT BY PRIOR empno=mgr
    START WITH ename = 'KING'

 Ça nous donne le résultat suivant:

Arbre
-------------------------------------------
  KING
    JONES
      SCOTT
        ADAMS
      FORD
        SMITH
    BLAKE
      ALLEN
      WARD
      MARTIN
      TURNER
      JAMES
    CLARK

Il y a une autre fonction Oracle qui  permet d'afficher la concaténation du chemin hiérarchique pour chaque élément listé (SYS_CONNECT_BY_PATH).

    SELECT SYS_CONNECT_BY_PATH(ename, ' * ')
    FROM emp
    CONNECT BY PRIOR empno=mgr
    START WITH ename = 'KING'

Ce qui nous donne le résultat suivant:
SYS_CONNECT_BY_PATH(ENAME,'*')
-------------------------------------------
 * KING
 * KING * JONES
 * KING * JONES * SCOTT
 * KING * JONES * SCOTT * ADAMS
 * KING * JONES * FORD
 * KING * JONES * FORD * SMITH
 * KING * BLAKE
 * KING * BLAKE * ALLEN
 * KING * BLAKE * WARD
 * KING * BLAKE * MARTIN
 * KING * BLAKE * TURNER
 * KING * BLAKE * JAMES
 * KING * CLARK

13 rows selected

On constate que KING est le président, et par exemple le superviseur de SCOTT est JONES.

Récupérer les données effacées.

En faisant des tests à l'instant avec une table TELEPHONE et par erreur je ne sais pas ce qu'il est se passer avec l'outil PL/SQL Developer 8, les données ont été effacées sans faire le DELETE FROM TELEPHONE (quand je fais un ordre de SELECT * from TELEPHONE, il me donne rien).

J'ai vérifié les objets effacés dans la corbeille d'oracle avec la vue dba_recyclebin, mais aucune trace. Alors, j'étais obligé de manipuler les requêtes avec FLASHBACK, dont voici toutes les étapes:


SQL> flashback TABLE telephone TO timestamp (systimestamp - interval '1' minute);
Done
SQL> select * from telephone;

Comment configurer Oracle Forms 10g avec Java 7 ?

Étant donné que Jinitiator n'est plus supporté par Oracle pour l'exécution des formulaires de Oracle Forms 10g , voici toutes les étapes pour configurer ce dernier avec Java, en téléchargeant le document sur mon site web à cette url: http://www.oraweb.ca/doc/Config_OracleForms10g_Java.pdf

Un problème survient avec la version 7 du java lors de l'exécution de la Form:

Pour régler ce problème, suivre les instructions dans le guide.
Testé sous Windows 7, Oracle Forms 10g avec une BD 10g et Firefox comme navigateur internet.

Que signifie DDL, DML, DCL et TCL en Oracle ?

Le DDL pour Data Definition Language ou Langage de définition des données, qui regroupe les ordres utilisés pour créer, modifier ou supprimer les structures de la table de la BD (Tables, Index, Vues, et d'autres objets). Il s'agit principalement des commandes telles que CREATE, ALTER et DROP.

Le DML pour Data Manipulation Language ou Langage de manipulation des données, qui regroupe les ordres utilisés pour manipuler les données contenues dans la BD. Il s'agit principalement des commandes telles que SELECT, INSERT, DELETE et UPDATE.

Le DCL pour Data Control Language, qui regroupe les ordres utilisés pour gérer la sécurité des accès aux données. Il s'agit principalement des commandes telles que GRANT et ROVOKE.

Le TCL pour Transaction Control Language, qui regroupe les ordres utilisés pour gérer la validation ou non des mises à jour effectuées sur la BD. Il s'agit principalement des commandes telles que COMMIT et ROLLBACK.

TRUNCATE TABLE ORACLE VS DELETE

La commande DELETE permet d'effacer des enregistrements d'une table. Cette opération peut être lente s'il y a un très grand nombre d'enregistrement au niveau de la table. À chaque opération, on met à jour les enregistrements UNDO ainsi que les enregistrements REDO LOGS.

Il est possible de revenir en arrière pour récupérer des données en cas de l'effacement avec un ROLLBACK.

L'ordre TRUNCATE permet de supprimer toutes les lignes d'une table. Ce n'est pas un ordre du LMD mais un ordre du LDD donc cela signifie qu'il déclenche un COMMIT implicite. Il n'est pas possible de récupérer les données après un TRUNCATE donc il faut l'utiliser avec précaution.
Cette opération est très rapide même s'il y a un très grand nombre d'enregistrements au niveau de la table. On ne génère pas ni UNDO, ni REDO LOGS.

Attention, pas de rollback possible avec la commande Oracle Truncate Table.


En terme de performance, le TRUNCATE est plus rapide que le DELETE. Le système effectue moins de manipulation interne avec un TRUNCATE (pas de passage par le cache, journalisation moindre....).

En revanche s'il existe des clés étrangères, le TRUNCATE ne fonctionnera pas. Il faut également avoir des droits importants pour utiliser le TRUNCATE. Pour plus de détails avec des exemples, visiter mon site web qui sera accessible dans quelques mois à cette url: www.oraweb.ca

Rechercher et supprimer des doublons dans une table.

Voici une requête qui me semble intéressante pour rechercher et supprimer des doublons en faisant la recherche avec le ROWID.

Avant ça, on va visualiser la table:

Donc, on voit que le nom et le prénom Kevin Feeney est en doublon. Pour rechercher la ligne en trop, exécuter cette requête:

Voici le code:
select * from employees a where a.rowid > ANY
(select b.rowid from employees b where a.first_name=b.first_name
 and a.last_name=b.last_name);

Pour supprimer le doublon, exécuter cette requête:
delete from employees a where a.rowid > ANY
(select b.rowid from employees b where a.first_name=b.first_name
 and a.last_name=b.last_name);

Si on fait un SELECT * FROM EMPLOYEES, le doublon ne sera pas affiché.

Renseigner une séquence à partir d'un trigger.

Voici les étapes pour renseigner une séquence à partir d'un trigger BD.

• Créer une séquence.

CREATE SEQUENCE SEQ_EMPL
START WITH 1
INCREMENT BY 1
MAXVALUE 999;

• Créer le trigger approprié à la séquence (avant l'insertion dans la table EMPLOYEES).

CREATE OR REPLACE TRIGGER TR_EMPL_ID
BEFORE INSERT ON EMPLOYEES
FOR EACH ROW

BEGIN
    SELECT SEQ_EMPL.NEXTVAL
    INTO :NEW.EMPLOYEE_ID FROM DUAL;
 END;

Visualiser les index posés sur une table.

Au fur et à mesure qu'on crée des clés (PK pour Primary Key, FK pour Foreign Key, etc..), autant d'index seront crées automatiquement. Pour savoir quels sont tous les index posés sur une table, exécuter cette requête (dans notre exemple, on a pris la table EMPLOYEES du schéma HR fourni par Oracle):

select index_name "Index" , lower(column_name) as "Colonne(s)"
from user_ind_columns
where table_name=upper('employees')
order by index_name,column_position;

Voici les résultats sur l'écran:

Connaitre les sessions actives.

Parfois, pour une raison quelconque, on ne sait pas si quelle session est bloquée et quel est l'utilisateur qui a bloqué (généralement ce genre de problème est dû à un verrouillage de l'enregistrement, par exemple une personne veut faire un UPDATE et une autre personne veut faire un DELETE sur le même enregistrement). Pour savoir quelles sont les informations de la session, exécuter cette requête (connecter avec un user sys as sysdba):

select a.USERNAME, a.OSUSER, a.PROGRAM, a.LOGON_TIME, a.MACHINE, a.MODULE, B.LOCK_ID1, a.SID, a.SERIAL#, 'BLOCKEUR'
  from v$session a, dba_waiters b
 where a.sid = b.holding_session
   and a.status <> 'KILLED'
union
select a.USERNAME, a.OSUSER, a.PROGRAM, a.LOGON_TIME, a.MACHINE, a.MODULE, B.LOCK_ID1, a.SID, a.SERIAL#, 'BLOCKER'
  from v$session a, dba_waiters b
 where a.sid = b.waiting_session
   and a.status <> 'KILLED'
union
select USERNAME, OSUSER, PROGRAM, LOGON_TIME, MACHINE, MODULE, TO_NUMBER(NULL), SID, SERIAL#, 'SESSION'
  from v$session
 where username is not null
   and status <> 'KILLED';

Si on veut KILLER une session bloquée, il suffit d'exécuter la requête suivante (dans notre exemple, le SID 145, serial 71).

ALTER SYSTEM KILL SESSION '145,71';
On peut faire également: SELECT * FROM DBA_BLOCKERS pour savoir quel est le ID de la session qui est bloqué ?.