Left over LOB-Index after EHP7

[tobias_ptz]

Hello,

At the moment we have some ERP systems where we cannot delete the old PSAPR3700 tablespace, because there is a LOB-Index left over which I can't move to the new PSAPSR3740 tablespace.

SQL> select index_name from dba_indexes where tablespace_name='PSAPSR3700';

INDEX_NAME
------------------------------
SYS_IL0000041984C00004$$


SQL> select owner, table_name from dba_lobs where 'SYS_IL0000041984C00004$$'in (segment_name, index_name);

OWNER TABLE_NAME
------------------------------ ------------------------------
SAPSR3 DBMAPS
OWNER TABLE_NAME
------------------------------ ------------------------------
SAPSR3 DBMAPS

So the only way to drop the tablespace is:

drop tablespace PSAPSR3700 including contents and datafiles cascade constraints; 

But I don't know anything about this index, so I'm not sure if I can drop the tablespace including the left over content.

Any idea, how I can move this index, or if there is no way (I don't find any) how to create the Oracle index (it's not in the DDIC) in the new tablespace?

Thank you!

Best regards,

Tobias

[fidel_vales · Accepted solution]

do you have only a LOB index on that tablespace? that would be very weird.

A lob index is associated to a lob segment and to a table.

You have to find out the table it belong to

you need to check it in dba_lobs. something like

select table_name, column_name from dba_lobs where owner = <the owner of that index> and index_name = <your_index>

in theory that table should be located on the same tablespace, if not, then something (weird) happened

once you have found the table, then you can reorganize it outside this tablespace.

If the table is not with the lob, then the command given previously will not work. If it is on the same tablespace, then it will work

[tobias_ptz · Accepted solution]

Hi Fidel,

Thank you!

Yes, a select on the tables and indexes just displayed this index:

select table_name from dba_tables where tablespace_name='PSAPSR3XXX';
select index_name from dba_indexes where tablespace_name='PSAPSR3XXX';

SQL> select table_name, column_name from dba_lobs where owner = 'SAPSR3' and index_name = 'SYS_IL0000147097C00004$$';

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------

TESPO
VARDATA

SQL> select table_name, tablespace_name from dba_tables where table_name='TESPO';


TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TESPO PSAPSR3

So the TESPO table is on tablespace PSAPSR3, but the LOB is still on the PSAPSR3700 / PSAPSR3701 tabelspace.

Do you know the syntax of the command to rebuild the LOB index on the PSAPSR3 tablespace for the table TESPO?

alter index SAPSR3."SYS_IL0000147097C00004$$" rebuild tablespace PSAPSR3 compute statistics; 

Tried already the command above, but it doesn't work.

Thank you!

Best regards,

Tobias

[stefan_koehler · Accepted solution]

Hi Tobias,

the easiest way would be to generate the DDL statement with BR*Tools (brspace), modify the LOB storage clause and run the BR*Tools.

SAP documentation: http://help.sap.com/saphelp_NW70/helpdata/de/cb/376c1d44ec274daa70e5e07cfa09c6/content.htm

d|-ddl: specifies how the Data Definition Language (DDL) statements for tables to be reorganized are handled  
Syntax: -d|-ddl yes|no|first|only|only_tab|only_ind|only_dep
* yes is the default and specifies that the DDL statements are generated and saved immediately before a table is reorganized
* no specifies that the DDL statements are not saved – we do not recommend this normally, although there is a small performance gain
* first specifies that all DDL statements are generated before the reorganization begins. Processing then stops before the reorganization so that you can change the statements if required.

Oracle documentation: Database SecureFiles and Large Objects Developer's Guide - Contents

The system determines which tablespace to use for LOB data and LOB index depending on your specification in the LOB storage clause:

* If you do not specify a tablespace for the LOB data, then the tablespace of the table is used for the LOB data and index.

* If you specify a tablespace for the LOB data, then both the LOB data and index use the tablespace that was specified.

For example
CREATE TABLE ContainsLOB_tab (n NUMBER, c CLOB)  
      lob (c) STORE AS BASICFILE segname (TABLESPACE lobtbs1 CHUNK 4096 
                        PCTVERSION 5 
                        NOCACHE LOGGING 
                        STORAGE (MAXEXTENTS 5) 
                       ); 

>> Do you know the syntax of the command to rebuild the LOB index on the PSAPSR3 tablespace for the table TESPO? Tried already the command above, but it doesn't work.

You should not do this as it is LOB related - refer to the Oracle documentation "The LOB index is an internal structure that is strongly associated with LOB storage. This implies that a user may not drop the LOB index and rebuild it."

Regards

Stefan

[tobias_ptz · Accepted solution]

Hi Stefan,

Thanks for the hint!

But I'm a bit confused about the how ..

You said I have to generate the DDL statement with brspace, I guess for the table TESPO.

After that, I have to modify the created ddl.sql file to change the LOB storage to the right tablespace, right?

If I generate the DDL for the table TESPO, I get the following ddl.sql output:

/* #TABL SAPSR3.TESPO */

  CREATE TABLE "SAPSR3"."TESPO#$" 
  ( "TABNAME" VARCHAR2(10) DEFAULT ' ' NOT NULL ENABLE, 
"VARKEY" VARCHAR2(110) DEFAULT ' ' NOT NULL ENABLE, 
"DATALN" NUMBER(5,0) DEFAULT 0 NOT NULL ENABLE, 
"VARDATA" BLOB
  ) 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 16384 NEXT 655360 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSAPSR3" 
 LOB ("VARDATA") STORE AS SECUREFILE (
  TABLESPACE "PSAPSR3" ENABLE STORAGE IN ROW CHUNK 8192
  CACHE NOCOMPRESS KEEP_DUPLICATES ) ;

/* #STMT */

/* #INDX */

  CREATE UNIQUE INDEX "SAPSR3"."TESPO~0#$" ON "SAPSR3"."TESPO#$" ("TABNAME", "VARKEY") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 16384 NEXT 163840 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0)
  TABLESPACE "PSAPSR3" ;

/* #STMT */

/* #GRNT */

/* #CONS */

/* #RCON */

/* #TRIG */

/* #COMM */

/* #MLOG */

So if I start the reorg with that statement, it will create the table TESPO in PSAPSR3 and the LOB in PSAPSR3.

At this point I don't have to modify this statement, right? Because everything will be created in the PSAPSR3 tablespace.

Another question is, will this statement recreate the the existent table, or not. If yes, I have to export the table content, run the DDL statement and import the TESPO table back?

Thanks for your help!

Best regards,

Tobias

[stefan_koehler · Accepted solution]

Hi Tobias,

>> After that, I have to modify the created ddl.sql file to change the LOB storage to the right tablespace, right?

Yes - that is the right procedure.

>> At this point I don't have to modify this statement, right? Because everything will be created in the PSAPSR3 tablespace.

Yes - the previous generated DDL statement is already correct. The BR*Tools already replaced / set the right tablespace (based on your input parameters).

>> Another question is, will this statement recreate the the existent table, or not. If yes, I have to export the table content, run the DDL statement and import the TESPO table back?

The DDL creates an interim table "TESPO#$" and it is renamed to its original name (or better said both objects are switched) by DBMS_REDEFINITION (called by BR*Tools). You don't have to do anything - just run that reorg with brspace.

Regards

Stefan

[tobias_ptz · Accepted solution]

Hi Stefan,

Great, thank you for your help!

Now I could drop the old tablespace ..

Besten Dank!

Tobias

[Reagan]

I would move the contents from the old tablespace (PSAPSR3700) to the new tablespace (PSAPSR3740). You can use BRTools to move them.

brspace -c force -o process,time -u / -f tbreorg -a reorg -tablespace PSAPSR3700 -t "*" -parallel 1 -degree 1 -newts PSAPSR3740 -indts PSAPSR3740

Reagan

[tobias_ptz]

Hi Reagan,

Thank you for the answer, but the command doesn't work.

The wildcard <-t "*"> isn't right: [BR1107E Table(s) '*' not found].

Any idea?

Thanks!

Tobias

[Reagan]

Tobias,

Grrr, I overlooked it. You could drop them (index and finally the tablespace) and recreate the index the way Yves suggested or from Tx SE14.

Cheers

Reagan

[ACE-SAP]

Hi Benjamin,

I did delete my answer as in fact it is not possible to recreate a LOB index.

The DDL I get was incomplete, and while I was searching why, Stefan did provide the correct answer...

The only way to move a LOB index is to move the LOB itself

ALTER TABLE TESPO MOVE LOB(VARDATA) STORE AS (TABLESPACE PSAPSR3);

How to move lobsegment and lobindex to a diffe... | Oracle Community

Regards