Importer des données avec Data Pump sans les exporter

Combien de fois vous a-t-on demandé d'exporter le schéma X de la base de données A et de le réimporter dans le schéma Y de B ? Bien sur parfois, X=Y ; d'autres fois A=B ; Parfois il ne s'agit pas d'un schéma mais juste d'une table. Souvent il faut changer
le nom des tablespaces et quand ça devient intéressant, il faut partitionner la table.

Avec la 9i, il fallait : exporter les données, créer la table dans votre base de données cible en changeant tablespace ou autre critère manuellement, les réimporter puis reconstruire les index... Data Pump offre quant à lui la possibilité de faire, en une opération l'import des structures et données et, au moins pour le tablespace, sans recréer la table manuellement au préalable. Merci au mot clé NETWORK_LINK qui permet de se servir d'une base de données (Et non pas d'un fichier d'export) comme source de l'import...

Bref, vous avez probablement oublié comment on faisait un export/import avec les commandes exp et imp ! Si ce n'est pas le cas, ou que vous vous demandez encore comment on peut faire avec Data Pump, voici 3 exemples qui illustrent la simplicité de Data Pump et, dans la plupart des cas, comment arriver à vos fins en une seule commande ; vous pourrez décliner ces commandes à votre aise pour répondre à vos préoccupations du moment (Vous trouverez toutes les informations dont vous aurez besoin dans Oracle Database Utilities - Data Pump Import). Enfin, même en 11.1, il existe encore quelques limites qu'on aimerait voir lever ; nous y reviendrons en fin de post.

Exemple 1 : Créer une copie d'un utilisateur dans la même base de données.

Et ça marche aussi, si la base de données cible est différente de la base de données source ;-) !
Supposons que vous vouliez copier le schéma de l'utilisateur SCOTT dans un nouvel utilisateur MYSCOTT dans la même base de données, il suffit :

• De se connecter à la base de données cible et de créer un database link vers le schéma SCOTT de votre base source (i.e. vers la même base de données si vous voulez copier le schéma dans la même base de données)

sqlplus / as sysdba

SQL> create database link scott_redx
   connect to scott identified by tiger
     using 'redx';

SQL> exit;

Une fois le lien de base de données créé, vous pouvez lancer l'import data pump. Celui-ci créera tous les objects, y compris le nouvel utilisateur :

$ impdp \"/ as sysdba\" schemas=scott \
    remap_schema=scott:myscott    \
    nologfile=y network_link=scott_redx

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 17:39:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" schemas=scott remap_schema=scott:myscott nologfile=y network_link=scott_redx
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "MYSCOTT"."DEPT"                                 4 rows
. . imported "MYSCOTT"."EMP"                                 14 rows
. . imported "MYSCOTT"."SALGRADE"                             5 rows
. . imported "MYSCOTT"."BONUS"                                0 rows
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/POST_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_SCHEMA_01" successfully completed at 17:39:28

Une fois l'opération terminée, vous pouvez supprimer le dababase link :

sqlplus / as sysdba
SQL> drop database link scott_redx;

SQL> exit;

Exemple 2 : Copier une table dans un nouveau tablespace

On supposera cette fois que vous travaillez sur une base de test et que vous vouliez copier la table SCOTT.DEPT de votre base de données de production. Pour le test, le tablespace de production n'existe pas... Comme dans l'exemple précédent, vous allez créer un database link depuis votre base de données de test vers votre base de données de production ; pour cela, donnez à SCOTT la possibilité de créer des database link, connectez-vous à votre base de données de test et créez le lien vers la production :

sqlplus / as sysdba

SQL> grant create database link to scott;

SQL> connect scott
Password :

SQL> create database link scott_redx
     connect to scott identified by tiger  using 'redx';

SQL> exit;

Une fois le database link créé, il vous suffit de lancer impdp comme ci-dessous sur la base de données de test :

$ impdp scott tables=dept           \
    remap_tablespace=users:demo   \
    nologfile=y network_link=scott_redx

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 19:39:15

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** tables=scott.dept remap_tablespace=users:demo nologfile=y network_link=scott_redx
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "SCOTT"."DEPT"                                 4 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 19:39:26

L'import terminé, vous pouvez supprimer le dababase link :

sqlplus scott
Password:

SQL> drop database link scott_redx;

SQL> exit;

Exemple 3 : Une limite de l'import via un database link

Il faudrait sans doute dire que l'export/import via des fichiers permet en plus de filtrer les données... le mot clé QUERY permet en effet, d'ajouter une clause WHERE sur les objets que vous exportez ou importez. Voici un exemple d'export/import où on ajoute un filtre lors de l'import. Commencez par donner accès à un directory à l'utilisateur SCOTT et à donner le privilège IMP_FULL_DATABASE à MYSCOTT pour qu'il puisse faire l'import d'un autre utilisateur :

SQL> grant read, write
on directory DATA_PUMP_DIR to scott;

SQL> exit

Vous pouvez ensuite lancer l'export Data Pump comme ci-dessous :

$ expdp scott tables=dept dumpfile=DATA_PUMP_DIR:scott_emp.dmpdp   \
                     nologfile=y;

Export: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 19:58:09

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Produc                                  tion
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** tables=dept dumpfile=DAT                                  A_PUMP_DIR:scott/********_emp.dmpdp nologfile=y
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.937 KB       4 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/REDX/dpdump/scott_emp.dmpdp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 19:58:34

Et voici comment faire l'import dans le schéma MYSCOTT en ajoutant une clause WHERE sur la table DEPT :

$ impdp myscott dumpfile=DATA_PUMP_DIR:scott_emp.dmpdp tables=dept \
query='dept:"where deptno=10"' remap_schema=scott:myscott nologfile=y

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 20:03:56

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "MYSCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MYSCOTT"."SYS_IMPORT_TABLE_01":  myscott/******** dumpfile=DATA_PUMP_DIR:scott_emp.dmpdp tables=dept query=dept:"where deptno=10" remap_schema=scott:myscott/******** nologfile=y
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MYSCOTT"."DEPT"                            5.937 KB       1 out of 4 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "MYSCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 20:04:11

Exemple 3 bis : Utiliser DBMS_RLS pour obtenir la même fonctionnalité via l'import avec un Database Link.

Si vous voulez faire un import d'un sous-ensemble de la table SCOTT.DEPT dans MYSCOTT.DEPT en utilisant un database link, vous pouvez toujours faire croire à Data Pump que la table contient moins de lignes qu'en réalité... DBMS_RLS (aka Virtual Private Database, VPD) peut simplement faire ce travail pour vous. Dans ce cas, il faudra que vous vous connectiez à la base de données source non pas avec SCOTT mais avec un utilisateur dédié (dans l'exemple MYSCOTT). Pour permettre de vous connecter à la base source, créez un database Link comme ci-dessous sur la base cible :

sqlplus myscott

SQL> create database link myscott_redx
connect to myscott identified by tiger using 'redx';

SQL> exit

Puis donnez le role exp_full_database a MYSCOTT sur la base source :

sqlplus sys@redx

SQL> grant exp_full_database to myscott;

SQL> exit;

L'étape suivant consiste à ajouter la fonction de la "Policy" VPD sur la base source et de l'activer :

sqlplus scott@redx

SQL> create or replace function filter_myscott(object_schema    VARCHAR2,
                                       object_name      VARCHAR2)
return varchar2 as
retval varchar2(200);
begin
if user='MYSCOTT' then retval:='deptno=10'; end if;
return retval;
end;
/

SQL> BEGIN
dbms_rls.add_policy(object_schema => 'scott',
 object_name => 'dept',
 policy_name => 'datapump export',
 policy_function => 'filter_myscott');
END;
/

Vous pouvez vérifier que la table DEPT ne se;ble alors plus n'avoir qu'une ligne pour MYSCOTT :

SQL> connect myscott@redx

SQL> select * from scott.dept;

DE DNAME          LOC
-- -------------- -------------
10 ACCOUNTING     NEW YORK

Il ne vous reste qu'à lancer l'import par le database link et les colonnes seront filtrées selon les conditions de votre VPD :

$ impdp myscott tables=scott.dept \
 remap_schema=scott:myscott     \      
 remap_tablespace=users:demo    \
 nologfile=y network_link=myscott_redx

Import: Release 11.1.0.6.0 - Production on Sunday, 27 January, 2008 21:42:29

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "MYSCOTT"."SYS_IMPORT_TABLE_01":  myscott/******** tables=scott.dept remap_schema=scott:myscott/******** remap_tablespace=users:demo nologfile=y network_link=myscott/********_redx
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . imported "MYSCOTT"."DEPT"                                 1 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/RLS_POLICY
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "MYSCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 21:42:44

Voila, plus que 2 regrets :

• Data Pump ne permet pas de faire des chargements avec des validations intermédiaires (cf le mot clé COMMIT de la commande "imp")
• Le mot clé remap_table ne fonctionne qu'avec les tablespaces transportables et ça serait vraiment utile de pouvoir renommer la table avant de l'importer pour éviter un conflit avec une table existante. Aujourd'hui vous ne pouvez faire qu'un ALTER TABLE RENAME après l'import dans le cas du Database Link.
  

Bon, cela dit... comme vous pouvez le constater, et sans même parler des performances, Data Pump a surement changer beacoup de chose dans votre vie de DBA...

Installer Oracle RAC 10g en mode silencieux sous Linux

Si vous avez lu le post précédent : "Installer Oracle Clusterware 10g en mode silencieux sous Linux", il est maintenant temps d'installer le logiciel de base de données avec l'option "Real Application Clusters (RAC)". Cette étape est vraiment simple, elle consiste à installer la version 10.2.0.1 sur laquelle vous appliquerez ensuite les patchs que vous voulez. Si $DISTRIB indique le répertoire dans lequel est située la distribution 10.2.0.1, voici la commande que vous devez lancer :

cd $DISTRIB
./runInstaller -silent                                 \ 
      -responseFile $DISTRIB/response/enterprise.rsp   \ 
       ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 \ 
       ORACLE_HOME_NAME=OraDBHome1                     \ 
       CLUSTER_NODES={"node1","node2"}                 \ 
       n_configurationOption=3

Remarquez que n_configurationOption=3 ne fait qu'installer le logiciel alors que d'autres options peuvent démarrer ASM ou créer une base de données. Enfin, il sera possible de lancer ces opérations ultérieurement avec DBCA, une fois les patchs appliqués.

Comme pour le clusterware, inutile de lancer les scripts root.sh avant d'appliquer le dernier Patch Set. Au fait l'opération est encore plus simple. On supposera que $DISTRIB indique maintenant le chemin de la distribution du dernier Patch Set. Voici comment faire :

cd $DISTRIBS
./runInstaller -silent                                 \ 
      -responseFile $DISTRIB/response/patchset.rsp     \ 
       ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1 \ 
       ORACLE_HOME_NAME=OraDBHome1                     \ 
       CLUSTER_NODES={"node1","node2"}

Et voilà, il ne reste plus qu'à lancer les scripts root.sh sur tous les serveurs, d'appliquer la dernière version d'OPatch, le dernier CPU et les patchs unitaires que vous voulez (Les procédures dépendent généralement des patchs et s'appuient presque toujours sur OPatch) . Ensuite, vous pouvez créer les listeners après avoir positionné les variables d'environnement pour Oracle :

export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
export PATH=$ORACLE_HOME/bin:$PATH
netca /silent \ 
  /responsefile $ORACLE_HOME/network/install/netca_typ.rsp \ 
  /nodeinfo node1,node2

Pour vérifier qu'il fonctionnent et sont enregistrés dans le clusterware, lancez

srvctl status nodeapps -n node1
srvctl status nodeapps -n node2

Et voilà, encore 10 minutes et DBCA aura créé la base de données dont vous avez besoin... Je vous laisse écrire la commande, moi ma base est déjà créée ! Un peu d'huile de coude et j'ai construit un environnement de test en RAC à partir de la production en moins de 2 heures. J'avoue qu'il y a un truc : l'équipe de stockage m'a fourni un clone du dernier snapshot de la base de données de production. Merci NetApp : pas besoin de copier les fichiers entre les 2 environnements ! Il ne restera plus qu'à étendre le cluster (plus tard) jusqu'à 8 ou 10 serveurs. J'ai déjà les commandes : j'en garde toujours sous le coude ;-).

Installer Oracle Clusterware 10g en mode silencieux sous Linux

Allons droit à l'objectif, voici comment installer Oracle Clusterware 10g en mode silencieux (i.e. sans DISPLAY) sous Linux en supposant que :

• $DISTRIB est le chemin de la distribution du logiciel Oracle (Vous commencerez par installer 10.2.0.1 puis vous installerez le dernier Patch Set ensuite)
  
• Tous les pre-requis sont vérifiés (i.e. Vous avez passes : runcluvfy.sh stage -pre crsinst -n node1,node2 -verbose)

cd $DISTRIB
./runInstaller -silent                                                            \
 -responseFile $DISTRIB/response/crs.rsp                                          \
 ORACLE_HOME="/u01/crs"                                                           \
 ORACLE_HOME_NAME="OraCRS102"                                                     \
 s_clustername="mycluster"                                                        \
 sl_tableList={"node1:node1-priv:node1-vip:N:Y","node2:node2-priv:node2-vip:N:Y"} \
 ret_PrivIntrList={"eth0:10.0.0.0:1","eth1:192.168.0.0:2","eth2:192.168.0.128:3"} \
 n_storageTypeOCR=1                                                               \
 s_ocrpartitionlocation="/dev/sdb1"                                               \
 s_ocrMirrorLocation="/dev/sdc1"                                                  \
 n_storageTypeVDSK=1                                                              \
 s_votingdisklocation="/dev/sdb2"                                                 \
 s_OcrVdskMirror1RetVal="/dev/sdc2"                                               \
 s_VdskMirror2RetVal="/dev/sdd2"

Quelques remarques à propos de cette commande :

• Ici, vous supposez implicitement que le compte d'installation est oracle, le groupe oinstall et le groupe dba est dba. verifiez le contenue du fichier de réponse pour des options plus évoluées...
  
• Il ne faut pas modifier le fichier de réponse dans ce cas puisque vous passez l'ensemble des paramètres dans la ligne de commande
• Les paramètres ORACLE_HOME, ORACLE_HOME_NAME et s_clustername ne méritent pas de commentaires particuliers
• s_tableList contient la liste des serveurs sous la forme "nom-public:nom-interconnect:non-vip:N:Y". Il faut que la commande nslookup permettent de résoudre ce nom et récupère l'IP correspondante (Bien sur, il faut aussi que les adressent soient correctement installee et les VIP libres puisque le clusterware les installera.
• ret_PrivIntrList contient la liste des interfaces et sous-réseaux correspondant (BITAND(IP, NETMASK)) des serveurs ainsi que quoi en faire :
• 1 : indique que c'est le sous-réseau public
• 2 indique que c'est le sous-réseau d'interconnect
• 3 indique que c'est un sous-réseau non utilise par le cluster
• n_StorageTypeOCR=1 indique qu'il y a 2 OCR et s_ocrpartitionlocation et s_ocrmirrorlocation indiquent leur emplacement (NFS, Volume logique, Raw devices , OCFS...)
• n_StorageTypeVDSK=1 indique qu'il y a 3 voting disk et s_votingdisklocation, s_OcrVdskMirror1RetVal et s_VdskMirror2RetVal indiquent leur emplacement (NFS, Volume logique, Raw devices , OCFS...). Remarquez que le nom du 2e emplacement semble un peu bizarre mais qu'il fonctionne bien (Je n'ai pas essaye avec s_VdskMirror1RetVal ?)

Cette commande installe le clusterware en quelque minutes. Vous pouvez lancer orainstRoot.sh si vous devez créer l'inventory mais NE LANCEZ PAS root.sh tout de suite; Commencez par changez la variable $DISTRIB avec le chemin du Patch Set et installez la dernière version :

cd $DISTRIB
./runInstaller -silent                           \
   -responseFile $DISTRIB/response/patchset.rsp  \   
    ORACLE_HOME="/u01/crs"                       \
    ORACLE_HOME_NAME="OraCRS102"

Maintenant vous pouvez lancer les scripts root.sh sur tous les serveurs. Si vous utilisez une classe d'adresse réservée pour le réseau public, comme 10.x.x.x, "vipca" va échouer (11g corrige cette erreur ;-( ). Je n'ai réussi à le relancer en mode silent dans ce cas! (Peut-être que vous trouverez ?). Ce n'est pas grave ! Vous pouvez configurer la VIP manuellement avec srvctl...

Si c'est votre cas, vérifiez que les adresses publiques et privées sont enregistrées dans le clusterware avec la commande oifcfg getif et le cas échéant, créez les avec oifcfg setif. Une fois les réseaux correctement enregistrés, vous pouvez créer VIP, GSD et ONS avec srvctl comme ci-dessous :

$ORA_CRS_HOME/bin/srvctl add nodeapps -n node1 \
       -o /u01/crs                             \
       -A node1-vip/255.255.255.0/eth0

$ORA_CRS_HOME/bin/srvctl add nodeapps -n node2 \
       -o /u01/crs                             \
       -A node2-vip/255.255.255.0/eth0

L'option -A suivie de l'alias de la VIP, du netmask et de l'interface réseau séparés par un "/" permettent d'enregistrer la VIP dans l'OCR. Il est a noter que vous devez vous attendre à l'erreur suivant puisque vous ne créerez le listener qu'une fois le logiciel rdbms installé :

CRS-0210: Could not find resource ora.node1.LISTENER_NODE1.lsnr

Vous pouvez maintenant vérifiez le status des VIP, GSD et ONS ; Une façon simple est d'utiliser encore srvctl comme ci-dessous :

$ORA_CRS_HOME/bin/srvctl status nodeapps -n node1
$ORA_CRS_HOME/bin/srvctl status nodeapps -n node2

Voila, c'est fini ! Vous avez installé le clusterware en 15 minutes, il vous en faudra 10 de plus pour installer le logiciel de base de données. Bien sur, sauf si vos serveurs sont des brouettes... Allez, le prochain post sera consacré aux 10 prochaines minutes.

Le rachat de MySQL AB vu d'un paranoïaque

Vous tentez sûrement de lire les Press Release et les blogs de la planète entière à propos des 2 rachats de MySQL AB et de BEA. Vous voulez comprendre quel sont les desseins secrets derrière ces 2 mouvements trop concomitants pour ne pas paraître suspects. Alors, voici une théorie, faites en ce que vous voulez : "C'est un accord secret entre Sun et Oracle !".

Commençons par ce qui parait communément admis. Si vous lisez comme moi, la "communauté" MySQL semble accueillir la nouvelle avec d'abord une espèce de choc. Ce choc se transforme en espoir qui grandit au fil des heures. Je ne vous ferai pas la théorie de l'Iceberg, mais ça y ressemble fortement. Un peu comme si Sun pouvait devenir la solution de tous les problèmes : le cycle de développement ou de sérieux problèmes d'architecture fondamentaux dont on s'accorde à considérer qu'il va falloir du lourd pour s'en sortir ; Voici un post qui illustre assez bien ce propos. Première mauvaise nouvelle, même si tout le monde semble en phase et que l'open-source pourrait faciliter l'anticipation du rachat, Sun pouvant s'impliquer des aujourd'hui dans ce projet "open" à 200%, ça prendra des mois avant que "ça roule" comme tout le monde l'espère, si jamais ça arrive. En plus, Sun ne semble pas jouir d'un savoir faire exceptionnel dans le domaine, malgré OpenSolaris ou OpenJDK. Et même, si vous êtes un de ces oiseaux de mauvais augure, vous direz que beaucoup d'espoirs pourront être déçus. Où en est SeeBeyond aujourd'hui chez Sun ? Vous pensez, ce n'était que 387 millions de dollars US. Rappelez-vous où était Sun au moment de ce rachat.

D'un autre côté où en est Solaris x86 ? Ça fait plus de 5 mois que Oracle 11g est sorti et il est maintenant disponible sur pratiquement toutes les plate-formes sauf Solaris x86. Si vous avez vécu il y a quelques années, vous savez ce que Oracle et Solaris représentent et c'est sans doute l'amour de ces 2 compagnies et de les imaginer se déchirer, qui me fait parler mais peut-on imaginer Solaris se déployer comme Linux le fait aujourd'hui sans Oracle ?

Et Glassfish ? Et SunOne ? Il y a quelques années Oracle et Sun battaient au coude à coude sur le marché du serveur d'applications (J2EE à l'époque) pour la place de 3e. Racheter MySQL va-t-il lancer une nouvelle stack "Sun" avec Solaris, Java et MySQL en Open Source ? Admettons un instant que ce soit le cas, que deviennent PHP, Linux, Redhat ? Pour quelle place ?

Tout ca n'est rien comparé à la question qui suit : Que vise Sun ? Jusqu'ici, je vois surtout Sun dans le "Mid-Range" des grandes entreprises, sur le "Mainframe" Unix ou dans la gestion des identités. En quoi acheter MySQL peut-il aider dans ces domaines ? Les acteurs qui pèsent dans le monde des applications ne fonctionnent pas avec MySQL et pourquoi, SAP ou d'autres suivraient Sun sur ce terrain maintenant ? En outre Oracle qui rachète BEA, c'est le dernier gros morceau logiciel d'infrastructure des entreprises qui n'appartient pas à IBM, Microsoft, SAP ou Oracle. Si on met de cote les solutions de supervision ou les anti-virus, qu'est-ce qu'il reste ? Tibco, Teradata, Compuware, rien d'énorme ?

Alors, est-ce que Internet vaut 1 milliard de dollars ? Sans doute bien plus ! Mais regardez Google ou Yahoo!, Sun serait donc le seul à croire que ce qui compte ce sont encore les briques techniques de base pour Internet alors que les autres se concentrent sur les interfaces graphiques du futur ou sur la sémantique web.

Enfin ce serait encore un moins grand mystère, que les CEO de Sun et d'Oracle qui ne se parleraient plus depuis des mois ou simplement pour échanger quelques banalités. Qu'est-ce que vous en pensez ?

Non ! rien ne colle et, ne nous y trompons pas, ces mecs sont des cracks ; ils savent ce qu'ils font et pourquoi ils le font, c'est jusque que je ne comprends pas... Sauf si "C'est un accord secret entre Oracle et Sun", l'un pour voir Oracle sur Solaris x86 apparaître en même temps que sur Linux et l'autre pour s'enlever l'épine du pieds que Innobase Oy ne lui a pas arrachée. Ça a l'air paranoïaque dit comme ça ! 1 milliard de dollars pour mettre la pression à Oracle ? Sauf si au passage, vous voulez entrer dans l'Internet 2.0. Il faut bien le reconnaître, si Microsoft fait pale figure par rapport à Google, Amazon ou Yahoo!, il fait figure. Alors que Sun et Oracle...

Enfin, tout ça pour envoyer un message personnel aux DBA MySQL, je souhaite pour 2008 toute la réussite possible à Sun et MySQL mais n'oubliez pas, pour votre part, que "les promesses n'engagent que ceux qui les écoutent". Pour l'instant qu'est ce que vous avez vu d'autre ?

Hasards superflus

Ma semaine avec Oracle est visiblement placée sous le signe du hasard.

• Premier hasard ; après qu'un backup ait f... Je rencontre une erreur qui fait sourire : ORA-01234
• Deuxième hasard, en voulant rechercher "oracle for loop exit" sur Google, je tape en fait "oracle for loop exist" et mon dernier post en anglais apparaît en position 1 sur 648 000.
  
• Autre hasard, je tombe sur un bug en 9.2.0.8 corrigé en 11.1.0.7 : J'ai enfin trouvé une bonne motivation pour migrer nos 1.8 To de 9i en 11g (dès que le premier Patch Set est disponible, dommage !)

Bref, plus rien ne devrait m'arriver en 2008, quoique...

2 exemples "avancés" de vues matérialisées avec une clause GROUP BY

"avancés" va peut-être vous faire rire, d'où les guillemets ! Quoiqu'il en soit, voici 2 exemples qui illustrent plusieurs possibilités des vues matérialisées et notamment :

• La possibilité de rafraîchir de manière rapide (FAST REFRESH), en appliquant uniquement les modifications, une vue matérialisée qui contient une jointure et/ou une clause GROUP BY
  
• La possibilité d'imbriquer (ou plutôt mettre en cascade) des vues matérialisées tout en conservant une mise à jour rapide
• L'utilisation de utlxmv.sql et DBMS_MVIEW.EXPLAIN_MVIEW pour comprendre pourquoi une vue matérialisée ne peut pas être rafraîchie avec la méthode "REFRESH FAST"
  
• L'utilisation de GROUPING SET dans une vue matérialisée pour avoir plusieurs niveaux d'agrégats dans la même vue matérialisée
  

Voilà pour ce post. Notez bien que j'ai testé ce qui suit sur 2 bases de données : Oracle 11.1.0.6 Enterprise Edition sur Linux et Oracle 10.2.0.2 Standard Edition. Moi qui peste toujours contre les limitations de la version SE, ce pourrait être l'exception qui confirme la règles ? Bien sur, je ne parle pas de "Query Rewrite ;-)"

Schéma d'exemple

La première difficulté de ce genre d'exercices c'est d'avoir des tables d'exemple représentatives. Construire quelque chose qui ressemble à un vrai problème prend du temps. Les schémas d'exemple d'Oracle, et en particulier SH (Sales History), dans le CD du même nom sont donc, le plus souvent, des candidats parfaits. Enfin, comme j'aime bien partir de 0 et que seul les aspects techniques sont vraiment intéressants, je vous propose de commencer par les scripts ci-dessous, pour créer quelques tables utiles pour les exemples qui suivent. De cette manière vous adapterez la taille de votre base de données selon vos besoins :

create table d1 (
id     number,
lib    varchar2(15),
attr1  number,
level1 number,
level2 number);

create table d2 (
id     varchar2(10),
lib    varchar2(10),
level1 varchar2(10));

create table d3 (
id     date,
lib    varchar2(80),
level1 date);

create table ftable (
d1   number,
d2   varchar2(10),
d3   date,
val1 number,
val2 number);

declare
v_d1   number := &d1_num_distinct_keys;
v_d2   number := &d2_num_distinct_keys;
v_d3   number := &d3_num_distinct_keys;
v_rows number := &fact_num_rows;
s_date date   := trunc(sysdate,'DD');
begin
for i in 0.. v_d1-1 loop
 insert into d1(id, lib, attr1, level1, level2)
   values(i,
         'Num #'||to_char(i),
         mod(i,17),
         mod(i,140),
         mod(i,20));
end loop;
commit;
for j in 0..v_d2-1 loop
 insert into d2(id, lib, level1)
   values('#'||to_char(j),
          'Char #'||to_char(j),
          '$'||to_char(mod(j,31)));
end loop;
commit;
for k in 0..v_d3-1 loop
 insert into d3(id, lib, level1)
    values(s_date-k,
           to_char(s_date-k,'Month Day, Year'),
           trunc(s_date-k,'MM'));
end loop;
commit;
for l in 1..v_rows loop
  insert into ftable(d1, d2, d3, val1, val2)
    values (mod(l,v_d1),
            '#'||mod(l,v_d2),
            s_date - mod(l,v_d3),
            mod(l,113),
            trunc(100000/(mod(l,19)+1)));  
end loop;
commit;
end;
/

Enter value for d1_num_distinct_keys: 120
old   2:   v_d1   number := &d1_num_distinct_keys;
new   2:   v_d1   number := 120;
Enter value for d2_num_distinct_keys: 23
old   3:   v_d2   number := &d2_num_distinct_keys;
new   3:   v_d2   number := 23;
Enter value for d3_num_distinct_keys: 365
old   4:   v_d3   number := &d3_num_distinct_keys;
new   4:   v_d3   number := 365;
Enter value for fact_num_rows: 150000
old   5:   v_rows number := &fact_num_rows;
new   5:   v_rows number := 150000;

alter table d1
add constraint d1_pk
 primary key(id);

alter table d2
add constraint d2_pk
 primary key(id);

alter table d3
add constraint d3_pk
 primary key(id);

exec dbms_stats.gather_table_stats(user, 'D1');
exec dbms_stats.gather_table_stats(user, 'D2');
exec dbms_stats.gather_table_stats(user, 'D3');
exec dbms_stats.gather_table_stats(user, 'FTABLE');

set autotrace traceonly;

select d1.level2,
    d2.level1,
    d3.level1,
    trunc(sum(ftable.val1)/1000)
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;

Execution Plan
-------------------------------------------------------
Plan hash value: 99612550

-------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Cost |
-------------------------------------------------------
|   0 | SELECT STATEMENT      |        |  2990 |  742 |
|   1 |  HASH GROUP BY        |        |  2990 |  742 |
|*  2 |   HASH JOIN           |        |   125K|  151 |
|   3 |    TABLE ACCESS FULL  | D3     |   365 |    3 |
|*  4 |    HASH JOIN          |        |   125K|  147 |
|   5 |     TABLE ACCESS FULL | D1     |   120 |    3 |
|*  6 |     HASH JOIN         |        |   125K|  142 |
|   7 |      TABLE ACCESS FULL| D2     |    23 |    3 |
|   8 |      TABLE ACCESS FULL| FTABLE |   125K|  138 |
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("FTABLE"."D3"="D3"."ID")
4 - access("FTABLE"."D1"="D1"."ID")
6 - access("FTABLE"."D2"="D2"."ID")

Statistics
----------------------------------------------------------
       0  recursive calls
       0  db block gets
     521  consistent gets
       0  physical reads
       0  redo size
  139738  bytes sent via SQL*Net to client
    4798  bytes received via SQL*Net from client
     400  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
    5980  rows processed

set autotrace off

Exemple 1 : MVs avec GROUP BY, JOIN, REFRESH FAST et imbriquées
Disons pour débuter que l'on veuille transformer le SELECT du schéma d'exemple précédent en en vue matérialisée (MV). L'opération est assez directe, il suffit de lancer l'ordre ci-dessous

create materialized view mv1
build immediate refresh force as
select d1.level2 d1_level2,
      d2.level1 d2_level1,
      d3.level1 d3_level1,
      trunc(sum(ftable.val1)/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;

Materialized view created.

exec dbms_stats.gather_table_stats(user, 'MV1')

Faites juste attention de mettre des alias pour vos colonnes. Non pas que ce soit obligatoire mais pour simplifier l'utilisation de la vue. Votre première requête devient celle ci-dessous et vous l'évolution du plan ne vous surprendra pas :

set autotrace traceonly

select *
 from mv1;

Execution Plan
----------------------------------------------------
Plan hash value: 2827454174

----------------------------------------------------
| Id  | Operation            | Name | Rows  | Cost |
----------------------------------------------------
|   0 | SELECT STATEMENT     |      |  5980 |    8 |
|   1 |  MAT_VIEW ACCESS FULL| MV1  |  5980 |    8 |
----------------------------------------------------

Statistics
----------------------------------------------------
       0  recursive calls
       0  db block gets
     421  consistent gets
       0  physical reads
       0  redo size
  186181  bytes sent via SQL*Net to client
    4798  bytes received via SQL*Net from client
     400  SQL*Net roundtrips to/from client
       0  sorts (memory)
       0  sorts (disk)
    5980  rows processed

Exemple 1 - Evolution #1 : Rendre la vue "Fast Refreshable"

La première question qu'il est sans doute légitime de se poser (Quoique j'ai vu des cas ou un "Refresh" complet était plus efficace d'un "Refresh" incrémental), c'est : Est-ce que cette vue peut être mise à jour de manière incrémentale ? Si non, comment la rendre "REFRESH FAST". Pour répondre à cette première question, vous pouvez sans doute compter sur la chance comme ci-dessous :

drop materialized view mv1;

create materialized view mv1
build immediate refresh fast as
select d1.level2 d1_level2,
      d2.level1 d2_level1,
      d3.level1 d3_level1,
      trunc(sum(ftable.val1)/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;

from d1,d2,d3,ftable
    *
ERROR at line 7:
ORA-12015: cannot create a fast refresh materialized view from a complex query

Vous risquez de bloquer. Vous pouvez également utiliser DBMS_MVIEW.EXPLAIN_MVIEW comme ci-dessous...

D'abord, créez la table MV_CAPABILITIES_TABLE qui stockera les informations quant aux raisons qui empêchent une vue matérialisée d'être REFRESH FAST à l'aide du script utlxmv.sql comme ci-dessous :

@?/rdbms/admin/utlxmv

desc MV_CAPABILITIES_TABLE

Name                    Null?    Type
----------------------- -------- ----------------
STATEMENT_ID                     VARCHAR2(30)
MVOWNER                          VARCHAR2(30)
MVNAME                           VARCHAR2(30)
CAPABILITY_NAME                  VARCHAR2(30)
POSSIBLE                         CHAR(1)
RELATED_TEXT                     VARCHAR2(2000)
RELATED_NUM                      NUMBER
MSGNO                            NUMBER(38)
MSGTXT                           VARCHAR2(2000)
SEQ                              NUMBER

Ensuite, utilisez DBMS_MVIEW.EXPLAIN_MVIEW (RTFM Data Warehouse Guide et PL/SQL Packages and Types Reference) pour connaître les raisons qui empêchent votre vue matérialisée d'être REFRESH FAST. Voici un exemple d'utilisation avec la vue précédente :

var mv char(2000)

begin
:mv:='create materialized view mv1
build immediate refresh fast as
select d1.level2 d1_level2,
      d2.level1 d2_level1,
      d3.level1 d3_level1,
      trunc(sum(ftable.val1)/1000) metric1
from d1,d2,d3,ftable
where ftable.d1=d1.id
and ftable.d2=d2.id
and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1';
end;
/

truncate table MV_CAPABILITIES_TABLE;

exec DBMS_MVIEW.EXPLAIN_MVIEW (:mv)

Enfin, affichez le résultat, stocke dans la table créée précédemment :

col REL_TEXT format a15
col MSGTXT format a70

SELECT capability_name,
     possible,
     SUBSTR(related_text,1,15)
       AS rel_text,
     SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
where capability_name not like '%PCT%'
 and capability_name not like '%REWRITE%'
ORDER BY seq;

CAPABILITY_NAME               P REL_TEXT     MSGTXT
----------------------------- - ------------ ------------------------------------------------------------
REFRESH_COMPLETE              Y
REFRESH_FAST                  N
REFRESH_FAST_AFTER_INSERT     N              aggregate function nested within an expression
REFRESH_FAST_AFTER_INSERT     N SCOTT.FTABLE the detail table does not have a materialized view log
REFRESH_FAST_AFTER_ONETAB_DML N              see the reason why REFRESH_FAST_AFTER_INSERT is disabled
REFRESH_FAST_AFTER_ANY_DML    N              see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled

Le vrai travail commence alors, à force de ce qui suit et à force d'itérations, vous arriverez a construire une vue matérialisée REFRESH FAST qui réponde à vos besoins :

• Ajoutez des MV logs à vos tables de base
• Ne mettre aucune expression à l'extérieur des fonctions d'agrégation
  
• Si votre select contient SUM(expr), ajoutez COUNT(expr)
• Votre select doit contenir COUNT(*)

Et remarquez qu'il est possible de construire des vues materialisées REFRESH FAST avec des jointures et des agrégats. Pour plus de détails à propos des restrictions associées au mode REFRESH FAST, regardez les differentes sections "Restrictions on Fast Refresh du Chapitre 6 d'Oracle Data Warehousing Guide 11g".

create materialized view log on ftable
with sequence, rowid (d1,d2,d3,val1)
including new values;

create materialized view log on d1
with sequence, rowid (id,level2)
including new values;

create materialized view log on d2
with sequence, rowid (id,level1)
including new values;
create materialized view log on d3
with sequence, rowid (id,level1)
including new values;

begin
:mv:='create materialized view mv1
 build immediate refresh fast as
 select count(*) rowcounts,
        d1.level2 d1_level2,
        d2.level1 d2_level1,
        d3.level1 d3_level1,
        count(ftable.val1/1000) cmetric1,
        sum(ftable.val1/1000) metric1
   from d1,d2,d3,ftable
  where ftable.d1=d1.id
    and ftable.d2=d2.id
    and ftable.d3=d3.id
  group by d1.level2,d2.level1,d3.level1';
end;
/

truncate table MV_CAPABILITIES_TABLE;

exec DBMS_MVIEW.EXPLAIN_MVIEW (:mv)

col REL_TEXT format a15
col MSGTXT format a70

SELECT capability_name,
     possible,
     SUBSTR(related_text,1,15)
       AS rel_text,
     SUBSTR(msgtxt,1,60) AS msgtxt
FROM MV_CAPABILITIES_TABLE
where capability_name not like '%PCT%'
 and capability_name not like '%REWRITE%'
ORDER BY seq;

CAPABILITY_NAME               P REL_TEXT
----------------------------- - ---------
REFRESH_COMPLETE              Y
REFRESH_FAST                  Y
REFRESH_FAST_AFTER_INSERT     Y
REFRESH_FAST_AFTER_ONETAB_DML Y
REFRESH_FAST_AFTER_ANY_DML    Y

Vous direz que ce n'est pas tout à fait ce que vous vouliez ? Ajoutez simplement une vue qui fait les derniers calculs sur la MV et vous avez ce que vous voulez...

create materialized view mv1
build immediate
refresh fast as
select count(*) rowcounts,
       d1.level2 d1_level2,
       d2.level1 d2_level1,
       d3.level1 d3_level1,
       count(ftable.val1/1000) cmetric1,
       sum(ftable.val1/1000) metric1
 from d1,d2,d3,ftable
where ftable.d1=d1.id
  and ftable.d2=d2.id
  and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;

Materialized view created.

exec dbms_stats.gather_table_stats(user, 'MV1');

create view v1 as
select d1_level2,
       d2_level1,
       d3_level1,
       trunc(metric1) metric1
  from mv1;

View created.

Exemple 1 - Evolution #2 : Comprendre ce que fait le mode "Fast Refreshable"

La deuxième question assez légitime à propos du REFRESH FAST, c'est de savoir ce que fait Oracle sur ce type de vues materialisées à chaque fois que vous faites un REFRESH. Je n'ai pas l'intention d'être exhaustif sur le sujet, mais au moins, voici ce que vous devrez faire pour en savoir plus. Regardons le cas ou la vue matérialisée est mise à jour sur les COMMIT :

drop view v1;
drop materialized view mv1;

create materialized view mv1
build immediate refresh fast
on commit as
select count(*) rowcounts,
       d1.level2 d1_level2,
       d2.level1 d2_level1,
       d3.level1 d3_level1,
       count(ftable.val1/1000) cmetric1,
       sum(ftable.val1/1000) metric1
 from d1,d2,d3,ftable
where ftable.d1=d1.id
  and ftable.d2=d2.id
  and ftable.d3=d3.id
group by d1.level2,d2.level1,d3.level1;

exec dbms_stats.gather_table_stats(user, 'MV1');

Pour savoir ce que fait Oracle, activez sql_trace, faites un update/commit de la table FTABLE par exemple et desactivez sql_trace. Utilisez tkprof pour repérer les ordres SQL générés en arrière plan associés à votre update/commit. Voilà ce que ca donne avec 11.1 :

sqlplus / as sysdba

show parameter user_dump_dest

connect scott

alter session set sql_trace=true

update FTABLE set val1=3
where (d1, d2, d3)
         in (select d1,d2,d3
               from ftable
             where rownum<=2); 

commit; 

alter session set sql_trace=false; 

exit 

cd /u01/app/oracle/diag/rdbms/redx/REDX/trace 

tkprof REDX_ora_9361.trc REDX_ora_9361.tkprof \ 
 explain=scott/tiger aggregate=no sys=no 

Vous pouvez ainsi facilement capturer l'ordre qui met à jour la vue matérialisée :

merge INTO "SCOTT"."MV1" "SNA$" USING
(SELECT /*+  OPT_ESTIMATE(QUERY_BLOCK MAX=598) */
   "MAS$3"."LEVEL2" "GB0",
   "MAS$2"."LEVEL1" "GB1",
   "MAS$1"."LEVEL1" "GB2",
   nvl(SUM(decode("DLT$0"."DML$$", 'I', 1,-1)
      *("DLT$0"."VAL1" / 1000)),0) "D0",
   SUM(decode("DLT$0"."DML$$",'I',1,-1)
      *decode(("DLT$0"."VAL1" / 1000),NULL,0,1)) "D1",
   SUM(decode("DLT$0"."DML$",'I',1,-1)) "D2"
 FROM
  (SELECT chartorowid("MAS$"."M_ROW$") rid$,
     "MAS$"."D1",
     "MAS$"."D2",
     "MAS$"."D3",
     "MAS$"."VAL1",
     decode("MAS$".old_new$,'N','I','D') dml$
   FROM "SCOTT"."MLOG$_FTABLE" "MAS$"
   WHERE "MAS$".snaptime$ > :1)
"DLT$0",
    (SELECT "MAS$"."ROWID" "RID$",
     "MAS$"."LEVEL1",
     "MAS$"."ID"
   FROM "SCOTT"."D3" "MAS$")
AS OF snapshot(:2) "MAS$1",
    (SELECT "MAS$"."ROWID" "RID$",
     "MAS$"."LEVEL1",
     "MAS$"."ID"
   FROM "SCOTT"."D2" "MAS$")
AS OF snapshot(:2) "MAS$2",
    (SELECT "MAS$"."ROWID" "RID$",
     "MAS$"."LEVEL2",
     "MAS$"."ID"
   FROM "SCOTT"."D1" "MAS$")
AS
OF snapshot(:2) "MAS$3"
 WHERE("DLT$0"."D1" = "MAS$3"."ID"
 AND "DLT$0"."D2" = "MAS$2"."ID"
 AND "DLT$0"."D3" = "MAS$1"."ID")
 GROUP BY "MAS$3"."LEVEL2",
   "MAS$2"."LEVEL1",
   "MAS$1"."LEVEL1") "AV$"
   ON (sys_op_map_nonnull("SNA$"."D1_LEVEL2")
          = sys_op_map_nonnull("AV$"."GB0")
   AND sys_op_map_nonnull("SNA$"."D2_LEVEL1")
          = sys_op_map_nonnull("AV$"."GB1")
   AND sys_op_map_nonnull("SNA$"."D3_LEVEL1")
          = sys_op_map_nonnull("AV$"."GB2"))
WHEN matched THEN
UPDATE
   SET "SNA$"."METRIC1" =
         decode("SNA$"."CMETRIC1" +
                  "AV$"."D1",0,NULL,nvl "SNA$"."METRIC1",0) +
                "AV$"."D0"),
       "SNA$"."CMETRIC1" = "SNA$"."CMETRIC1" + "AV$"."D1",
       "SNA$"."ROWCOUNTS" = "SNA$"."ROWCOUNTS" + "AV$"."D2"
DELETE
  WHERE("SNA$"."ROWCOUNTS" = 0)
WHEN NOT matched THEN
INSERT("SNA$"."D1_LEVEL2","SNA$"."D2_LEVEL1","SNA$"."D3_LEVEL1",
       "SNA$"."METRIC1","SNA$"."CMETRIC1","SNA$"."ROWCOUNTS")
VALUES("AV$"."GB0","AV$"."GB1","AV$"."GB2", 
       decode("AV$"."D1", 0, NULL, "AV$"."D0"), "AV$"."D1", "AV$"."D2")
 WHERE("AV$"."D2" > 0)

Vous pouvez remarquer dans cet ordre que seule la ligne correspondante dans MV1 est mise à jour et que (Cf section UPDATE) cette mise à jour est obtenue en ajoutant la variation et non par par un recalcul complet. Vous pouvez procéder ainsi pour repondre à d'autres questions si vous le souhaitez.

Exemple 1 - Evolution #3 : Mettre en cascade une seconde vue matérialisée également "REFRESH FAST"

Si maintenant vous voulez créer une vue matérialisée avec un niveau d'agrégation plus important, vous pouvez toujours la baser sur la vue matérialisée que vous venez de créer ; Il est en effet possible de mettre en cascade des vues matérialisées (Nested Materialized View) et que celles-ci restent REFRESH FAST. Par exemple, supposons qu'il faille maintenant créer une vue matérialisée basée sur le SELECT qui suit :

select d2_level1,
      d3_level1,
      avg(trunc(metric1)) ametric1
 from mv1
group by d2_level1, d3_level1;

Si votre vue matérialisée respecte les règles pour être REFRESH FAST, c'est à dire dans ce cas :

• La table de base (Ici MV1) à le bon "Materialized View Log" pour permettre à la vue d'être REFRESH FAST
  
• Pas d'expression à l'extérieur de les fonctions d'agrégation
• Si AVG(expr), inclure COUNT(expr)
• La vue contient COUNT(*)

Il sera possible de créer encore une vue matérialisée REFRESH FAST. Noter que dans l'exemple ci-dessous, elle est créée avec un REFRESH sur les COMMIT mais que souvent, un REFRESH périodique est plus adapté ; posez-vous bien la question :

create materialized view log on mv1
 with sequence, rowid
(d2_level1, d3_level1, metric1)
 including new values;

create materialized view mv2
build immediate
refresh fast on commit as
select d2_level1,
      d3_level1,
      count(*) cstar,           
      count(trunc(metric1)) cmetric1,           
      avg(trunc(metric1))   ametric1
 from mv1
group by d2_level1, d3_level1;

Materialized view created.

Voilà pour ce premier exemple. Notez qu'il y a de nombreuses limites à une telle approche en cascade; la complexité de mise en œuvre bien sur. Mais aussi l'impact des vues matérialisées sur ordre DML en terme de performance, surtout si le REFRESH est ON COMMIT ! Enfin, si vous pensez toujours faire vos refresh ON COMMIT n'oubliez pas qu'un changement sur les structures sous-jascentes (Un ordre DDL en général et à quelques exceptions prêt, cf PARTITION CHANGE TRACKING), invalidera les vues matérialisées et empêchera vos prochains ordres DML à moins que vous ne rafraîchissiez avec, par exemple, DBMS_MVIEW.REFRESH('MV1','C') les vues.

En outre, si la seconde vue matérialisée MV2 était une somme et non une moyenne, il est alors possible d'utiliser les GROUPING SET pour "mettre le contenue de la seconde vue matérialisée dans la première". C'est le sujet du second exemple qui suit...

Exemple 2 : MV avec Grouping Set

En substance cet exemple s'appuie sur le fait qu'on peut faire une somme dans un SELECT avec plusieurs niveaux d'agrégation dans le résultat. Par exemple, on peut calculer en même temps la somme des VAL1 de FTABLE agrégés sur, d'une part : d1_level2, d2_level1 et d3_level1 et d'autre part sur d2_level1 et d3_level1. Pour cela, il suffit d'utiliser ROLLUP dans la clause GROUP BY comme ceci :

select d1.level2 d1_level2,
      d2.level1 d2_level1,
      d3.level1 d3_level1,
      trunc(sum(ftable.val1)/1000) metric1
 from d1,d2,d3,ftable
where ftable.d1=d1.id
  and ftable.d2=d2.id
  and ftable.d3=d3.id
group by d2.level1,d3.level1, rollup(d1.level2);

Le principe est essentiellement le même que pour une vue matérialisée avec des agrégats. S'ajoute simplement la nécessité d'utiliser, dans la clause SELECT la fonction GROUPING sur les colonnes incluses dans le GROUP BY (Qu'elles soient dans les clauses ROLLUP, CUBE ou pas !). Pour plus d'information sur ces restrictions, reportez vous encore au manuel "Restrictions on Fast Refresh on Materialized Views with Aggregates". Vous constaterez, si vous avez laissé les Materialized View Log créés précédemment, vous pouvez créer MV3 comme ci-dessous :

drop materialized view mv3;

create materialized view mv3
   build immediate
   refresh fast as
 select d1.level2 d1_level2,
        d2.level1 d2_level1,
        d3.level1 d3_level1,
        grouping(d1.level2) glevel_d1,
        grouping(d2.level1) glevel_d2,
        grouping(d3.level1) glevel_d3,
        count(ftable.val1)  cmetric1,
        count(*)            cstar,
        sum(ftable.val1)    metric1
  from d1,d2,d3,ftable
 where ftable.d1=d1.id
   and ftable.d2=d2.id
   and ftable.d3=d3.id
 group by d2.level1,d3.level1, rollup(d1.level2);

Pour sélectionner le niveau d'agrégat sur d2.level1 et d3.level1, le SELECT sur la vue matérialisée ne doit alors pas contenir de SUM mais juste un critère de sélection sur glevel_d1, glevel_d2 et glevel_d3, comme ci-dessous :

select d2_level1,
      d3_level1,
      metric1
 from mv3
where glevel_d1=1
  and glevel_d2=0
  and glevel_d3=0;

Vous utiliserez également ce critère avec d'autres valeurs , pour le niveau sur d1_level2, d2_level1 et d3_level1 :

select d1_level2,
      d2_level1,
      d3_level1,
      metric1
 from mv3
where glevel_d1=0
  and glevel_d2=0
  and glevel_d3=0;

Et voilà qui termine ce second exemple, vous pouvez facilement l'enrichir en ajoutant les niveaux d'agrégat de votre choix dans la vue résultantes.

Avant d'en finir, supprimez les objets de cet exemple de votre base de données

drop materialized view mv3;
drop materialized view mv2;
drop view v1;
drop materialized view mv1;
drop table ftable purge;
drop table d1 purge;
drop table d2 purge;
drop table d3 purge;
drop table MV_CAPABILITIES_TABLE purge;

Pour conclure

Vous voyez où je veux en venir, non ? Les cubes MV de 11g... et c'est déjà une autre histoire

Il n'y a pas besoin d'espace dans les ordres SQL quand c'est "évident"

Là j'en ai appris une bien bonne ! Merci à Laurent pour avoir pointé cet exemple et à Tom Kyte pour l'explication :

Ne mettez pas d'espace :
select*from dual;
D
-
X

C'est une partie de mon monde qui vient de s'écrouler après 10 ans passés à faire de l'Oracle. J'ignorais complètement ce détail. A noter parmi la liste des trucs à utiliser plus fréquemment pour rendre mon code un peu plus illisible !

Attributs par défaut des index et tables partitionnés

Je ne surprendrai personne en disant que Oracle Partitioning est de loin l'option la plus utile d'Oracle 11g (quoique...). Et après bientôt 3 ans à écrire sur ce blog, rien sur le sujet !

Etonnante façon, de rendre la monnaie de ma pièce, le partitioning nous a probablement évité d'arrêter une application pendant plusieurs dizaines de minutes aujourd'hui suite à une corruption du BITMAP d'un "Locally Managed Tablespace". Pour faire court, voici l'histoire : la table principale de l'application de quelques dizaines de Giga-Octets, reçoit des nouvelles demandes tous les jours. Pour faciliter son archivage/purge, elle est partitionnée sur une séquence qui contient l'identifiant de jour, ainsi on peut facilement archiver les données ou les supprimer. Bref, après ce qui semble être un bug référencé mais non reproduit de 9.2, impossible d'allouer de nouveaux extents dans ce tablespace (Ne serait-ce pas le signe qu'il est urgent de fuir 9.2 ?) ; d'après Metalink, la table d'allocation des extents dans ce LMT est corrompue, il faut la reconstruire ! Petit problème, ça risque de prendre un long moment pour relire le tablespace en entier et reconstruire ce bitmap. Bonne nouvelle ! Les données sont toujours accessibles en lecture. 10 minutes plus tard, un nouveau tablespace créé et les partitions de la table et des index du jour dedans ; l'application est de nouveau opérationnelle. Okay ! Je vais écrire 2 ou 3 trucs sur le Partitioning prochainement, c'est promis.

D'abord, une colle : "La base a une standby ! Pensez-vous que le tablespaces est corrompu sur la standby aussi ? Je n'ai pas vraiment d'idée ; on verra ce que le VALIDATE datafiles donnera demain mais je ne m'attends pas vraiment a voir un bloc corrompu". Remarquez qu'à priori la seule façon de vérifier, c'est d'ouvrir la Standby en écriture et que ma base fait largement plus d'1 Tera-Octets...

Ensuite, puisqu'il s'agit encore de prendre des bonnes résolutions pour 2008, si ma table était dans plusieurs tablespaces, je pourrais en cas de nécessité de restaurer, redémarré avec seulement une partie des données alors que pour l'instant...

C'est noté, je vais commencer un post un peu fouillé à propos d'Oracle Partitioning. Avant ça, qu'au moins ce post soit utile... voici comment changer les attributs par défaut de vos partitions !

Sans parler de 11g qui va, grâce au partitionnement par intervalle, jusqu'à éliminer la nécessité d'ajouter des partitions dans un tel cas, ajouter une partition à une table partitionnée par plage de valeurs est aussi simple que ce qui suit :

alter table MYTABLE
add partition P20080108
  values less than (to_date('09/01/2008','DD/MM/YYYY'));

Et pourtant...

On peut imaginer que cette table a des index qui sont partitionnés selon le même critère ou que vous vouliez que la nouvelle partition soit ajoutée dans un tablespace bien spécifique. Dans un tel cas, les valeurs utilisées sont les attributs par défaut positionnés au niveau de l'index ou de la table partitionnées. Vous pouvez accéder/visualiser ces attributs par défaut dans les colonnes DEF_xxx des vues DBA_PART_INDEXES et DBA_PART_TABLES. Par exemple, les requêtes ci-dessous qui affichent les tablespaces dans lesquels sont générées les nouvelles partitions pour la table et les index de MYTABLE :

select table_name
   , def_tablespace_name
from user_part_tables
where table_name='MYTABLE';

TABLE_NAME DEF_TABLESPACE_NAME
---------- -------------------
MYTABLE    TABLESPACE0

select index_name
   , def_tablespace_name
from user_part_indexes
where table_name='MYTABLE';

INDEX_NAME   DEF_TABLESPACE_NAME
------------ -------------------
MYTABLE_IDX1 TABLESPACE1
MYTABLE_IDX2 TABLESPACE2

Remarque :
Ne pas confondre DBA_PART_INDEXES et DBA_PART_TABLES d'une part et DBA_IND_PARTITIONS/DBA_IND_SUBPARTITIONS et DBA_TAB_PARTITIONS/DBA_TAB_SUBPARTITIONS d'autre part. Les vues DBA_PART_xxx affichent les propriétés des index ou des tables qui sont spécifiques aux objets partitionnés, comme le tablespace par défaut des prochaines partitions/sous-partitions ; Les vues DBA_xxx_PARTITIONS affichent pour les index ou tables partitionnés les informations relatives aux partitions/sous-partitions déjà créées.

Pour modifier ces attributs, utilisez les commandes ALTER TABLE MODIFY DEFAULT ATTRIBUTES et ALTER INDEX MODIFY DEFAULT ATTRIBUTES comme ci-dessous :

alter table MYTABLE
modify default attributes
  tablespace TABLESPACE3;

alter index MYTABLE_IDX1
modify default attributes
  tablespace TABLESPACE4;

N'oubliez pas de vérifier :

select table_name
   , def_tablespace_name
from user_part_tables
where table_name='MYTABLE';

TABLE_NAME DEF_TABLESPACE_NAME
---------- -------------------
MYTABLE    TABLESPACE3

select index_name
   , def_tablespace_name
from user_part_indexes
where table_name='MYTABLE';

INDEX_NAME   DEF_TABLESPACE_NAME
------------ -------------------
MYTABLE_IDX1 TABLESPACE4
MYTABLE_IDX2 TABLESPACE2

sid='*' vs sid='&SID'

Si vous utilisez RAC (Et si vous n'utilisez pas RAC !!!), soyez attentif que les 2 syntaxes de la clause "sid=" des commandes ALTER SYSTEM SET/RESET sont complémentaires mais que sid='*' ne remplace pas sid='&SID". Si vous n'êtes pas attentif, ça peut vous amener à vous frapper la tête par terre. Voici pourquoi !

Nota Bene :
Ne pensez pas que ce problème n'est pas pour vous, parce que vous n'utilisez pas RAC ; les tests qui suivent sont réalisés avec 11.1.0.6 sous Linux et SANS RAC ; il est toujours possible que ce que je dis dépende de la version utilisée.

Prenons un paramètre qui n'est pas positionné par défaut dans le SPFILE de votre instance (Par exemple query_rewrite_enabled) et affichons la valeur courante pour commencer, dans le SPFILE, avec SQL*Plus :

SQL> show spparameters query_rewrite_enabled

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- -----
*        query_rewrite_enabled         string

Affichez ensuite la valeur en cours d'utilisation

SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
query_rewrite_enabled                string      TRUE

Vous pourriez interroger les "fixed views" gv$spparameter, gv$parameter et gv$parameter2 pour obtenir le même résultat, mais la commande show de SQL*Plus est bien assez précise pour cette démonstration.

Vous pouvez positionnez une valeur différente pour l'instance comme ci-dessous (On supposera que votre instance s'appelle REDX :

SQL> alter system
   set query_rewrite_enabled=TRUE
   scope=spfile
   sid='REDX';

System altered.

SQL> show spparameters query_rewrite_enabled

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- -----
REDX     query_rewrite_enabled         string      TRUE
*        query_rewrite_enabled         string      TRUE

Vous remarquez que le paramètre est en fait positionné 2 fois et comme vous pouvez vous en douter, si vous arrêtez, redémarrez votre instance, le paramètre est TRUE en mémoire (C'est à dire celui avec le nom d'instance comme préfixe) :

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1300324 bytes
Variable Size             310380700 bytes
Database Buffers          100663296 bytes
Redo Buffers                6139904 bytes
Database mounted.
Database opened.

SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
query_rewrite_enabled                string      TRUE

Si maintenant vous positionnez le paramètre à FALSE pour '*' comme ci-dessous, en fait, ce que vous faites, c'est changez la valeur pour toutes les instances sauf celles pour lesquelles le paramètre a été explicitement positionné auparavant :

SQL> alter system
   set query_rewrite_enabled=FALSE
   scope=spfile
   sid='*';

SQL>  show spparameters query_rewrite_enabled

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- -----
*        query_rewrite_enabled         string      FALSE
REDX     query_rewrite_enabled         string      TRUE

Pour vous en persuader, arrêtez, redémarrez l'instance et vous verrez que la valeur du paramètre courant n'a pas changée, bien que vous l'ayez modifiée dans le SPFILE :

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1300324 bytes
Variable Size             310380700 bytes
Database Buffers          100663296 bytes
Redo Buffers                6139904 bytes
Database mounted.
Database opened.
SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE

1er piège.

Imaginons que pour une raison quelconque, vous ayez une telle configuration et soyons grand seigneur avec la même valeur et que vous ne vous souciez pas de la clause 'sid='

SQL> alter system
   set query_rewrite_enabled=TRUE
   scope=spfile
   sid='*';

SQL> alter system
   set query_rewrite_enabled=TRUE
   scope=spfile
   sid='REDX';

SQL> show spparameters query_rewrite_enabled;

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- -----
*        query_rewrite_enabled         string      TRUE
REDX     query_rewrite_enabled         string      TRUE

Si vous exécutez la commande ALTER SYSTEM SET sans la clause sid=, voici ce qu'il va ce passer :

SQL> alter system
   set query_rewrite_enabled=FALSE
   scope=spfile;

SQL> show spparameters query_rewrite_enabled;

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------
*        query_rewrite_enabled         string      FALSE
REDX     query_rewrite_enabled         string      TRUE

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1300324 bytes
Variable Size             310380700 bytes
Database Buffers          100663296 bytes
Redo Buffers                6139904 bytes
Database mounted.
Database opened.
SQL> show parameter query_rewrite_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
query_rewrite_enabled                string      TRUE

Votre DBA a redémarré l'instance pour rien... Mais il y a pire !

2eme piège.

Poussons maintenant le vice avec un paramètre modifiable dynamiquement. Par exemple log_archive_dest_state_2. Modifions une première fois ce paramètre en précisant sid='REDX' comme ci-dessous :

SQL> alter system
   set log_archive_dest_state_2=defer
   sid='REDX';

System altered.

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
log_archive_dest_state_2             string      DEFER

Si la deuxième fois, on modifie le paramètre en omettant la clause sid= , comme ci-dessous :

SQL> alter system
   set log_archive_dest_state_2=enable;

System altered.

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------
log_archive_dest_state_2             string      ENABLE

mais si vous arrêtez et redémarrez l'instance, le paramètre reprend la valeur DEFER

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area  418484224 bytes
Fixed Size                  1300324 bytes
Variable Size             318769308 bytes
Database Buffers           92274688 bytes
Redo Buffers                6139904 bytes
Database mounted.
Database opened.

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER

La raison est évidemment que le SPFILE a été modifie mais pour '*" et pas 'REDX', comme vous pouvez le voir ci-dessous :

SQL>  show spparameters log_archive_dest_state_2;

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- ------
REDX     log_archive_dest_state_2      string      DEFER
*        log_archive_dest_state_2      string      ENABLE

Conclusion...

En ce début d'année, nous retiendrons ce qu'il y a de positif dans cet exemple ; évitez de laisser traîner des préfixes dans le fichier SPFILE, sauf si vous le faites exprès. Voici les 2 commandes qui seront vos amies désormais (RAC ou pas, vous l'avez compris !) :

SQL> alter system
   reset query_rewrite_enabled
   scope=spfile sid='REDX';

SQL>  show spparameters query_rewrite_enabled

SID      NAME                          TYPE        VALUE
-------- ----------------------------- ----------- -----
*        query_rewrite_enabled         string      TRUE 

Et n'oubliez pas que, même si vous faites confiance à Oracle, le plus souvent, vérifier ce que vous venez de faire permet simplement d'éviter se frapper la tête contre les murs quelques jours ou semaines après ! Ça aurait pu être un proverbe chinois, non ?

Le blog de Hugu

Si vous êtes un fan d'Oracle à la recherche de sites utiles, intéressants et actifs en français, ajoutez le fil RSS du blog de Hugu à votre news Reader. Et puis je l'ai déjà dit, il nous dégote les nouveautés avant tout le monde, accrochez-vous au blog de Jean-Philippe aussi. Allez ! Vous avez un supporter : moi !

Greg

PS : Jean-Phi dans tes résolutions de 2008 (Que je te souhaite très bonne au passage), mets moins de fun et un peu plus de sueur dans ton blog ;-)

Faut-il parler à nouveau de clusters étendus ?

Geoclusters, Extended/Stretch clusters... Voici un sujet qui m'a croisé plusieurs fois depuis maintenant 7 ans, dans 4 pays et sur 2 continents. Sujet passionnant avec plus d'embûches que d'étincelantes réussites personnelles, il faut l'avouer ! Bien sur : j'en connais qui finalement fonctionnent et même auxquels j'ai modestement contribué quand c'était avec RAC... mais si c'était à refaire ?

Ce n'est un secret pour personne que ce n'est pas franchement mon cheval de bataille. Non pas que je n'adorerais pas avoir les mains libres pour mener un tel projet de bout en bout. Non pas que ce soit infaisable (en production j'entends !). Non pas, et ça fait des frissons rien qu'en le disant, que la technologie ne soit pas là et bientôt mature ! Non ce sont : la motivation, la compréhension de la vie d'une application, la gestion des problèmes.

Prenons la motivation. Si quelqu'un pouvait venir me voir en disant, "Nous voulons mettre en place un cluster étendu pour notre image de marque". Ou s'il disait, "Je veux faire ça parce que je crois ce que me dit IBM, Sun, HP, Symantec ou Oracle et que leur marketing est trop bon !". S'il disait : "J'en ai besoin pour être conforme à je ne sais pas quelle législation de dans 15 jours". S'il disait : "Je crois que c'est un passage pour adresser quelque chose de plus grand ensuite" ou "Je suis en mission pour le Seigneur, il faut que tu reviennes dans le band !".

A vrai dire à chaque fois qu'on vient avec ce sujet, c'est pour mettre en place un DRP, pour utiliser toutes les ressources ou parce que l'application est 24x7 avec 5 neufs : banalités !

Prenons la compréhension de la vie d'une application, maintenant :

• A quoi ça sert d'utiliser toutes les ressources si pour le même prix, on pourrait avoir 5 fois plus de ressources et une solution DRP sans Geo Clusters ? Qu'on n'a pas besoin de 5 neufs ? Je sais, c'est fou le nombre de clients qui ont des liens DWDM de 300 kms gratuits ou pour qui RAC ou Veritas est déjà payé. A se demander pourquoi il y en a qui paient !
  
• Alors disons qu'il vous faut les 5 neufs ou même 4. Il y a des choses à faire avant de mettre en place un geocluster, d'autres pendant et d'autres après. Les geoclusters que je connais sont souvent dans un "no man's land" ; le jour où ils pourront servir, on verra ce qu'il adviendra. Mais, c'est vrai, on parle d'un monde sans RTOS !
  

Alors voila, disons le autrement : je serais honoré de travailler pour celui qui aura compris ça et qui pourra dire : Je veux 99.999% et je veux pouvoir migrer de version d'OS, d'application ou de base de données sans arrêter mon système et je veux que le système soit conforme aux exigences de performances pendant cette période. J'ai un cahier des charges, un SLA correctement formalisé, j'ai un plan de continuité de service, ET j'ai le pouvoir de le faire !

Faisons l'analyste du Gartner : C'est pour 2008 (Probabilité 0,0001%)! Alors... Et mes rêves de geoclusters flamboyants ? C'est ce que je disais, ce n'est pas mon cheval de bataille, quoique !

Bon je vais partager 3 ou 4 réflexions, si vous n'êtes pas encore découragés par ce post

la gestion des problèmes ou plutôt, parce que je n'ai pas l'intention d'être exhaustif, voilà ce que vous pourriez appeler les idées les plus communément fausses sur le sujet.

Nota Bene : On supposera que l'objectif affiché d'un geocluster est de survivre la perte d'un site et que la bascule entre les 2 sites se fasse de manière automatique ! Ce n'est qu'une supposition je l'avoue mais c'est ce que pense la plupart des gens.

• Fausse idée numéro 1 : le cluster étendu est d'abord un problème de cluster !

Explication : Prenez un crayon et dessinez une architecture étendue sur le serveur de données ! La première question que vous allez vous poser, c'est où mettre les données, non ? Si on veux survivre à la panne d'un site, il faut les mettre sur 2 site séparés. Pour mettre les données dans 2 data center avec un cluster (C'est à dire pas avec une techno de type standby ou un système de réplication de la base de données), de quoi disposez-vous ?
a) La réplication au niveau de la baie ! C'est à peu près incompatible avec un geocluster, au moins avec une bascule automatique... Non pas vraiment que la bascule ne soit pas automatisable mais qu'après la bascule l'environnement entrera dans un mode dégradé. La bascule a un coût qui empêche de l'automatiser (*)
b) La réplication au niveau du serveur (host) soit avec ASM dans le cas d'Oracle soit avec un Volume Manager. Remarquez que ASM peut fonctionner sur un volume logique et le fait d'utiliser ASM ne signifie pas forcément que c'est ASM qui réplique les fichiers de base de données mais je m'égare...

Corollaire : Le jeu #1 du GeoCluster consiste donc à construire un sous-système de stockage qui soit performant malgré la distance et qui puisse repartir d'autre chose que de zéro lorsque pour une raison x ou y il y a eu perte d'un site. Pour construire ce genre de bête, il faut savoir faire de l'affinité en lecture avec les baies de stockage et ne ré-appliquer que le différentiel en cas de perte momentanée de l'accès à un système de stockage. Justement ce que ASM 11g sait faire ! VxVM sait aussi mais à quoi bon investir dans un trucs, même gratuit, dont on aura toutes les peines du monde à se débarrasser ensuite ?

Bon mais je vais un peu vite à la conclusion. En fait tout est une question de besoin. Est-ce que lire une données aléatoirement d'un site à l'autre en fonctionnement initial convient à vos objectifs de performance ? Est-ce que reconstruire l'intégralité des miroirs disques en cas de perte momentanée de l'accès a un site convient à vos contraintes de disponibilité ? Si oui, c'est un début... mais, même si votre base de données fait 2 Go, assurez-vous que 15 ms ou plus pour faire un I/O sur les redo logs est bien tolérable ; On a tué des gestionnaires de SAN pour moins que ça, vous savez.

Deux dernières remarques :
* Pour l'instant, il n'est pas question de RAC plutôt que d'une autre solution avec Oracle en single instance ; il faut toutefois que je précise maintenant qu'avec RAC, il y a une contrainte supplémentaire, il faut que le Volume Manager si vous voulez l'utilisez fonctionne en cluster et en parallèle. Ce n'est pas le cas de tous les VM et c'est souvent l'objet de licences supplémentaires. Enfin ASM, met tout le monde d'accord ; ça fonctionne toujours mais ne règle pas tous les problèmes de performances du sous-système de stockage. Il peut que ce qu'il peut
* Ce n'est pas parce que vos bases de données font 2 Go que vous pouvez tolérer que votre sous système I/O ne soit pas performants. Par exemple, chaque groupe de commit implique un I/O.

• Fausse idée numéro 2 : On peut mettre en place un cluster étendu avec 2 sites distant !

Et non ! Il faut 3 sites et la raison s'explique en quelques mots... par l'absurde.

Disons que vous avez 2 sites que nous appellerons A et B. Il y a plusieurs types de pannes possibles et vous voudrez à tout prix éviter que les données deviennent incohérentes. Vous voudrez éviter le "split brain syndrome" (Foutaises ! j'ai une diagonale). Pour cela, il faut à tout prix qu'à aucun moment il y ait 2 incarnations du même système vivant des vies séparées et incapables ensuite de se resynchroniser. Pour adresser cette contrainte, le cluster utilise le suicide (Je n'aborderais pas la vieille histoire du STONITH mais sur un geocluster plus qu'ailleurs, c'est impossible). Imaginons donc que vous perdiez le lien entre A et B. Vous ne pouvez pas laisser dans une telle situation A et B survivre. Il faut que l'un des 2 et un seulement un survive ! Disons que nous choisissons A survit. Je vous laisse faire le raisonnement ou vous choisissez B.

Maintenant imaginons que A soit détruit ! Si B reste seul au monde sans aucun autre site pour l'aider et qu'il ne peut plus joindre A que peut-il supposer ? La connexion vers A est morte ou A est mort ? En fait dans ce cas B n'a aucun moyen de faire la différence et pour agréer votre choix qui est que A survit dans le cas d'un perte de la connexion entre les sites, B choisira de se suicider.

Conclusion : Il faut 3 sites pour construire un geocluster si vous voulez que la bascule soit automatique ! (Encore une fois RAC ou pas)

• Fausse idée numéro 3 : Le débit de DWDM convient pour faire du cluster etendu jusqu'à 180 km !
  

La évidemment je suis provocateur et tout est encore une question de l'objectif que l'on se fixe. Mais en substance ce que je veux dire c'est que le débit en bien moins important que la latence dans ce cas. N'oubliez pas, par exemple, qu'à chaque fois que vous validez une transaction, vous devez attendre un "ack" du sous système de stockage pour valider la transaction.

Bien sur la plupart des constructeurs font des différences selon la distance et parlent, par exemple de Metro Cluster. Disons pour faire simple que c'est juste un problème d'ampleur de ce que vous allez rencontrez. Et considérez qu'au delà de 3 km votre vie va basculer.

Si, en plus, vous utilisez RAC, il y a le trafic du cache fusion à considérer. 9i proposait une solution RAC Guard pour réduire cette contrainte. 10g permet de "partitionner" l'activité grâce aux services et, je ne dis pas que vous en aurez besoin, mais gardez cette carte dans votre manche et validez que on peut différencier les activités au niveau de l'application. Pour dire les chose autrement : Un point de synchronisation qui empêche la montée en charge d'une application sur une single instance, pourra devenir un problème avec RAC et un gros problème avec RAC en geocluster.

• Fausse idée numéro 4 : Un stretch cluster est une solution de DRP

J'imagine que vous direz que je joue sur les mots mais (cf fausse idée numéro 1), c'est le sous-système de stockage qui assure la reprise des données en cas de désastre dans un cluster étendu et non pas le logiciel cluster (avec ou sans RAC). Le clusterware, quelque soit son fournisseur, n'est qu'une partie de la solution. Autrement dit... si je factorise et ramène tout à Oracle, la question revient à se poser la question équivalente qui est : "Vaut-il mieux utiliser ASM ou Data Guard pour dupliquer ses données sur un site distant ?" (Et laisser respectivement au logiciel cluster ou à l'observer de Fast Start Failover le soin de basculer automatiquement). Et la réponse pour paraphraser un homme illustre est : "It all depends" !

Pour conclure :
En dépit de mes demi-réussites dans ce domaine et de ce post en demi-teinte, le temps est sans doute venu pour que se développent de plus en plus de clusters étendus. La technologie est là avec 11g et 2008, 2009 et probablement 11g R2 la verront maturer. Quand à savoir si le bon projet pour y allez, c'est le vôtre ? Moi, j'ai fait ma part du travail en vous prévenant, si après ça, vous êtes encore plus motivé... Je vous envie déjà : racontez moi la suite !

Grégory

(*) Le problème d'un cluster failover c'est qu'il a souvent tendance à faire ce pourquoi il a été conçu, c'est à dire basculer. C'est comme la femme de ménage qui débranche le serveur pour passer l'aspirateur. Vous me direz c'est pas grave sauf si la bascule laisse des traces. Une bascule avec de la réplication de baie laisse des traces...

Perl, Oracle 11g, Apache et Ubuntu...

Perl ! un autre de ces langages qui vont tellement bien avec Oracle... C'est John Scoles de The Pythian Group qui s'occupe du module DBD::Oracle. Il faut dire que c'est le langage de prédilection des DBA qui utilisent Unix et Windows (A mon corps défendant en ce qui me concerne mais quand même !). Si vous cherchez à savoir si vous pouvez utiliser Perl avec Oracle 11.1, la réponse est en détail dans cet échange et en substance oui.

Pour les besoins d'un proof of concept, j'ai développé quelques écrans web en Perl et ce premier Post synthétise comment installer Perl, DBD::Oracle avec Oracle 11g avant de l'utiliser avec Apache sur Ubuntu. Un prochain post (C'est l'intention qui compte, non ?) devrait aborder la question de l'utilisation des LOB avec Perl.

Installer Perl, DBI et DBD::Oracle avec Oracle 11g sur Ubuntu

Ça prend 15 minutes mais, comme dit un de mes amis, ça va mieux en le disant. Pour installer Perl sur Ubuntu (Si ce n'est pas déjà fait), il suffit de lancer la commande ci-dessous :

sudo apt-get install perl

Pour installer Oracle 11g sur Ubuntu, regardez ce post de Augusto. Le client suffit si vous ne voulez pas installer Oracle Server.

Une fois Perl et Oracle installés, vous pouvez installer DBI et DBD::Oracle. Pour cela vous pouvez directement installer les modules depuis CPAN.org comme décrit ici. Je ne sais pas si c'est possible d'installer DBD::Oracle avec 11g de cette manière (sûrement !). La méthode que j'utilise pour ma part, fonctionne même quand votre client n'a pas d'accès à Internet, consiste à :

• Télécharger le module que vous voulez installer en le recherchant avec le moteur de recherche de CPAN :
  

http://search.cpan.org/~pythian/DBD-Oracle/Oracle.pm
est l'URL ou vous trouverez le fichier DBD-Oracle-x.xx.tar.gz;
vous pouvez le téléchargez avec wget

• Mettre le fichier sur la machine cible et le décompresser :

tar -zxvf DBD-Oracle*.tar.gz
cd DBD-Oracle* 

• Positionnez la variable ORACLE_HOME

export ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 

• Créez le fichier Makefile avec Perl (C'est la qu'est l'astuce le -l permet de compiler sans que les makefile de client d'Oracle soient disponibles. Il se trouve qu'en 11g ces fichiers sont sur le CD client et plus avec le serveur comme c'était le cas avec 10g).
  

perl Makefile.PL -l 

• Compilez le module

make

• Installez DBD::Oracle

sudo make install

• Supprimez la distribution

cd ..
rm -rf DBD-Oracle*

• Testez que le module est dans les modules reconnus par Perl
  

cat >test.pl <<EOF
use DBI;
use DBD::Oracle;
EOF
perl test.pl

Quelques remarques :

• Vous devez installer DBI avant DBD::Oracle si ce n'est pas déjà fait. La procédure est essentiellement la même au nom de fichier prêt et au flag "-l" que vous n'avez pas besoin d'utiliser dans le cas de DBI
• Si vous n'avez pas la possibilité de vous connecter root ou d'exécuter le make install avec la commande sudo, il faudra installer DBI et DBD::Oracle dans un répertoire local à votre utilisateur ; pour ce faire :
• Changez le chemin d'installation de votre module en ajoutant la variable PREFIX=/home/username/pm lorsque vous construisez le fichier Makefile. Voici un exemple d'une telle commande :
  

  perl Makefile.PL PREFIX=/home/username/pm

• Positionnez la variable d'environnement PERL5LIB avant de lancer perl pour vous assurer que le module est bien dans le chemin de recherche de Perl. (Il s'agit de pointer sur le répertoire qui contient le .pm du module Perl :
  

  export PERL5LIB=/home/username/pm/lib/perl5/site_perl/5.8.5:$PERL5LIB

• Enfin, si Perl n'est pas installé et que vous ne pouvez pas vous connecter root ou exécuter apt-get avec sudo ou si Perl est déjà installé dans une version différente de celle que vous voulez utiliser, il est très simple de l'installer à partir des sources :
• Récupérez la version de Perl qui vous intéresse sur la page source du site CPAN
• Faire l'extraction du fichier TARBALL et naviguez dans le répertoire correspondant
• Lancez l'installation de Perl à l'aide de la commande suivante. Le "prefix" l'installation vous sera demandé ; Précisez un directory que vous avez créé pour l'occasion et pour lequel vous avez les droits en lecture et écriture :
  

  ./Configure

• "perl" est normalement accessible depuis ~/bin et le prefix sera ajouté automatiquement lors de l'installation des modules DBI et DBD
  

Testez DBD::Oracle

On ne peut vraiment pas comparer JavaEE et Perl quand on parle de développer des application web et pourtant, il y a vraiment un truc génial avec Perl, c'est qu'un script qui génère une page HTML est exactement comme un script quelconque, hormis que vous voudrez imprimer du HTML plutôt que du texte simple. Pour tester Perl et Oracle, écrivez un script comme celui ci-dessous (par exemple dans un fichier example1.pl) :

#!/usr/bin/perl -w
use strict;
use DBI;

print "Content-type: text/html\r\n\r\n";
print "\n";

$ENV{ORACLE_HOME}='/u01/app/oracle/product/11.1.0/db_1';

my $dbh = DBI->connect( 'dbi:Oracle:ORCL', 'scott', 'tiger',
                      { RaiseError => 1, AutoCommit => 0 }
                    ) || die "Can't connect to the database : $DBI::errstr";

my $sql = qq{ select dummy from dual };
my $sth = $dbh->prepare( $sql );
$sth->execute();

my( $dummy );
$sth->bind_columns( undef, \$dummy );

while( $sth->fetch() ) {
print "dummy : $dummy
\n";
}

$dbh->disconnect();
print "\n";

N'oubliez pas :

• de remplacer ORACLE_HOME par le ORACLE_HOME de votre client
• de remplacer ORCL par l'alias TNS décrivant la connexion à la base de données
• de remplacer "scott" et "tiger" par un nom d'utilisateur et un mot de passe pour vos connecter à votre base de données

Pour exécuter, votre "page web", lancer la commande ci-dessous :

perl example1.pl

Le résultat, ci ce fonctionne, ressemble a ceci :

Content-type: text/html


Database connection
dummy : X

Installer Apache et mod_perl.
Je ne rentrerai pas ici dans le détail de comment installer Apache manuellement ou de le configurer. La magie d'Ubuntu est toujours la même, si vous utilisez aptitude, ça prend 5 minutes ; Vous trouverez des détails sur ce post. En substance, lancez les 2 commandes qui suivent :

sudo apt-get install apache2
sudo apt-get install libapache2-mod-perl2

C'est fait !

La manière dont les fichiers de configuration et le répertoire racine sont disposés sur Ubuntu est vraiment simple de lecture et d'utilisation. Pour une première approche, créez un répertoire cgi-bin sous /var/www :

sudo mkdir /var/www/cgi-bin

L'ensemble des fichiers de configurations sont dans /etc/apache2. Editez le fichier qui contient le paramétrage du site par défaut

sudo vi /etc/apache2/sites-available/default

Remplacez les références à /usr/lib/cgi-bin par des references à /var/www/cgi-bin comme ci-dessous :

ScriptAlias /cgi-bin/ /var/www/cgi-bin/
<Directory "/var/www/cgi-bin">
 AllowOverride None
 Options +ExecCGI -MultiViews +SymLinksIfOwnerMatch
 Order allow,deny
 Allow from all
</Directory>

Redémarrez Apache

sudo /etc/init.d/apache2 restart

Pour vérifier que tout fonctionne

Copiez le fichier example1.pl créé précédemment dans /var/www/cgi-bin et positionnez les droit d'exécutions pour Apache comme ci-dessous :

sudo cp ~/example1.pl /var/www/cgi-bin/.
sudo chmod a+x /var/www/cgi-bin/example1.pl 

En supposant que Apache fonctionne sur le port 80, vous pouvez simplement utiliser wget pour tester votre example comme ci-dessous :

wget http://localhost/cgi-bin/example1.pl -O -

Le résultat, ressemble a ce qui suit :

--16:39:18--  http://localhost/cgi-bin/example1.pl
         => `-'
Resolving localhost... 127.0.0.1
Connecting to localhost|127.0.0.1|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/html]

  [<=>                                                              ] 0             --.--K/s            
Database connection
dummy : X

  [ <=>                                                             ] 63            --.--K/s

16:39:19 (7.49 MB/s) - `-' saved [63]

Voila c'est aussi simple que ça. Rendez-vous est pris pour discuter de comment manipuler des LOB avec Perl mais c'est déjà une autre histoire.

Utiliser oraenv dans vos scripts

Il est possible d'utiliser le script oraenv dans vos propres scripts et ainsi de bénéficier des évolutions des variables d'environnement lors de changements de versions d'Oracle; sans jouer avec <<EOF ! Vous remarquerez, par exemple, qu'en 11g, oraenv positionne désormais ORACLE_BASE !

Positionnez les variables ORAENV_ASK et ORACLE_SID, puis utilisez oraenv comme ci-dessous :

export ORAENV_ASK=NO
export ORACLE_SID=REDX
. oraenv
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1 is /u01/app/oracle

En 11g, votre environement ressemble alors à ceci :

env | grep -e ORA -e LIBRARY
LD_LIBRARY_PATH=/u01/app/oracle/product/11.1.0/db_1/lib
ORACLE_SID=REDX
ORACLE_BASE=/u01/app/oracle
ORAENV_ASK=NO
ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

Revoir la taille de vos fichiers à la hausse et à la baisse

J'avoue que ça semble un peu trivial si vous lisez ce blog mais je n'ai pas vraiment trouvé mon bonheur sur le web et comme on est jamais aussi bien servi que par soi-même, voici quelques requêtes pour aider à faire augmenter et réduire la taille des fichiers de base de données. Voilà pour compléter mes posts précédents à propos de DBMS_REDEFINITION par exemple :

• 2-3 trucs à propos de DBMS_REDEFINITION
• 2-3 trucs (de plus) à propos de DBMS_REDEFINITION
  

La commande en question :

D'abord, il faut noter que changer les paramètres d'un fichier de données, est assez simple. Si vous voulez utiliser une commande indépendante de votre OS et du fait que vous utilisiez ou non OMF, voici comment faire.

D'abord lister les fichiers pour un tablespace donné :

set pages 100
set lines 140
col tablespace format a30
col bl_kb format 99
col management format a10
col segment format a6
col contents format a9
col status format a7
col size_mb format 999,999
col real_mb format 999,999
col max_mb format 999,999
col nfile format 9999
col perfree format 999.99

select t.tablespace_name tablespace,
      t.block_size/1024 bl_kb,
      t.extent_management management,
      t.segment_space_management segment,
      t.contents contents ,
      t.status,
      f.nfile,
      round(f.size_mb-nvl(fs.free_mb,0)) real_mb,
      f.size_mb,
      f.max_mb,
      round((f.max_mb-(f.size_mb-nvl(fs.free_mb,0)))
            /f.max_mb*100,2) perfree
 from dba_tablespaces t,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) size_mb,
              round(sum(
                case when autoextensible='NO' then bytes
                     else greatest(bytes,maxbytes) end)
                         /1024/1024) max_mb,
              count(file_id) nfile
         from dba_data_files
        group by tablespace_name) f,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) free_mb
         from dba_free_space
        group by tablespace_name) fs
where t.tablespace_name=f.tablespace_name
  and t.tablespace_name=fs.tablespace_name(+)
order by size_mb;

Une fois que vous visualisez la liste de vos tablespaces, vous pouvez afficher la liste des fichiers associés en exécutant la requête ci-dessous et en remplaçant la variable &TSNAME par le nom du tablespace que vous observez :

set lines 120
col id format 9999;
col file_name format a80
col size_mb format 999,999
col extens format a3
col max_mb format 999,999

select d.file_id id,
      d.file_name,
      round(d.BYTES/1024/1024) size_mb,
      d.AUTOEXTENSIBLE extens,
      round(d.maxbytes/1024/1024) max_mb
 from dba_data_files d,
      v$datafile df
where tablespace_name='&TSNAME'
  and df.file#=d.file_id
order by df.creation_time;

Et enfin vous pouvez re-dimensionner vos tablespaces ou changer d'autres propriétés avec la commande alter database datafine n où n est le file_id :

alter database datafile 10
       resize 4096M;

Pour plus d'infos, reportez-vous à la documentation du "ALTER DATABASE".

Vous ne pouvez pas réduire votre fichier au deçà de l'extent alloué le plus loin.

D'où l'utilité parfois de toutes ces fonctionnalités dont la plupart ne sont disponibles qu'en version Enterprise Edition que sont : "ALTER INDEX REBUILD ONLINE", "DBMS_REDEFINITION", "ALTER INDEX COALESCE", "ALTER TABLE SHRINK...", "ALTER TABLE MOVE...ONLINE" qui vous permettent de déplacer des données ou index dans un nouveau tablespace

Voici une requête qui permet de générer la liste des ordres SQL pour retailler au plus court l'ensemble des fichiers de données d'un tablespace :

select 'alter database datafile '||
         a.file_id ||' resize ' ||
         ceil(nvl(b.last_block*c.block_size
           /1024/1024,512)) ||'M;' command
 from dba_data_files a,
       (select file_id,
               max(block_id+blocks-1) last_block
          from dba_extents
         group by file_id ) b,
       dba_tablespaces c
where a.file_id = b.file_id(+)
  and c.tablespace_name=a.tablespace_name
  and a.tablespace_name='&TSNAME'
  and ceil(blocks*c.BLOCK_SIZE/1024/1024)-
      ceil((nvl(hwm,1)* c.BLOCK_SIZE)/1024/1024 ) > 0;

Vous pouvez empêcher les fichiers de continuer à s'étendre dans un système de fichiers.

Pour réduire l'espace occupé par une base de données, il est fréquent d'utiliser des fichiers en mode d'extension automatique. Bien sur avec ASM il est aisé d'ajouter des disques à un point de montage mais pour des raisons qui me dépassent, certaines personnes n'utilisent pas encore ASM , même en 10g. Pour répondre à cette dernière préoccupation qui consiste à savoir combien d'espace restera disponible lorsqu'on aura arrêté l'extension automatique des fichiers situés sur un point de montage, voici une première requête :

accept mount2stop prompt "Mount to stop autoextend from (e.g./u03/oradata/ORCL) :"
/u03/oradata/ORCL

set pages 100
set lines 140
col tablespace format a30
col bl_kb format 99
col management format a10
col segment format a6
col contents format a9
col status format a7
col size_mb format 999,999
col max_mb format 999,999
col nfile format 9999
col perfree format 999.99

select t.tablespace_name tablespace,
      t.block_size/1024 bl_kb,
      t.extent_management management,
      t.segment_space_management segment,
      t.contents contents ,
      t.status,
      f.nfile,
      f.size_mb,
      f.max_mb,
      round((f.max_mb-(f.size_mb-nvl(fs.free_mb,0)))
             /f.max_mb*100,2) perfree
 from dba_tablespaces t,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) size_mb,
              round(sum(
                case when autoextensible='NO' then bytes
                     when instr(file_name,'&mount2stop')=1 then bytes
                     else greatest(bytes,maxbytes) end)
                        /1024/1024) max_mb,
              count(file_id) nfile
         from dba_data_files
        group by tablespace_name) f,
      (select tablespace_name,
              round(sum(bytes)/1024/1024) free_mb
         from dba_free_space
        group by tablespace_name) fs
where t.tablespace_name=f.tablespace_name
  and t.tablespace_name=fs.tablespace_name(+)
order by size_mb;

Ou si vous voulez faire un focus plus particulier sur les fichiers actuellement en mode auto extend et les tablespaces associés

col size_mb format 999,999
col max_mb  format 999,999
col free_mb format 999,999

select tablespace_name,
      f.file_name,
      round(f.bytes/1024/1024) size_mb,
      round(f.maxbytes/1024/1024) max_mb,
      round((f.maxbytes-f.bytes)/1024/1024) free_mb
      from dba_data_files f
where instr(f.file_name,'&mount2stop')=1
  and f.autoextensible='YES' and f.maxbytes!=f.bytes
order by tablespace_name, f.file_id

Une fois que vous avez déterminé à quels tablespaces il faut ajouter des nouveaux fichiers pour éviter de tomber à court d'espace lorsque vous aurez bloqué l'extension automatique, affichez les fichiers actuels avec la requête ci-dessous :

set lines 120
col id format 9999;
col file_name format a80
col size_mb format 999,999
col extens format a3
col max_mb format 999,999

select d.file_id id,
      d.file_name,
      round(d.BYTES/1024/1024) size_mb,
      d.AUTOEXTENSIBLE extens,
      round(d.maxbytes/1024/1024) max_mb
 from dba_data_files d,
      v$datafile df
where tablespace_name='&TSNAME'
  and df.file#=d.file_id
order by df.creation_time;

Vous pouvez alors ajouter des fichiers à l'aide de la commande "alter tablespace add datafile" comme par exemple ci-dessous (Changez les noms et paramètres selon vos besoins) :

alter tablespace X add datafile '/u04/oradata/ORCL/x02.dbf'
       size 1024M autoextend on next 16M maxsize 4096M;

Enfin une fois que vous êtes assuré de ne pas manquer d'espace, bloquez l'extension automatique à l'aide de la requête qui suit et qui génère le SQL pour bloquer les fichiers concernés :

accept mount2stop prompt "Mount to stop autoextend from (e.g./u03/oradata/ORCL) :"
/u03/oradata/ORCL

set lines 140
col tablespace_name format a15
col command format a120

select tablespace_name,
      'alter database datafile '||
      f.file_id||' autoextend off;' command
 from dba_data_files f
where instr(f.file_name,'&mount2stop')=1
  and f.autoextensible='YES'
order by tablespace_name, f.file_id;

Exécutez les ordres générés par la dernière requête...