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é ?.

Site web en ligne...

J'ai mis en ligne mon site web consacré aux technologies d'oracle.
Voici le lien pour visiter: www.oraweb.ca

Liens pour APEX.

Voici une liste des blogues concernant l'outil d'oracle APEX (Oracle application Express):
http://www.absodia.com/A257-liste-des-blogs-oracle-apex-application-express-html-db.html
Un autre lien pour les actions dynamiques:
http://st-curriculum.oracle.com/obe/db/apex/r40/apexdynactions/apexdynactions_ll.htm#top

Exporter des données Oracle vers Excel 2007.

Avec PL/SQL Developer 7, on pourrait exporter des données Oracle vers Excel, dont voici les étapes:

• Faites une requête en SQL avec un SELECT (exemple SELECT * From S_ITEM)
• Ramener tous les enregistrements de la table afin de les exporter (fetch avec PL/SQL Developer).
  

• Cliquer sur le bouton droit de la souris pour afficher le menu contextuel suivant (Export Results - CSV file).
  

• Une boite de dialogue s'ouvre pour sauvegarder le fichier CSV qui va utiliser dans Excel.

• Dernière étape à ouvrir le fichier CSV avec Excel 2007.

Créer un utilisateur sous SQL Plus.

Voici la méthode complète et paramétrable pour créer un utilisateur sous SQL Plus :

SET VERIFY OFF
ACCEPT passwd PROMPT 'Entrer le mot de passe pour l'utilisateur SYSTEM : ' hide
ACCEPT nom_bd PROMPT 'Entrer le nom de la BD ou la chaine de connexion . Default is [LOCAL] : '

REM Connect
connect SYSTEM/&passwd@&nom_bd
ACCEPT username PROMPT 'Entrer le nouveau utilisateur : '
ACCEPT passwd_user PROMPT 'Entrer le mot de passe : ' HIDE
PROMPT ' Tablespaces de votre BD :'
SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;
ACCEPT def_tbs PROMPT 'Entrer tablespace par défaut: '
ACCEPT tmp_tbs PROMPT 'Entrer tablespace temporaire: '
ACCEPT u_quota PROMPT 'Entrer quota (exemple 100K, ou 1M, ou UNLIMITED): '
ACCEPT quota_tbs PROMPT ' Tablespace: '

CREATE USER &username
IDENTIFIED BY &passwd_user
DEFAULT TABLESPACE &def_tbs
TEMPORARY TABLESPACE &tmp_tbs
QUOTA &u_quota ON &quota_tbs;

GRANT CONNECT, RESOURCE TO &username;

Si vous travaillez avec l'interface graphique de TOAD ou PL/SQL Developer, ça serait très facile de créer un utilisateur.

Abed

Extraire la définition des objets depuis une BD.

Il existe un moyen pour extraire toutes les définitions des objets de la BD (stucture des tables, index, tablespaces, contraintes, etc...) en utilisant un package DBMS_METADATA ainsi qu'une méthode GET_DDL qui permet d'envoyer les données formatées en SQL ou XML, dont voici la syntaxe:

Sous SQL Plus ou PL/SQL Developer, faites ceci:
SET HEAD OFF
SET LONG 1000
SET PAGES 0

SELECT DBMS_METADATA.GET_DDL('TABLE','EMPLOYES') From DUAL;

Pour extraire les données avec le format XML, il suffit de changer GET_DDL par GET_XML.

Abed

Sécuriser une table.

Il est possible qu'on crée une procédure ainsi qu'un déclencheur pour pouvoir sécuriser une table (exemple la table employees du schéma SCOTT ou HR).

Voici le code de la procédure:

CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205,
'Vous avez le droit de manipuler la table EMPLOYE entre les heures du travail ');
END IF;
END secure_dml;
/

Et le déclencheur pour la procédure:

CREATE OR REPLACE TRIGGER secure_employees
BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
-- Appel de la procédure
secure_dml;
END secure_employees;

Exemple: On veut faire la mise à jour du salaire pour un employé numéro 204:

UPDATE EMPLOYEES
SET SALARY = SALARY * 1.2
WHERE EMPLOYEE_ID=204;

Voici le message d'erreur de PL/SQL Developer:



Abed