To do that we’ve to choose a different order for the columns in the index. We create a new index that provides the same uniqueness constraint (that all combinations of „mandt” and „name” have to be unique. Therefore, inserts/updates/deletes can happen as long as they do not require access to the corrupted blocks. Unfortunately, with a real index corruption the index is not automatically flagged UNUSABLE.
SQL> insert into pktest values ('100', 'Test1', 3) insert into pktest values ('100', 'Test1', 3) * ERROR at line 1: ORA-01502: index 'SAPR3.I_PKTEST' or partition of such index is in unusable state => as we see it does not work anymore. => Now let’s make the index broken... SQL> alter index i_pktest unusable Index altered. While this makes technically no difference, it is a semantic difference compared to a primary key constraint! SQL> commit Commit complete. Instead, the key columns all get the NOT NULL constraint and a unique index is defined on the key columns. => as we see the unique index takes care of our primary key constraint.īe aware that SAP does not create any primary key constraints as such on the database. SQL> insert into pktest values ('100', 'Test1', 3) insert into pktest values ('100', 'Test1', 3) * ERROR at line 1: ORA-00001: unique constraint (SAPR3.I_PKTEST) violated SQL> insert into pktest values ('100', 'Test3', 3) 1 row created. SQL> insert into pktest values ('100', 'Test2', 2) 1 row created. SQL> insert into pktest values ('100', 'Test1', 1) 1 row created. SQL> create unique index i_pktest on pktest (mandt, name) Index created. To explain my solution, I will make up a little example: SQL> create table pktest (mandt varchar2(3) not null, name varchar2(40) not null, id integer) Table created. The first one was still based on the assumption that for an index rebuild the old index has to be read. But actually it was the second solution I found. It is easy, it is safe – you should use it if necessary. Even a REBUILD ONLINE has it’s “dangers”). (Please do check the mentioned note before nevertheless. With this knowledge, it is now possible to perform the Index rebuild while SAP is up and running. When used with the ONLINE option the REBUILD accesses the table directly instead of the old index (an elaborate discussion on this can be found in Oracles Metalink: Note:278600.1) What the Oracle documentation does not mention is that the behaviour for a REBUILD ONLINE is quite different. So if the existing index is corrupt, we cannot use the REBUILD clause. Solution #1įirst, the nice and easy one, that now has found its way into the # 365481 – Block corruptions note:īecause the Oracle documentation says that for the REBUILD of an index, the necessary data is taken from the existing Index segment and not from the table (they changed that for Oracle 11g! See “14.1.7 Re-creating Indexes” ). To get around this, I made a little research and found two possible ways out. Ok, this is rather unlikely, but it is a potential risk – it is not bulletproof this procedure. It involves the risk, that – although the SAP is shutdown – duplicates keys can be entered into the table.(You can use DBMS_METADATA.GET_DDL, the EXP/IMP tools or the Oracle dictionary directly, just to mention three of the options to get the DDL for the index). Of course, it is no problem to do so, but you have to know how – and many do not.
He/She will have to find out the index-definition commands (DDL) before the index is dropped.
It requires the DBA to be handy with the Oracle Dictionary.We’re living in 2008 and still the recreation of a secondary data structure can only be done in a downtime!? Not too nice. There are several things that are not so nice about this: Stop your SAP-system to prevent the entry of duplicate keys (these are nasty to remove later on!).If it does, the standard recommendation to fix this was some procedure like this: This works fine, as long as the index structure is not corrupted. The index does not need to be a unique index, but it has to be possible for the database to look into it and find out, if a given key is already present or not. Primary key constraints in Oracle are enforced by using an index.