Oracle 12c IOTC Error

[former_member204618]

Hi All,

Further to my other post which I have now resolved, I've run into another error :-

ORA-12089: cannot online redefine table "SAPSR3"."SCPRSVALS" with no primary key
ORA-06512: at "SAPSR3.IOTC", line 619
ORA-06512: at line 10
Processing the 10 largest tables
Processing table SAPSR3.SCPRSVALS
0:: Error_Stack...
0:: ORA-12089: cannot online redefine table "SAPSR3"."SCPRSVALS" with no primary key
0:: 
0:: Error_Backtrace...
0:: ORA-06512: at "SYS.DBMS_REDEFINITION", line 180
0:: ORA-06512: at "SYS.DBMS_REDEFINITION", line 3780
0:: ORA-06512: at "SAPSR3.IOTC", line 718
0:: 
0:: Fatal error - See preceding lines
0:: Error_Stack...
0:: ORA-12089: cannot online redefine table "SAPSR3"."SCPRSVALS" with no primary key
0:: 
0:: Error_Backtrace...
0:: ORA-06512: at "SAPSR3.IOTC", line 844
0:: ORA-06512: at "SAPSR3.IOTC", line 601
0:: 
0:: Fatal error - See preceding lines

Command I ran was iotc.convert (10,4)

I've check this table and it definitely has a primary key, so I am a little unsure of what it's complaining about.

But when viewing the definition using describe I see something different between 2 environments for the same table.

DEV

SQL> describe SCPRSVALS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                  VARCHAR2(96)
 VERSION                                      VARCHAR2(3)
 TABLENAME                                 VARCHAR2(90)
 RECNUMBER                               VARCHAR2(30)
 FIELDNAME                                  VARCHAR2(90)
 FLAG                                             VARCHAR2(9)
 VALUE                                           VARCHAR2(765)

UAT

SQL> describe SCPRSVALS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL VARCHAR2(96)
 VERSION                                   NOT NULL VARCHAR2(3)
 TABLENAME                                 NOT NULL VARCHAR2(90)
 RECNUMBER                                 NOT NULL VARCHAR2(30)
 FIELDNAME                                 NOT NULL VARCHAR2(90)
 FLAG                                      NOT NULL VARCHAR2(9)
 VALUE                                     NOT NULL VARCHAR2(765)

Suggestions?

Thansk

Craig

[Former Member]

Craig,

do you have any idea why the two environments are different?

The columns which are part of the primary key need to be NOT NULL defined.

Regards

Joern

[former_member204618]

Hi Joem,

I've not got a clue why they are different, wouldn't have known about this except for this error I encountered.

Cheers

Craig

[Former Member]

Craig,

i think you should just change them to NOT NULL, as otherwise you cannot convert to an IOT.

Joern

[former_member204618]

Hi Joem,

How would I do that?

The only thing that I have done recently is to use advanced compression on some of the largest tables this must have been one of them.

There is a check constraint on the columns for IS NOT NULL but the NULLABLE field says Yes!

This is DEV

  CREATE TABLE "SAPSR3"."SCPRSVALS"
   ( "ID" VARCHAR2(96 BYTE) DEFAULT ' ' CONSTRAINT "SYS_C00772943" NOT NULL ENABLE NOVALIDATE,
  "VERSION" VARCHAR2(3 BYTE) DEFAULT ' ' CONSTRAINT "SYS_C00772944" NOT NULL ENABLE NOVALIDATE,
  "TABLENAME" VARCHAR2(90 BYTE) DEFAULT ' ' CONSTRAINT "SYS_C00772945" NOT NULL ENABLE NOVALIDATE,
  "RECNUMBER" VARCHAR2(30 BYTE) DEFAULT ' ' CONSTRAINT "SYS_C00772946" NOT NULL ENABLE NOVALIDATE,
  "FIELDNAME" VARCHAR2(90 BYTE) DEFAULT ' ' CONSTRAINT "SYS_C00772947" NOT NULL ENABLE NOVALIDATE,
  "FLAG" VARCHAR2(9 BYTE) DEFAULT ' ' CONSTRAINT "SYS_C00772948" NOT NULL ENABLE NOVALIDATE,
  "VALUE" VARCHAR2(765 BYTE) DEFAULT ' ' CONSTRAINT "SYS_C00772949" NOT NULL ENABLE NOVALIDATE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
ROW STORE COMPRESS ADVANCED LOGGING
  STORAGE(INITIAL 1745371136 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSAPSR3700" ;


  CREATE UNIQUE INDEX "SAPSR3"."SCPRSVALS~0" ON "SAPSR3"."SCPRSVALS" ("ID", "VERSION", "TABLENAME", "RECNUMBER", "FIELDNAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS COMPRESS ADVANCED LOW
  STORAGE(INITIAL 1402445824 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSAPSR3700" ;

This is UAT - no advanced compression applied here yet!

  CREATE TABLE "SAPSR3"."SCPRSVALS"
   ( "ID" VARCHAR2(96 BYTE) DEFAULT ' ' NOT NULL ENABLE,
  "VERSION" VARCHAR2(3 BYTE) DEFAULT ' ' NOT NULL ENABLE,
  "TABLENAME" VARCHAR2(90 BYTE) DEFAULT ' ' NOT NULL ENABLE,
  "RECNUMBER" VARCHAR2(30 BYTE) DEFAULT ' ' NOT NULL ENABLE,
  "FIELDNAME" VARCHAR2(90 BYTE) DEFAULT ' ' NOT NULL ENABLE,
  "FLAG" VARCHAR2(9 BYTE) DEFAULT ' ' NOT NULL ENABLE,
  "VALUE" VARCHAR2(765 BYTE) DEFAULT ' ' NOT NULL ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
  STORAGE(INITIAL 1745371136 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSAPSR3700" ;


  CREATE UNIQUE INDEX "SAPSR3"."SCPRSVALS~0" ON "SAPSR3"."SCPRSVALS" ("ID", "VERSION", "TABLENAME", "RECNUMBER", "FIELDNAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 1402445824 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSAPSR3700" ;

Cheers

Craig

UPDATE

I've just checked a few other tables that got compressed and they also exhibit the change in NULLABLE field from No to Yes!

[former_member204618]

Joern Bartels wrote:

Craig,

i think you should just change them to NOT NULL, as otherwise you cannot convert to an IOT.

Joern

Hi Joem,

I tried to make the column not null with :-

alter table
   SCPRSVALS
modify
   (ID NOT NULL);

But get this error :-

Error starting at line : 1 in command -
alter table
   SCPRSVALS
modify
   (ID NOT NULL)
Error report -
SQL Error: ORA-01442: column to be modified to NOT NULL is already NOT NULL
01442. 00000 -  "column to be modified to NOT NULL is already NOT NULL"
*Cause:   
*Action:

I'm confused as describe shows :-

Name      Null Type         
--------- ---- -------------
ID             VARCHAR2(96) 
VERSION        VARCHAR2(3)  
TABLENAME      VARCHAR2(90) 
RECNUMBER      VARCHAR2(30) 
FIELDNAME      VARCHAR2(90) 
FLAG           VARCHAR2(9)  
VALUE          VARCHAR2(765)

What gives?

Cheers

Craig

UPDATE

OK I figured out that the problem was that the primary key constraint is missing.  So I tried to put it back :-

Error starting at line : 17 in command -
ALTER TABLE SCPRSVALS
ADD CONSTRAINT "SCPRSVALS~0" PRIMARY KEY 
(
  ID 
, VERSION 
, TABLENAME 
, RECNUMBER 
, FIELDNAME 
)
USING INDEX SCPRSVALS~0
ENABLE
Error report -
SQL Error: ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.
*Action:

Only to be greeted with the above error message!

It's almost like it doesn't like the tilde (~) symbol in the constraint name, but that's the name in UAT!

Any ideas?

Thanks

Craig

[Former Member]

Hello Craig,

please open a call with SAP on that issue.

Regards

Joern

[former_member204618]

Never mind I managed to get the primary key constraint created has to simply use "USING INDEX" rather than specifying the index name.

But I have now found that some but not all (I am still checking) of the tables I used reorg'd to use Advanced Compression have lost their Primary Constraints also!

I am beginning to suspect there is a bug with brtools somewhere.

[Former Member]

Craig,

another reason to open a call with SAP.

Joern

[former_member204618]

Hi Joem,

I am a little confused, I recreated the primary key constraint from the details taken from UAT and I was able to apply IOT.  However I ended up with other errors that I have raised with SAP.

Problem now is I didn't think to check production and training.  Production and training (these haven't yet been patched from SPS3 to SPS17 or had upgrade to Oracle 12c)  don't have any primary key constraints for these tables either!

These systems are running ERP 6.0 EHP2 SPS17 except as mentioned prod and training due to be patched end of this month.  I've just checked another of our systems running ERP 6.0 EHP4 SPS17 and they too don't have and any primary key constraints on these tables!  So my concern now is how come UAT had them, when I haven't done anything to them yet and DEV didn't which is why I got the error, but production doesn't have them anyway?

Should I remove the primary key constraint and revert the tables back?

Cheers

Craig