ORA-02431: Cannot disable constraint

Recently a question came up on the otn forum which reminded me to write a small blog article that I will be referring to instead of creating a different test each time I see people asking how to trouble shoot the same error as that mentioned by the Original Poster(OP). The OP was struggling about an existing constraint which, despite he is seeing it via a select against user_constraints table, he, nevertheless, was unable to disable it because of ORA-02431 error: cannot disable constraint FK_Batch_Products no such constraint.
Here below is the select against the OP user_constraints table:

CONSTRAINT_NAME             CONSTRAINT_TYPE      STATUS
--------------------------- ------------------ -----------
FK_Product_SourceSpecies    R                  ENABLED
FK_Product_CreatePerson     R                  ENABLED
FK_Product_ModifyPerson     R                  ENABLED
FK_Product_ExpressionSystem R                  ENABLED
FK_Product_Localisation     R                  ENABLED
FK_Batch_Products           R                  ENABLED

Have you already spotted the obvious?

Well if not then let me tell you one thing:  each time I see lowercase letters in an Oracle object names then I am hundred percent sure that the owner of this object will have trouble identifying those objects and I will not be surprised when he will be faced to such a kind of non existing object error.

Below I have modeled the problem and have shown the solution to the OP.

SQL> create table t (id number, vc varchar2(10));

Table created.

SQL> alter table t add constraint t_pk primary key (id);

Table altered.

SQL> alter table t add constraint "t_lower_case" check (vc != 'NONE');

Table altered.

SQL> select table_name, constraint_name
  2  from user_constraints
  3  where table_name = 'T';

TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
T                              T_PK
T                              t_lower_case

SQL> alter table t drop constraint t_lower_case;
alter table t drop constraint t_lower_case
                              *
ERROR at line 1:
ORA-02443: Cannot drop constraint  - nonexistent constraint

SQL> alter table t drop constraint "t_lower_case";

Table altered.

Bottom Line : it is very important to be careful when creating Oracle objects; give  them correct naming standard without enclosing their names between double quotes i.e. “ “

SQL Patch and SQL Plan Baseline how do they collaborate

In my continuing process of investigating SQL Patch and SQL Plan Baseline I wanted to know how these two technologies collaborate together. As I did in my preceding blog article I used the demo proposed by the optimizer group. Remember from the previous post that I have used package dbms_sqldiag_internal.i_create_patch in order to inject an index hint into a packaged query and have obtained the following execution plan

SQL>  Select count(*), max(emp1no)
  2  From   (Select *
  3          From   emp1
  4          Where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

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

-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|    
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |   912 (100)|          
|   1 |  SORT AGGREGATE              |         |     1 |    16 |            |          
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |  1484K|   912   (1)|
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   (2)| 
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement

And then I decided to let the optimizer capturing my sql baseline

SQL> alter session set optimizer_use_sql_plan_baselines=TRUE;

Session altered.

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

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

------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost 
------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |   912 
|   1 |  SORT AGGREGATE              |         |     1 |     8 |            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912   
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   
-----------------------------------------------------------------------

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

   3 - access("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement
   - SQL plan baseline SQL_PLAN_fynfh13fg894pda1b6c69 used for this statement

My packaged query is now using a SQL patch named “index_patch” which has been captured by a SQL baseline as shown below:

SQL>  select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_fynfh13fg894pda1b6c69'));

---------------------------------------------------------------------------
SQL handle: SYS_SQL_ef51d008dcf42495
SQL text: Select count(*), max(emp1no) From   (Select * From   emp1 Where  deptno= 10)
---------------------------------------------------------------------------

---------------------------------------------------------------------------
Plan name: SQL_PLAN_fynfh13fg894pda1b6c69         Plan id: 3659230313
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
---------------------------------------------------------------------------

Plan hash value: 20205423
---------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost 
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |     8 |   912   
|   1 |  SORT AGGREGATE              |         |     1 |     8 |            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912   
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   
---------------------------------------------------------------------------

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

   3 - access("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement

What if I disable the SQL patch “index_patch”? Will my query still use the hinted index ?

SQL> begin
  2          dbms_sqldiag.alter_sql_patch( name =>  'index_patch'
  3                                       ,attribute_name => 'STATUS'
  4                                       ,value =>  'DISABLED'
  5                                       );
  6          end;
  7    /

PL/SQL procedure successfully completed.

SQL> select
  2         name
  3        ,status
  4   from
  5     dba_sql_patches
  6    where name = 'index_patch';

NAME                           STATUS
------------------------------ --------
index_patch                    DISABLED

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000
SQL> select * from table(dbms_xplan.display_cursor);

----------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost 
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |   912 
|   1 |  SORT AGGREGATE              |         |     1 |     8 |            
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912   
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   
-----------------------------------------------------------------------

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

   3 - access("DEPTNO"=10)

Note
-----
   - SQL plan baseline SQL_PLAN_fynfh13fg894pda1b6c69 used for this statement

Wonderful!!! My SQL plan baseline is still using the index despite I have disabled the SQL patch which, don’t forget, is the technology I have used to inject the hint.

Let me now drop this “index_patch” SQL patch

SQL> begin
  2    sys.dbms_sqldiag.drop_sql_patch('index_patch');
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL> select
  2         name
  3        ,status
  4   from
  5     dba_sql_patches
  6    where name = 'index_patch';

no rows selected

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP1 | 95000 |   742K|   241   (2)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)

See how the index is not used anymore. Frankly speaking, I don’t understand what disabling a SQL PATCH means!!!

SQL> select
  2        plan_name,
  3        origin,
  4        enabled,
  5        accepted,
  6        fixed
  7      from dba_sql_plan_baselines
  8  where sql_text like '%emp1%';

PLAN_NAME                      ORIGIN         ENA ACC FIX
------------------------------ -------------- --- --- ---
SQL_PLAN_fynfh13fg894pda1b6c69 AUTO-CAPTURE   YES YES NO
SQL_PLAN_fynfh13fg894p443f3a3e AUTO-CAPTURE   YES NO  NO

My SQL Plan baseline is still enabled and accepted but is not reproducible because it has been based on a SQL patch that doesn’t exist anymore

SQL> select * from table(dbms_xplan.display_sql_plan_baseline(plan_name =>'SQL_PLAN_fynfh13fg894pda1b6c69'));

---------------------------------------------------------------------------
Plan name: SQL_PLAN_fynfh13fg894pda1b6c69         Plan id: 3659230313
Enabled: YES     Fixed: NO      Accepted: YES     Origin: AUTO-CAPTURE
---------------------------------------------------------------------------

Plan hash value: 20205423

----------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost
----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |     8 |   912
|   1 |  SORT AGGREGATE              |         |     1 |     8 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |   742K|   912
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188
----------------------------------------------------------------------

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

   3 - access("DEPTNO"=10)

So in summary when a SQL baseline uses a SQL patch to produce a best plan then the reproducibility of this SQL baseline depends on this SQL patch exactly as it depends on any other database object like index or table. Disabling the underlying SQL patch has no effect on the reproducibility of the SQL PLAN baseline while dropping the SQL patch will definitely impeach the parent baseline to be selected by the Optimizer

SQL PATCH and invisible index

I was playing with the demo presented in the oracle optimizer blog on how to use SQL path to inject a hint into a packaged application and then decided to extend it a little bit to see how this SQL patch will react if I make invisible the index used in the hint. You can get the script I used directly from the optimizer group blog.

Suppose you have the following query into a packaged application which does’nt allow any SQL code alteration

SQL>  explain plan for
  2  Select count(*), max(emp1no)
  3  From   (Select *
  4          From   emp1
  5          Where  deptno = 10);

Explained.

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    16 |   241   (2)| 00:00:03 |
|   1 |  SORT AGGREGATE    |      |     1 |    16 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP1 | 95000 |  1484K|   241   (2)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEPTNO"=10) 

And you want to access the EMP table via its index EMP1_I1. As explained in the optimizer blog article, you can use the dbms_sqldiag_internal.i_create_patch as follows:

SQL>  begin
  2    sys.dbms_sqldiag_internal.i_create_patch(
  3        sql_text  => 'Select count(*), max(emp1no) From   (Select * From   emp1 Where  deptno = 10)',
  4        hint_text => 'INDEX(@SEL$2 emp1)',
  5        name      => 'index_patch');
  6  end;
  7  /

SQL>  Select count(*), max(emp1no)
  2  From   (Select *
  3          From   emp1
  4          Where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

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

-----------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)|    
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |       |       |   912 (100)|          
|   1 |  SORT AGGREGATE              |         |     1 |    16 |            |          
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP1    | 95000 |  1484K|   912   (1)|
|*  3 |    INDEX RANGE SCAN          | EMP1_I1 | 95000 |       |   188   (2)| 
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement

And the index is used without altering the packaged application.

But what will happen if we make the index EMP1_I1 invisible

SQL> alter index emp1_I1 invisible;

Index altered.

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP1 | 95000 |   742K|   241   (2)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement

The SQL patch is marked as being used but the hint which is supposed to add to the packaged query is not used. We have FULL TABLE ACCES SCAN instead.

That is, invisible index, represent one of the other reasons to look for when you note that, despite your SQL path is used, the hint is not applied

And wait!! What if I drop this index?

SQL> drop index emp1_i1;

Index dropped.

SQL> Select count(*), max(emp1no)
  2      From   (Select *
  3              From   emp1
  4              where  deptno = 10);

  COUNT(*) MAX(EMP1NO)
---------- -----------
     99900      100000

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

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   241 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| EMP1 | 95000 |   742K|   241   (2)| 00:00:03 |
---------------------------------------------------------------------------

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

   2 - filter("DEPTNO"=10)

Note
-----
   - SQL patch "index_patch" used for this statement

Definitely This Note about SQL patch which accompanies the execution plan is not to be always trusted. Something needs to be changed here. How could a non reproducible SQL PATH be marked as being used when it did not what it is supposed to do. May be the optimizer group should identify a non reproducible SQL path, marks it as such and display it into the dba_sql_patches table via a column such as reproducible (No/Yes)