If you have used Datapump to migrate, duplicate or re-organise a schema, then you may have inadvertently introduced an application upgrade time bomb.

A primary key index (or unique key index) that was created by an “add constraint” statement, will be dropped by an “alter table xxx drop primary key” statement.  If the developers rely on this “lazy” method, then the SQL in their application upgrades may fail if the index was created in another way.  (Eg Datapump import, or manual DBA maintenance).

For example, the table, index and primary key constraint (with user specified names rather than system generated names) are created by the application scripts like this:

create table TEST as select * from dual;
alter table TEST add constraint TEST_PK primary key (dummy);

Later, Datapump (11.2.0.2.3) is used to move the schema to a new database.  Datapump creates the tables, then the indexes, before adding the constraints separately.  (Except when reference partitioning is used).

The Datapump SQL is equivalent to:

create table TEST as select * from dual;
create index TEST_PK on TEST(dummy);
alter table TEST add constraint TEST_PK primary key (dummy);

This works fine until the next application upgrade, when a column is to be added to the primary key:

SQL> alter table TEST add (dummy2 varchar2(100) default 'a' not null);

Table altered.

SQL> alter table TEST drop primary key;

Table altered.

SQL> alter table TEST add constraint TEST_PK primary key (dummy,dummy2);
alter table TEST add constraint TEST_PK primary key (dummy,dummy2)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object 

SQL> -- The application upgrade script failed
SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME                                                                                                                                                              
----------                                                                                                                                          
TEST_PK

The index wasn’t dropped, but it can no longer support the primary key, so the “add constraint” statement can’t create an appropriately named index to enforce the new primary key.

Prevention

  1. Developers could create objects using commands similar to Datapump, ie run “create index” explicitly
  2. Use the full syntax when dropping constraints and intending the index to be dropped too, eg
    alter table TEST drop primary key drop index;
  3. Manually recreate affected indexes and constraints as required after a Datapump import.  (Not always a quick task).
  4. After a Datapump import, manually set the index properties to make it seem that the index was created by an “add constraint” statement.  (This involves updating ind$, so this may not be considered appropriate for production environments).

Ind$.Property

These are the index property flags for a Oracle 11.2:

 /* unique : 0x01 */
 /* partitioned : 0x02 */
 /* reverse : 0x04 */
 /* compressed : 0x08 */
 /* functional : 0x10 */
 /* temporary table index: 0x20 */
 /* session-specific temporary table index: 0x40 */
 /* index on embedded adt: 0x80 */
 /* user said to check max length at runtime: 0x0100 */
 /* domain index on IOT: 0x0200 */
 /* join index : 0x0400 */
 /* system managed domain index : 0x0800 */
 /* The index was created by a constraint : 0x1000 */
 /* The index was created by create MV : 0x2000 */
 /* composite domain index : 0x8000 */

An “alter table xxx drop primary key” statement will drop the index too if the bits for 0x1001 are set (4097 in decimal; meaning ‘unique and created by a constraint’).  A Datapump import will lose the 0x1000 flag, so a DBA could generate statements to reinstate it by running this in the source database:

-- For Oracle 11.2
set linesize 300
spool ind_property_hack.sql
select 'update sys.ind$ set property=property+4096 
 where bitand(property,4096)=0
 and obj#= (
  select object_id
  from dba_objects do2
  where owner='''||do.owner||'''
  and object_name='''||do.object_name||''');'
from sys.ind$ i, dba_objects do
where bitand(property,4096)=4096
and i.obj# = do.object_id
and i.dataobj# = do.data_object_id
and do.owner not in (select distinct user_name from SYS.default_pwd$)
order by 1
/
spool off
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s