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.
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.
Si 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
2 L_ERR NUMBER(3);
3 BEGIN
4 DBMS_REDEFINITION.CAN_REDEF_TABLE(USER,'COMMANDES');
5 DBMS_REDEFINITION.START_REDEF_TABLE
6 ( USER,'COMMANDES','COMMANDES_SAV',
7 COL_MAPPING=>
8 'NO_COMMANDE,CODE_CLIENT,NO_EMPLOYE,DATE_COMMANDE,'||
9 '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');
...