vendredi 8 avril 2011

SQLPLUS: NEW_VALUE

Lorsque vous écrivez des scripts sous SQLPLUS vous pouvez utiliser la commande NEW_VALUE pour stocker la valeur d'une colonne dans une variable SQLPLUS.
La commande de raccourci est NEW_V.

Exemple:
SQL> col instance_name NEW_V inst_name
SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl11

SQL> select instance_number from v$instance where instance_name='&inst_name';
old   1: select instance_number from v$instance where instance_name='&inst_name'
new   1: select instance_number from v$instance where instance_name='orcl11'

INSTANCE_NUMBER
---------------
              1

Si la requête retourne plusieurs lignes alors c'est la dernière valeur qui sera sauvergardée dans la variable:
SQL> col table_name NEW_V t_name
SQL> select table_name from user_tables where rownum<=3;

TABLE_NAME
------------------------------
ICOL$
CON$
UNDO$

SQL> prompt &t_name
UNDO$

mardi 29 mars 2011

ORA-01031: insufficient privileges

Si vous utilisez une base oracle sous windows et que vous obtenez l'erreur ORA-01031 lorsque vous vous connectez en SYSDBA sans préciser de mot de passe, il y'a de fortes chances que le problème soit lié à la configuration de votre fichier SQLNET.ORA
C:\HOMEWARE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Mar 28 13:22:53 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01031: insufficient privileges

En effet lorsque le paramètre SQLNET.AUTHENTICATION_SERVICES de votre fichier SQLNET.ora est positionné à NONE ou pas positionné du tout (NONE est la valeur par défaut), alors vous ne pouvez pas vous connecter en SYSDBA sans préciser de mot de passe.
Pour préciser à oracle que l'authentification se fait par l'OS windows il faut mettre ce paramètre à NTS:
SQLNET.AUTHENTICATION_SERVICES = (NTS)

Ainsi, vous n'êtes plus obligé de préciser le mot de passe:
C:\HOMEWARE>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Mar 29 15:04:51 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

jeudi 24 mars 2011

ORA-00932: inconsistent datatypes: expected - got CLOB

Si vous tombez sur l’erreur ORA-00932 c’est que vous avez exécuté une requête contenant une colonne de type LOB dans la clause GROUP BY ce qui n’est absolument pas possible dans Oracle.

Voici un petit exemple très simple :
SQL> create table emp2 as select * from emp;

Table created.

SQL> alter table emp2 add (job_desc clob);

Table altered.

SQL> update emp2 set job_desc=to_clob(job);

14 rows updated.

SQL> commit;

Commit complete.

SQL> select job, count(1) from emp2 group by job order by 2 desc;

JOB         COUNT(1)
--------- ----------
CLERK              4
SALESMAN           4
MANAGER            3
ANALYST            2
PRESIDENT          1

SQL> select job_desc, count(1) from emp2 group by job_desc order by 2 desc;
select job_desc, count(1) from emp2 group by job_desc order by 2 desc
                                             *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected - got CLOB

Dans mon exemple je pourrais m’en sortir en convertissant mon champ CLOB en VARCHAR2. Ceci n’est possible bien sûr que si mon CLOB ne fait pas plus de 4000 octects :
SQL> col jd for A20
SQL> select to_char(job_desc) jd, count(1) from emp2 group by to_char(job_desc) order by 2 desc;

JD                     COUNT(1)
-------------------- ----------
CLERK                         4
SALESMAN                      4
MANAGER                       3
ANALYST                       2
PRESIDENT                     1

Cette restriction fait partie d’un ensemble de restrictions d’utilisation de la clause GROUP BY. Ces restrictions sont listées dans la doc oracle « SQL Language reference » (chapitre 19) :
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10002.htm#i2065646

This clause is subject to the following restrictions:
• You cannot specify LOB columns, nested tables, or varrays as part of expr.
• The expressions can be of any form except scalar subquery expressions.
• If the group_by_clause references any object type columns, then the query will not be parallelized.

mercredi 9 mars 2011

Ignorer les hints ( _OPTIMIZER_IGNORE_HINTS)

A la question "est-il possible de dire au CBO d’ignorer les hints ? " la réponse est OUI.
Il existe en effet un paramètre caché nommé _OPTIMZER_IGNORE_HINTS qui par défaut est à FALSE et qui peut être mis à TRUE si l’on veut que l’optimiseur ne prenne pas en considération lors du parsing les hints contenus dans les requêtes SQL.

Cette astuce peut être utile notamment après un upgrade de votre base Oracle pour déterminer si les hints présents dans la version précédente de la base sont toujours nécessaires dans la nouvelle version. En combinant cette astuce avec SQL Performance Analyzer (fonctionnalité du Real Application Testing disponible en 11g seulement) vous pouvez très rapidement déterminer si en supprimant les hints les requêtes s’exécutent mieux ou moins bien après le passage en 11g.

Exemple:

Voici un petit test case très simple permettant de constater l' efficacité du paramètre _OPTIMZER_IGNORE_HINTS :

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> create index t1_idx on t1(object_id);

Index created.

SQL> select count(1) from t1;

  COUNT(1)
----------
     50574

SQL> exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> explain plan for select * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)


SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=20)

13 rows selected.


L’exemple précédent montre que le hint FULL(T1) force le Full Table Scan.
Sans le hint le CBO choisit un accès indexé.

Activons maintenant le paramètre _OPTIMZER_IGNORE_HINTS au niveau de la session:
SQL> alter session set "_optimizer_ignore_hints"=TRUE;

Session altered.

SQL> explain plan for select /*+ full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)

On s’aperçoit que le CBO a bien ignoré le hint qui pourtant fonctionnait bien lorsque le paramètre caché _OPTIMZER_IGNORE_HINTS était à sa valeur par défaut à savoir FALSE.

On peut aussi désactiver ce paramètre uniquement au niveau de la requête via un hint OPT_PARAM.
On a donc là un hint permettant de désactiver les hints. N’est-ce pas magnifique ?

SQL> explain plan for select /*+ opt_param('_optimizer_ignore_hints' 'TRUE') full(t1) */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    93 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1     |     1 |    93 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OBJECT_ID"=20)


Si on met le OPT_PARAM après le hint FULL, le hint n’est alors pas ignoré. J’avoue avoir été surpris lorsque j’ai fait le test :

SQL> explain plan for select /*+ full(t1) opt_param('_optimizer_ignore_hints' 'TRUE') */ * from t1 where object_id=20;

Explained.

SQL> select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    93 |   157   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    93 |   157   (2)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_ID"=20)

lundi 7 mars 2011

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

L'erreur ORA-02449 indique simplement que vous tenter de dropper une table qui est référencée par une clé étrangère d'une autre table.

Pour résoudre ce problème il faudrait supprimer ces Foreign Key (FK) avant de dropper la table, mais comment déterminer les tables qui référencent cette Foreign Key ?

La requête suivante peut vous permettre de répondre à cette question:

select * from user_constraints 
where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name='NOM_TABLE_A_DROPPER') 
and CONSTRAINT_TYPE='R'; 

Le type de contrainte “R” indique qu’il s’agit d’une FK
La colonne R_CONSTRAINT_NAME contient le nom de la Primary Key référencée par la FK.

Voici un petit exemple :
SQL>  CREATE TABLE PAYS
  2      (      id_pays         numeric(10)     not null,
  3             nom_pays        varchar2(50)    not null,
  4             supplier_id     numeric(10)     not null,
  5             CONSTRAINT pays_pk PRIMARY KEY (id_pays)
  6      );

Table created.

SQL> CREATE TABLE CLIENT
  2      (      id_client       numeric(10)     not null,
  3             nom_client      varchar2(50)    not null,
  4             id_pays         numeric(10)     not null,
  5             CONSTRAINT client_pk PRIMARY KEY (id_client),
  6  CONSTRAINT fk_pays
  7               FOREIGN KEY (id_pays)
  8               REFERENCES PAYS(id_pays)
  9      );

Table created.

Si j’essaie de dropper la table PAYS je tombe sur l’erreur ORA-02449 :
SQL> drop table pays;
drop table pays
           *
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys


La requête suivante m’indique le nom de la foreign key à dropper pour pouvoir dropper ma table :
SQL> select table_name,CONSTRAINT_NAME from user_constraints
  2  where R_CONSTRAINT_NAME in (select CONSTRAINT_NAME from user_constraints where table_name='PAYS')
  3  and CONSTRAINT_TYPE='R';

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
CLIENT                         FK_PAYS

La FK étant identifié je peux la dropper puis dropper ma table :

SQL> alter table CLIENT drop constraint FK_PAYS;

Table altered.

SQL> drop table pays;

Table dropped.


Si on veut vraiment supprimer la table sans se soucier des éventuels FK, on peut utiliser la clause CASCADE CONSTRAINTS de la commande DROP TABLE. Cette clause indique à Oracle de supprimer automatiquement toutes les FK qui référenceraient la table à dropper :
SQL> drop table pays cascade constraints;

Table dropped.