5 Indexes are remained after completion of REORG to new tablespace

[Former Member]

1.I did REORG activity from 1 tablespace to new tablespace

2.objects have been moved successfully to new tablespace except 5 indexes.

3.i converted lob2lob and long2lob then i have done above activity.

Please suggest me to move those objects(indexes) to new tablespace via brspace command.

[former_member189725 · Accepted solution]

You need to recreate the indexes in the new tablespace . Since the tables in the old tablespace have been moved to a new tablespace , hence these indexes have become invalid due to change in the rowid .Hence recreate them using se11 and also check if these indexes are defined in the ABAP dictionary  from tcode SE11 . If not , check them if they are required or not . Then go ahead and recreate them. Before that delete the indexes from the old tablespace.

Can you list those 5 indexes here.

Regards

Ratnajit

[Former Member · Accepted solution]

HI Ratnajit Dey,

Thanks for your info and please find indexes as you informed .Let me suggest for further step after reviewing below indexes.

SEGMENT_NAME              SEGMENT_TYPE    OWNER                SIZE_MB

------------------------- --------------- ------------------------------ ----------

DD01L~2                         INDEX                     SAPRP1                                  2

SPROXDAT~OB1             INDEX                     SAPRP1                                  5

FUPARAREF~3               INDEX                     SAPRP1                                 52

FUPARAREF~4               INDEX                     SAPRP1                                 47

TDEVC~ENH                   INDEX                     SAPRP1                                 .5

[former_member189725 · Accepted solution]

Go to tcode SE14 , enter the table name DD01L , Edit.

Click on indexes , then check if the index DD01L~2 exists , click on the index .

If index DD01L~2 does not exist , just ignore the index creation . In that case drop the index from the database level.

Then click on storage parameters .

On the next screen click on For New Creation .

Select Current Database Parameters

Change the tablespace name to new one. Do not change any other parameter.

Save it.

Go back one screen .

First delete the database index . Then once done , create database index again . This will create the index in the new tablespace.

Regards

Ratnajit

[stefan_koehler · Accepted solution]

Hello Ratnajit,

Since the tables in the old tablespace have been moved to a new tablespace , hence these indexes have become invalid due to change in the rowid

... absolutely nonsense. As Narendra mentioned the reorg was done by BR*Tools. So the PL/SQL package DBMS_REDEFINITION was used. In this case there is no movement of the object at all. Basically it is about creating new database objects (with an insert and merge of all the data). The indexes can not become invalid, because they are (newly) created on the interim table, before the merge and the reorg of the table  is finished.

... and your suggestion by creating the index via SE14 will create a lock (if not specified otherwise).

@ Narendra: You can use brspace (function idrebuild with option "-newts") for that: http://help.sap.com/saphelp_nw70/helpdata/en/64/f73f7515a46049a51df47ee9f6bba8/content.htm

Regards

Stefan

[Former Member · Accepted solution]

HI Stefan,

i unable to find command to reorg the 5 indexes to new tablespace by the link,which u mentioned above.Please suggest me for further action .

[volker_borowski2 · Accepted solution]

Why didn't you try interactive?

brtools is menue driven

Drill down to segment management and reorganisation, you should be able to select index rebuild.

You can put all 5 indexes in as indexnames, comma-seperated.

If they are really big, consider to use 4 or 8 as parallel degree for rebuild, depending on available # of cpu.

Volker

[Former Member · Accepted solution]

Hi Volker Borowski,

I successfully exported to new TS as per the procedure,mentioned above by you.Thanks for accurate solution .

Have a nice time.