TRUNCATE : Appel de Procédure Stockée via db link

Alors que le delete, lui, fonctionne sans problème, la commande truncate en tant que DDL ne passe pas au travers d'un db link.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1

Sur la base source :
 
SQL> DELETE FROM toto@versqua;

1 row deleted.

SQL> ROLLBACK;

Rollback complete.

SQL> TRUNCATE TABLE toto@versqua;
TRUNCATE TABLE toto@versqua
                    *
ERROR at line 1:
ORA-02021: DDL operations are not allowed on a remote database

Il existe une solution qui consiste à disposer une procédure stockée sur la base cible qui effectue le truncate. Cette procédure étant appelée depuis la base source via db link.
 

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         1
 

SQL> CREATE PROCEDURE p_u_truncate (p_table IN VARCHAR2)
  2     IS
  3     BEGIN
  4        EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || p_table;
  5     END;
  6  /

Procedure created.

Sur la base source :

SQL> EXEC p_u_truncate@versqua('TOTO');

PL/SQL procedure successfully completed.

Sur la base cible :

SQL> SELECT COUNT(*) FROM toto;

  COUNT(*)
----------
         0

Explication : la procédure stockée sur la base cible est appelée depuis la base source et s'exécute sur la base cible.

DOUBLONS : détecter les doublons partiels

Comment détecter les lignes d'une table faisant doublon sur une liste de colonne (et non pas sur l'ensemble de la ligne).

Exemple :

SQL> CREATE TABLE toto (
  2     pays VARCHAR2(3),
  3     ville VARCHAR2(10),
  4     note NUMBER
  5  );

Table created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',1);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',2);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON',4);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON',5);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','MARSEILLE',5);

1 row created.

SQL> INSERT INTO toto VALUES ('USA','NEW YORK',5);

1 row created.

SQL> COMMIT;

Commit complete.

Pour le cas classique :

SQL> SELECT t1.pays, t1.ville, t1.note, count(*)
  2  FROM toto t1
  3  GROUP BY t1.pays, t1.ville, t1.note
  4  HAVING COUNT(*) > 1
  5  ;

PAY VILLE            NOTE   COUNT(*)
--- ---------- ---------- ----------
FRA PARIS               3          2

Et la liste :

SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (
  2     SELECT MIN(t2.rowid) FROM toto t2
  3     WHERE t1.pays=t2.pays
  4       AND t1.ville=t2.ville
  5       AND t1.note=t2.note
  6     HAVING COUNT(*) > 1
  7  )
  8  ;

PAY VILLE            NOTE
--- ---------- ----------
FRA PARIS               3

FRA PARIS               3

Et par extension, pour le cas des doublons partiels :

SQL> SELECT t1.pays, t1.ville, COUNT(*)
  2  FROM toto t1
  3  GROUP BY t1.pays, t1.ville
  4  HAVING COUNT(*) > 1
  5  ;

PAY VILLE        COUNT(*)
--- ---------- ----------
FRA LYON                2
FRA PARIS               4

Et la liste :

SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (
  2     SELECT MIN(t2.rowid) FROM toto t2
  3     WHERE t1.pays=t2.pays
  4       AND t1.ville=t2.ville
  5     HAVING COUNT(*) > 1
  6  )
  7  ;

PAY VILLE            NOTE
--- ---------- ----------
FRA PARIS               1
FRA PARIS               2
FRA PARIS               3
FRA PARIS               3
FRA LYON                4
FRA LYON                5

APPEND NOLOGGING : accélérer les insertions

Pour les chargements massifs, il peut être intéressant d'utiliser certaines options qui permettent d'accélérer le temps de traitement. Bien sûr ces options ont des contre-parties.

L'option APPEND pour l'insert force Oracle à passer directement au-delà du HWM (High Water Mark) pour insérer les nouvelles lignes. Elle évite ainsi la recherche des blocks libres et réduit fortement le nombre de blocks à sauvegarder dans les UNDO. Mais elle conduit à une éventuelle perte d'espace disque.

L'option NOLOGGING indique à Oracle qu'il n'est pas nécessaire de logguer dans les REDO les changements qui ont lieu dans la table. Il s'ensuit une écriture moins importante dans les REDO, mais également l'impossibilité de récupérer la table avec les nouvelles lignes en cas de restauration de cette dernière.

Exemple

Préparation :

SQL> CREATE TABLE toto AS
  2  SELECT rownum ID, dbms_random.string('A', 12) STR,
  3  trunc(dbms_random.value(1,1000)) NUM
  4  FROM all_objects
  5  WHERE rownum <= 200000
  6  ;

Table created.

SQL> INSERT INTO toto SELECT * FROM toto;

200000 rows created.
SQL> INSERT INTO toto SELECT * FROM toto;

400000 rows created.
SQL> COMMIT;

Commit complete.

Session 1 :

SQL> SET TIMING ON
SQL> INSERT INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:31.04
SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:02.09
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                  2270900
 

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------- ----------
rollback changes - undo records applied                     6087

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               31320224

Session 2 :

SQL> SET TIMING ON
SQL> INSERT /*+ APPEND */ INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:12.06

SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.00
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                   258516

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------- ----------
rollback changes - undo records applied                        0

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               25438660

Session 3 :

SQL> SET TIMING ON
SQL> INSERT /*+ APPEND NOLOGGING */ INTO toto SELECT * FROM toto;

800000 rows created.

Elapsed: 00:00:04.01

SQL> ROLLBACK;

Rollback complete.

Elapsed: 00:00:00.00
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'undo change vector size';

NAME                                                       VALUE
----------------------------------------------------- ----------
undo change vector size                                      208

SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'rollback changes - undo records applied';

NAME                                                       VALUE
----------------------------------------------------------------
rollback changes - undo records applied                        0
 
SQL> SELECT name, value FROM v$mystat NATURAL JOIN v$statname WHERE name = 'redo size';

NAME                                                       VALUE
----------------------------------------------------- ----------
redo size                                               24488552

en résumé :

APPEND   NOLOGGING  INSERT(sec)  UNDO_VECTOR  REDO_SIZE

     N           N           31      2270900   31320224

     Y           N           12       258516   25438660

     Y           Y            4          208   24488552

ENABLE ROW MOVEMENT : tables partitionnées

Oracle garantit qu'une ligne ne sera pas déplacée physiquement par un update et donc que son ROWID ne sera pas changé. Ce phénomène pourrait en effet survenir dans une table partitionnée avec un update sur la colonne de partitionnement, forçant Oracle à déplacer la ligne dans la bonne partition. (Déplacement physique, changement de ROWID).

Exemple :

SQL> CREATE TABLE toto(a NUMBER)
  2  PARTITION BY RANGE (a)(
  3  PARTITION p1 VALUES LESS THAN (10),
  4  PARTITION p2 VALUES LESS THAN (20),
  5  PARTITION p3 VALUES LESS THAN (MAXVALUE));

Table created.

SQL> INSERT INTO toto VALUES (5);

1 row created.

SQL> UPDATE toto SET a=15;
UPDATE toto SET a=15
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

 

Pourquoi Oracle provoque-t'il cette erreur ?

A priori on ne voit pas de raison pour laquelle on ne pourrait pas tranquillement mettre à jour une table, même sur la colonne de partitionnement, sans provoquer une erreur... Ceci est en fait un héritage, qu'Oracle nous a transmis pour que lors de l'introduction de tables partitionnées, les programmes qui utilisaient les ROWID n'aient pas de mauvaise surprise en partitionnant leurs tables. Le changement du ROWID lors d'un update pouvant être catastrophique avec ce genre de conception de programme.

Mais comment faire alors ?

Par défaut, donc, Oracle empêche les mises à jour des colonnes de partitionnement des tables partitionnées. Pour autoriser ces mises à jour, il est donc nécessaire de passer la commande ENABLE ROW MOVEMENT en préalable :

Exemple :

SQL> ALTER TABLE toto ENABLE ROW MOVEMENT;

Table altered.

SQL> UPDATE toto SET a=15;

1 row updated.

Bien qu'il existe la commande inverse qui rétablit l'impossibilité de mettre à jour ces colonnes (DISABLE ROW MOVEMENT), si aucun de vos programmes n'utilise les ROWID, il n'est pas nécessaire de repasser les tables en mode par défaut (disable).

Voici tout de même la commande :

SQL> ALTER TABLE toto DISABLE ROW MOVEMENT;

Table altered.

VARIABLES : bind, substitution, programme

Dans cet exemple sont illustrés les emplois des différents types de variable suivants :

 

- variable de substitution (define) - interprétée par SqlPlus

- bind variable (variable) - SQL - PL/SQL
- variable de programme (declare) - PL/SQL

Ces variables sont utilisées pour des raisons bien spécifiques, et ne doivent en aucun cas être confondues...

Exemple

  

SQL> SET VERIFY OFF
SQL> DEFINE matable=dba_users;
SQL> VARIABLE b NUMBER;
SQL> DECLARE
  2  res VARCHAR2(30);
  3
  4  BEGIN
  5  :b :=0;
  6  SELECT username INTO res FROM &matable WHERE user_id=:b;
  7  dbms_output.put_line('USER IS : '||res);
  8  END;
  9  /
USER IS : SYS

PL/SQL procedure successfully completed.

Autre exemple pour une bind variable en SQL pur

SQL> VARIABLE b NUMBER;
SQL> EXECUTE :b:=0;

PL/SQL procedure successfully completed.

SQL> SELECT username
  2  FROM all_users
  3  WHERE user_id = :b;

USERNAME
------------------------------
SYS

OUT : valeur de retour d'une procedure

Les procédures Oracle offrent la possibilité de renvoyer une valeur en sortie au travers d'une variable OUT définie dans la liste des paramètres de la procédure.

CREATE OR REPLACE PROCEDURE p_sqrt(
    a IN NUMBER, b OUT NUMBER
) AS
BEGIN
    b:=a*a;
END;
/

SET SERVEROUTPUT ON SIZE 2000

DECLARE
  entree NUMBER;
  sortie NUMBER;
BEGIN
  entree:=9;
  p_sqrt(entree,sortie);
  dbms_output.put_line(entree||'² = '||sortie);
END;
/

9² = 81

PL/SQL procedure successfully completed.

CUBE : requête analytique

Il existe de nombreuses fonctions analytiques permettant d'obtenir des rapports multi-dimensionnels plus ou moins complexes.

Exemple de Cube :

SET LINES 150
SET PAGES 30
SET FEED OFF
SET ESCAPE \

BREAK ON typ SKIP 1 ON tbs SKIP 0
COL tbs FOR A10 TRUNC

SELECT
    NVL(t.typ,'ALL') AS typ,
    NVL(t.tablespace_name,'________') AS tbs,
    ROUND(SUM(t.bytes)/1024/1024,0) AS siz_mo
FROM (
    SELECT
   (CASE
        WHEN s.segment_type LIKE 'TAB%' THEN 'TAB'
        WHEN s.segment_type LIKE 'IND%' THEN 'IND'
        ELSE 'OTH'
    END
    ) AS typ,
    s.tablespace_name,
    s.bytes
    FROM dba_segments S
    WHERE owner='SYS'
) T
GROUP BY CUBE(t.typ, t.tablespace_name)
ORDER BY 1 DESC, 2 DESC
/

SET FEED ON
SET ESCAPE OFF

 

Résultat :
 

TYP TBS            SIZ_MO
--- ---------- ----------
TAB TOOLS              23
    SYSTEM            402
    SYSAUX            205
    ________          629

OTH UNDOTBS            52
    SYSTEM            218
    SYSAUX             21
    ________          291

IND TOOLS               0
    SYSTEM            370
    SYSAUX            283
    ________          654

ALL UNDOTBS            52
    TOOLS              23
    SYSTEM            990
    SYSAUX            508
    ________         1573