Mes petits carnets de DBA: 2012

Les surprises de CREATE TABLE AS

Pour certaines opérations, il n'est pas possible de faire un ALTER TABLE. Dans ce cas, on utilise généralement le mécanisme suivant :

Créer la nouvelle table à partir de la première
Supprimer l'ancienne table
Renommer la nouvelle

Malheureusement, le CREATE TABLE AS d'Oracle n'inclue pas les objets liés à la table: index, contraintes et triggers.

Le script pour générer le script

J'ai écrit les requêtes suivantes qui devraient vous permettre de récupérer le script à exécuter. Il faudra remplacer les paramètres par les vrais noms des tables dans la partie centrale (création/drop/renommage de table)

/*Drop des triggers*/
SELECT 'DROP TRIGGER "'||owner||'"."'||trigger_name||'";' from all_triggers WHERE owner='&mon_schema' AND table_name='&ma_table';
/*Drop des index*/
SELECT 'DROP INDEX "'||owner||'"."'||index_name||'";' from all_indexes WHERE owner='&mon_schema' AND table_name='&ma_table' AND NOT (index_name LIKE 'PK_%' OR uniqueness='UNIQUE');
/*Drop des contraintes*/
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='C';
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='R';
SELECT 'DROP CONSTRAINT "'||owner||'"."'||constraint_name||'";' from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND
 constraint_type='P';
 
/*Création de la nouvelle table*/
CREATE TABLE &mon_schema.&ma_table_new AS SELECT * FROM &mon_schema.&ma_table;
/*Drop de l'ancienne table*/
DROP TABLE &mon_schema.&ma_table;
/*Renommage de la nouvelle table*/
ALTER TABLE &mon_schema.&ma_table_new RENAME TO &mon_schema.&ma_table;


/*Recréation des contraintes*/
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND
 constraint_type='P';
 SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='R';
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT', constraint_name, owner) from all_constraints WHERE owner='&mon_schema' AND table_name='&ma_table' AND constraint_type='C';
/*Recréation des index*/
SELECT DBMS_METADATA.GET_DDL('INDEX', index_name, owner) from all_indexes WHERE owner='&mon_schema' AND table_name='&ma_table' AND NOT (index_name LIKE 'PK_%' OR uniqueness='UNIQUE');
/*Recréation des triggers*/
SELECT DBMS_METADATA.GET_DDL('TRIGGER', trigger_name, owner) from all_triggers WHERE owner='&mon_schema' AND table_name='&ma_table';

La démarche

La démarche permettant d'obtenir des objets vraiment corrects avec les bons noms est donc la suivante :

Générer le script
Dropper les triggers sur la table d'origine
Dropper les index sur la table d'origine
Dropper les contraintes sur la table d'origine
Créer la nouvelle table à partir de la table d'origine
Dropper l'ancienne table
Renommer la nouvelle table
Recréer les contraintes sur la nouvelle table
Recréer les index sur la nouvelle table
Recréer les triggers sur la nouvelle table

Pour aller plus loin

La doc Oracle sur le package dbms_metadata : http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm

Mon article sur comment dropper une table et ses contraintes associés sans faire un cascade : http://laetitia-avrot.blogspot.fr/2012/12/dropper-une-table-sans-cascade-ora-02449.html

Dropper une table sans cascade (ORA-02449)

J'ai un collègue développeur qui a décidé de coder tous les champs texte en clob das une seule table. Forcément, les performances ont commencé à en pâtir. Quand les données ont dues être intégrées à une base décisionnelle, ça a été un désastre.

Ebauche de solution

J'ai donc réfléchi avec lui pour transformer mes clobs en varchar2...
Malheureusement, Oracle ne permet pas de faire un ALTER TABLE pour modifier un champd de type clob en champ de type varchar2, même en mettant la fonction de conversion.
J'ai donc créé une nouvelle table (oui, l'ordre des colonnes était important car il y avait du SQL Loader impacté) et voulu dropper la table d'origine pour renommer la nouvelle...

ORA-02449: unique/primary keys in table referenced by foreign keys

Oracle est sympa sur ce coup-là : il nous donne un sacré coup de main dans les précisions :

Cause: An attempt was made to drop a table with unique or primary keys referenced by foreign keys in another table.
Action:  Before performing the above operations the table, drop the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

Bref, vous avez oublié de supprimer les contraintes liées à votre table.

La requête qui tue

J'ai donc concocté cette requête magique (oui, pas de user_constraints, mais dba_constraints car nous n'utilisons jamais les users applicatifs quand des humains se connectent) :

SELECT 'ALTER TABLE '||c.owner||'.'||c.table_name||' DROP CONSTRAINT '||c.constraint_name||';'
FROM dba_constraints p
JOIN dba_constraints c ON p.constraint_name=c.r_constraint_name
  AND p.owner=c.owner
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
AND p.table_name = '&ma_table'
AND p.owner='&mon_schema';

J'ai donc pu tranquillement dropper mes contraintes une par une pour dropper ma table.

Pour aller plus loin

La doc Oracle sur l'ORA-02449 : http://docs.oracle.com/cd/E11882_01/server.112/e17766/e2100.htm#sthref1269

Setter $ORACLE_SID avant de lancer un script (ORA-12162)

Sous Unix, je suis tombée sur un cas assez bizarre : mon script shell devait se connecter à une base via SQL Loader pour faire un import de données mais me sortait une ORA-12162: TNS:net service name is incorrectly specified...

ORA-12162: TNS:net service name is incorrectly specified

La signification de cette erreur est très claire : la variable $ORACLE_SID est mal renseignée... Mais je l'avais bien fait pourtant!

Le phénomène bizarre

Je me suis alors rendue compte que sans export, le script plantait alors qu'avec un export, il ne plantait pas. Exemple : (Ma base s'appelle Matalina)

>lancesqlload.sh
ORACLE_SID=MATALINA
echo $ORACLE_SID : MATALINA
ORA-12162: TNS:net service name is incorrectly specified
>
>lancesqlload_avecExport.sh
export ORACLE_SID=MATALINA
echo $ORACLE_SID : MATALINA
Import Succeed!
>

Je suppose que ce fonctionnement qui me semble bizarre est du au fait qu'un shell s'exécute dans son propre environnement et qu'il a dont besoin de l'export pour prendre en compte les variables ?

Pour aller plus loin

La doc Oracle sur l'ORA-12162 : http://docs.oracle.com/cd/E11882_01/server.112/e17766/net12150.htm#sthref3401

Mais qui bloque ma table ? (ORA-00054)

Grrr rien de pire que de créer une nouvelle table et d'avoir une ORA-00054 au moment de créer les clés trangères...

Recherche des infos sur la session bloquante

Il suffit de jouer cette requête :

SELECT ao.object_id,
  ao.owner,
  ao.object_name,
  lo.session_id,
  s.osuser,
  s.machine,
  s.program
FROM dba_objects ao 
  INNER JOIN v$locked_object lo ON ao.object_id=lo.object_id
  INNER JOIN v$session s ON lo.session_id = s.sid
WHERE object_name='mon_objet';

Il suffit ensuite de décrocher son téléphone et de demander des comptes à la personne qui bloque le truc (si c'est un script, je vous laisse galérer tout seul dans votre coin :-)).

Killer une session quel que soit l'OS

Hé oui, ça peut paraître bête, mais j'ai tellement l'habitude de killer les sessions qui m'embêtent sous Unix que je me suis retrouvée comme une conne sous MS-DOS!

Recherche de la session à killer

Cette partie est la même quel que soit l'OS.
Il suffit de jouer cette requête :

SET LINESIZE 100
COLUMN spid FORMAT A10
COLUMN username FORMAT A10
COLUMN program FORMAT A45

SELECT s.inst_id,
       s.sid,
       s.serial#,
       p.spid,
       s.username,
       s.program
FROM   gv$session s
       JOIN gv$process p ON p.addr = s.paddr 
                        AND p.inst_id = s.inst_id
WHERE  s.type != 'BACKGROUND';

Le résultat sera de ce genre :


   INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM 
---------- ---------- ---------- ---------- ---------- --------------------------------------------- 
         1        150         17 6116       DBSNMP     emagent.exe 
         1         92        120 1384       XXX        SQL Developer 
         1         16         68 6104       DBSNMP     emagent.exe 
         1         32        105 4616       SYS        sqlplus.exe 
         1        124         22 5684       DBSNMP     emagent.exe

Killer la session

Toujours sous sqlplus, vous pouvez alors killer cette session. La syntaxe est :

ALTER SYSTEM KILL SESSION 'sid,serial#';

Ce qui dans mon exemple donne :

ALTER SYSTEM KILL SESSION '92,120';

SystÞme modifiÚ.

Oui, la console sous MS-DOS affiche toujours des caractères bizarres, mais Oracle n'a visiblement pas l'intention de le corriger.

Pour aller plus loin

Voici le livre Oracle avec la syntaxe du ALTER SESSION : http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2013.htm#i2231814

Résoudre l'ORA-00020

Lors d'un import datapump, je me suis retrouvée face à l'ORA-00020.

L'erreur

Voici les insultes proférées par Oracle pour avoir tenté un import datapump sans avoir les ressources processeur nécessaire :

ORA-31626: la tÔche n'existe pas
ORA-31687: erreur lors de la crÚation du processus esclave  avec l'ID de processus esclave 1
ORA-31687: erreur lors de la crÚation du processus esclave DW01 avec l'ID de processus esclave 1
ORA-39106: Le processus esclave DW01 a ÚchouÚ lors du dÚmarrage. Erreur du processus esclave :
ORA-00020: nombre maximum de processus () atteint

(On remarquera au passage les jolis caractères bizarres liés à une mauvaise utilisation par Oracle du jeu de caractères MS-DOS, encore une raison de préférer les bases en anglais : mieux vaut du bon anglais que du mauvais français...)

Ce que ça veut dire

Voilà l'explication Oracle :

ORA-00020: maximum number of processes (string) exceeded

Cause: All process state objects are in use.

Action: Increase the value of the PROCESSES initialization parameter.

Oracle est tout bêtement en train de dire qu'on lui a alloué un certain nombre de processus et qu'il les a tous mangés!

Première solution

Mon import correspondant à une "restore" (oui, je suis obtue, mais pour moi seul rman fait une vraie restore) totale des données, j'ai demandé à mon intégrateur préféré de couper le serveur d'application. ça a permis de libérer des processus pour faire passer mon import.

Deuxième solution

Si vous ne pouvez pas couper de connexion, vous pouvez tout aussi bien augmenter le nombre de processus disponibles pour cette instance :

SQL> select name, value from v$parameter where name = 'processes';

NAME 
-------------------------------------------------------------------------------- 
VALUE 
-------------------------------------------------------------------------------- 
processes 
150 
SQL> alter system set processes=300 scope=spfile; 
 
SystÞme modifiÚ. 
 
SQL> shutdown immediate 
Base de donnÚes fermÚe. 
Base de donnÚes dÚmontÚe. 
Instance ORACLE arrÛtÚe. 
SQL> startup 
Instance ORACLE lancÚe. 
 
Total System Global Area 1073741824 bytes 
Fixed Size                  1300756 bytes 
Variable Size             427820780 bytes 
Database Buffers          629145600 bytes 
Redo Buffers               15474688 bytes 
Base de donnÚes montÚe. 
Base de donnÚes ouverte.

On remarquera encore la bonne gestion part Oracle des caractères accentués sous Windows

Pour aller plus loin

Le livre des erreurs Oracle : http://docs.oracle.com/cd/E11882_01/server.112/e17766/e0.htm#sthref15

Plan d'exécution

Il existe plusieurs manières de récupérer un plan d'exécution sous Oracle.

Autotrace only

Cette méthode est ma préférée. Elle aurait cependant l'inconvénient de nécessiter un exécution complète de la requête pour afficher le plan d'exécution. Je n'ai jamais rencontré de problème de lenteur avec cette méthode.
Il suffit d'ajouter la commande autotrace only pour qu'au lieu d'exécuter les requêtes, Oracle nous affiche le plan d'exécution. Je vous laisse faire les set linesize qui vont bien avant.

SET AUTOTRACE TRACEONLY EXPLAIN

Plan_table

Avec cette méthode, on demande la création du plan d'exécution (en lui donnant un id, tant qu'à faire). Le plan d'exécution est ensuite stocké dans la table plan_table. On peut donc l'afficher avec du SQL:

EXPLAIN PLAN SET statement_id='MonId' FOR SELECT * FROM maTable;
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','MonId',null));

Et voilà vous avez votre plan d'exécution!

Blog

Le script pour générer le script

La démarche

Pour aller plus loin

Ebauche de solution

ORA-02449: unique/primary keys in table referenced by foreign keys

La requête qui tue

Pour aller plus loin

ORA-12162: TNS:net service name is incorrectly specified

Le phénomène bizarre

Pour aller plus loin

Recherche des infos sur la session bloquante

Recherche de la session à killer

Killer la session

Pour aller plus loin

L'erreur

Ce que ça veut dire

Première solution

Deuxième solution

Pour aller plus loin

Autotrace only

Plan_table