La redéfinition d'une table

Le package « DBMS_REDFINITION » permet la réorganisation d’une table en ligne pendant qu'elle reste accessible aux utilisateurs. Vous pouvez également changer la structure de stockage, par exemple partitionner une table pendant qu'elle est utilisée, ce qui garantit une haute disponibilité pour les applications.

en savoir

La redéfinition d’une table est exécutée au cours des étapes suivantes :

- Le premier pas consiste à vérifier que la table peut être redéfinie en ligne. La procédure « CAN_REDEF_TABLE » permet de le contrôler. Attention, il s’agit d’une procédure, et il n’y a alors pas de valeur de retour ; si la redéfinition n’est pas possible, Oracle lance une exception avec le message d'erreur indiquant la raison.

- La suite consiste à créer une table intérimaire avec les attributs souhaités pour la table à redéfinir. Il est préférable de n’inclure aucune contrainte d’aucune sorte, ni aucun index ou trigger existant déjà sur la table initiale. Il est possible par la suite de les copier en état à partir de la table initiale. Vous pouvez toutefois ajouter toute les contraintes, index ou triggers nouveaux dont vous avez besoin.

- S’il s’agit d’une table de grande taille, préparer la session pour accepter les traitements en parallèle afin d’accélérer les traitements des étapes suivantes à l’aide des commandes suivantes :

ALTER SESSION FORCE PARALLEL DML PARALLEL;

ALTER SESSION FORCE PARALLEL QUERY PARALLEL;

- On débute la redéfinition en ligne de la table à l’aide de la procédure « START_REDEF_TABLE ». Cette procédure accepte principalement trois arguments : le nom du propriétaire de la table « UNAME », le nom de la table d’origine « ORIG_TABLE » et la table intérimaire « INT_TABLE ». À la fin de la procédure « START_REDEF_TABLE », la table intérimaire est alimentée avec tous les enregistrements de la table d’origine ; ainsi le temps d’exécution de la procédure est proportionnel à le taille de la table.

- Une fois que la table intérimaire est remplie, on peut copier tous les objets dépendants pour la table intérimaire à l’aide de la procédure « COPY_TABLE_DEPENDENTS ». Attention, cette procédure a un argument en sortie et il faut automatiquement le récupérer à l’aide d’une variable, sinon la procédure n’aboutit pas. Les arguments de la procédure en plus de ceux de la procédure de démarrage de la redéfinition sont :

- Pendant la copie des dépendants, les utilisateurs peuvent continuer de modifier la table et plus le temps des créations des index est long. La procédure « SYNC_INTERIM_TABLE » vous permet d’appliquer les modifications et alléger le traitement final. Dans cette étape vous pouvez créez d’autres index et également calculer les statistiques de la table intérimaire.

- La dernière étape est exécutée à l’aide la procédure « FINISH_REDEF_TABLE » qui renomme la table d’origine avec le nom de la table intérimaire et la table intérimaire avec le nom de la table d’origine. Ainsi les deux tables ont permuté, chacune prenant le nom de l’autre, aucune des tables n’étant effacée.

STAG@agate>desc commandes

Nom                               NULL ?   Type

 --------------------------------- -------- -----------

 NO_COMMANDE                       NOT NULL NUMBER(6)

 CODE_CLIENT                       NOT NULL CHAR(5)

 NO_EMPLOYE                        NOT NULL NUMBER(6)

 DATE_COMMANDE                     NOT NULL DATE

 DATE_ENVOI                                 DATE

 PORT                                       NUMBER(8,2)

 LIVREE                            NOT NULL NUMBER(1)

 ACQUITEE                          NOT NULL NUMBER(1)

 ANNULEE                           NOT NULL NUMBER(1)

 

STAG@agate>exec dbms_redefinition.can_redef_table(user,'commandes');

 

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

 

STAG@agate>create table commandes_int  (

  2     no_commande   number(6)          ,

  3     code_client   char(5)            ,

  4     no_employe    number(6)          ,

  5     date_commande date               ,

  6     date_envoi    date,

  7     port          number(8,2),

  8     livree        number(1)          ,

  9     acquitee      number(1)          ,

 10     annulee       number(1)          )

 11  partition by range (date_commande)

 12  interval ( numtoyminterval( 1, 'year'))

 13  store in (dtp_p01,dtp_p02,dtp_p03,dtp_p04,dtp_p05,

 14            dtp_p06,dtp_p07,dtp_p08,dtp_p09,dtp_p10)

 15  ( partition ind_1980 values less than

 16              (to_date('01/01/1981','dd/mm/yyyy'))

 17                     tablespace dtp_p01);

 

Table créée.

 

STAG@agate>alter session force parallel dml parallel 8;

 

Session modifiée.

 

STAG@agate>alter session force parallel query parallel 8;

 

Session modifiée.

 

STAG@agate>exec dbms_redefinition.start_redef_table(-

> user,'commandes','commandes_int');

 

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

Pour la redéfinition de la table, Oracle crée un journal de vue matérialisée « MLOG$_COMMANDES » pour la gestion des mises à jour de la table « COMMANDES ». C’est ce journal de vue matérialisée que vous devez interroger pour savoir s’il faut synchroniser la table intérimaire.

STAG@agate>update commandes set port = port*1.1

  2  where date_commande > '01/12/2009';

 

1155 ligne(s) mise(s) à jour.

 

STAG@agate>commit;

 

Validation effectuée.

 

STAG@agate> select dmltype$$, count(*) from mlog$_commandes

  2   group by dmltype$$;

 

D   COUNT(*)

- ----------

U       1155

 

STAG@agate>delete details_commandes

  2  where no_commande in (select no_commande from commandes

  3                         where date_commande > '20/12/2009');

 

15695 ligne(s) supprimée(s).

 

STAG@agate>delete commandes where date_commande > '20/12/2009';

 

429 ligne(s) supprimée(s).

 

STAG@agate>commit;

 

Validation effectuée.

 

STAG@agate> select dmltype$$, count(*) from mlog$_commandes

  2   group by dmltype$$;

 

D   COUNT(*)

- ----------

U       1155

D        429

 

STAG@agate>variable erreurs number;

STAG@agate>exec dbms_redefinition.copy_table_dependents(-

> user,'commandes','commandes_int',-

> ignore_errors=>true,num_errors=>:erreurs);

 

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

 

STAG@agate>print erreurs

 

   ERREURS

----------

         0

 

STAG@agate>select dmltype$$, count(*) from mlog$_commandes

  2  group by dmltype$$;

 

aucune ligne sélectionnée

 

STAG@agate>exec dbms_redefinition.finish_redef_table(-

> user,'commandes','commandes_int');

 

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

 

STAG@agate>select partition_name, tablespace_name, high_value

  2  from user_tab_partitions

  3  where table_name = 'COMMANDES'

  4  order by partition_position;

 

PARTITION_ TABLESPACE HIGH_VALUE

---------- ---------- -----------------------------------

IND_1980   DTP_P01    TO_DATE(' 1981-01-01 00:00:00', 'SY

                      YYY-MM-DD HH24:MI:SS', 'NLS_CALENDA

                      R=GREGORIA

...

Pour vérifier que le processus de redéfinition s'est correctement déroulé, nous interrogeons le dictionnaire de données, et la nouvelle table « COMMANDES » est une table partitionnée. Attention, la table intérimaire « COMMANDES_INT » existe toujours.

0031Si la table que vous voulez définir contient des colonnes virtuelles ou des colonnes invisibles vous devez utiliser l’argument « COL_MAPPING » de la procédure « START_REDEF_TABLE ». L’argument contient une chaine de caractères, utilisée dans le select de la table à redéfinir qui permet de retrouver le nom, le nombre et les valeurs des colonnes nécessaires pour alimenter la table intérimaire.

STAG01@topaze>desc commandes

 Nom                                NULL ?   Type

 ---------------------------------- -------- -----------------------

 NO_COMMANDE                        NOT NULL NUMBER(6)

 CODE_CLIENT                        NOT NULL CHAR(5)

 NO_EMPLOYE                         NOT NULL NUMBER(6)

 DATE_COMMANDE                      NOT NULL DATE

 DATE_ENVOI                                  DATE

 PORT                                        NUMBER(8,2)

 LIVREE                             NOT NULL NUMBER(1)

 ACQUITEE                           NOT NULL NUMBER(1)

 ANNULEE                            NOT NULL NUMBER(1)

 ANNEE                                       NUMBER(4)

 TRIMESTRE                                   NUMBER(1)

 MOIS                                        NUMBER(2)

 

STAG01@topaze>CREATE TABLE COMMANDES_SAV  (

...

 11     ANNEE                NUMBER(4) AS

 12            (EXTRACT(YEAR FROM DATE_COMMANDE)),

 13     TRIMESTRE            NUMBER(1) AS

 14            (TO_NUMBER(TO_CHAR(DATE_COMMANDE,'Q'))),

 15     MOIS                 NUMBER(2) AS

 16            (EXTRACT(MONTH FROM DATE_COMMANDE))

 17  )TABLESPACE DTB_TRAN;

 

Table créée.

 

STAG01@topaze>DECLARE

     L_ERR NUMBER(3);

  BEGIN

     DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'COMMANDES');

     DBMS_REDEFINITION.START_REDEF_TABLE

         ( USER,'COMMANDES','COMMANDES_SAV',

            COL_MAPPING=>

             'NO_COMMANDE,CODE_CLIENT,NO_EMPLOYE,DATE_COMMANDE,'||

             'DATE_ENVOI,PORT,LIVREE,ACQUITEE,ANNULEE');

...

STAG01@topaze>set colinvisible on

STAG01@topaze>desc EMPLOYES

 Nom                                NULL ?   Type

 ---------------------------------- -------- -------------

 NO_EMPLOYE                         NOT NULL NUMBER(6)

...

 SALAIRE (INVISIBLE)                NOT NULL NUMBER(8,2)

 COMMISSION (INVISIBLE)                      NUMBER(8,2)

....

  5     DBMS_REDEFINITION.START_REDEF_TABLE

  6         ( USER,'EMPLOYES','EMPLOYES_SAV', COL_MAPPING=>

  7     'NO_EMPLOYE,REND_COMPTE,NOM,PRENOM,FONCTION,TITRE,'||

  8     'DATE_NAISSANCE,DATE_EMBAUCHE,PAYS,REGION,SALAIRE,COMMISSION');

...