Knowledge Management

Question:
Hi,
Would really appreciate it if anyone could help resolve the following error i.e. while reorganising the unique index using sapdba 6.20. I did extend the tablespace while the first error message cam up, please detail all
instructions (thanks):
CREATE UNIQUE INDEX "SAPR3"."COEP______0" ON
"SAPR3"."COEP"("MANDT","KOKRS","BELNR","BUZEI") TABLESPACE "PSAPBTABI"
PCTFREE 10 INITR
ANS 2 MAXTRANS 255 STORAGE (INITIAL 450600K NEXT 512000K PCTINCREASE 0
MINEXTENTS 1 MAXEXTENTS 300) NOPARALLEL
ORA-01652: unable to extend temp segment by 64000 in tablespace PSAPBTABI
I have increased the tablespace space size by adding another 2 GB file and have also increased PSAPTEMP as well. The error I am getting now is about increasing tablespace PSAPUSER1D by 121 extents (not too sure why), secondly how do I increase the max extents on the tablespace and secondly why is it looking in there for the constraints
ALTER TABLE "SAPR3"."COEP" ADD (CONSTRAINT "COEP______0" PRIMARY KEY
("MANDT","KOKRS","BELNR","BUZEI"))
ORA-01630: max # extents (121) reached in temp segment in tablespace
PSAPUSER1D
The scripts are as follows, hope this helps:
Restart.rsi
Y /oracle/LI3/sapreorg/0305301535/droBTABI.sql
Y /oracle/LI3/sapreorg/0305301535/indBTABI.sql
N /oracle/LI3/sapreorg/0305301535/conBTABI.sql
droBTABI.sql
ALTER TABLE "SAPR3"."COEP"
DROP CONSTRAINT "COEP______0" CASCADE;
EXIT;
indBTABI.sql
CREATE UNIQUE INDEX "SAPR3"."COEP______0"
ON "SAPR3"."COEP"("MANDT","KOKRS","BELNR","BUZEI")
TABLESPACE "PSAPBTABI"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 450600K NEXT 512000K PCTINCREASE 0
/* USED SPACE 1986600K */ MINEXTENTS 1 MAXEXTENTS 300) NOPARALLEL;
conBTABI.sql
ALTER TABLE "SAPR3"."COEP"
ADD (CONSTRAINT "COEP______0"
PRIMARY KEY ("MANDT","KOKRS","BELNR","BUZEI")
USING INDEX TABLESPACE "PSAPBTABI"
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE (INITIAL 450600K NEXT 512000K
MINEXTENTS 1 MAXEXTENTS 300
PCTINCREASE 0));
Can I not startup SAP, and go into transaction SE11 and recreate this unique/primary index COEP______0.
Rgeards
SB
Answer:
hi,
U need to increase the maxextents for tablespace PSAPUSER1D
ALTER TABLESPACE PSAPUSER1D DEFAULT STORAGE ( MAXEXTENTS <extents no>);
Whenever index/constraint is created a temporary segment is allocated in the particular tablespace which is made permanent after the creation.
U can make the maxextents unlimited, but its always better have a limit...
rgds
nainesh