New tablespace for Indexes

[former_member204618]

Hi All,

We were having some performance problems on our 10G database and so our Oracle DBA asked me to create a new tablespace I named it PSAPSR3INDEX and rebuild a single tables indexes into it after he'd analysed the ADDM report.  So I have done this using BRTOOLS.

But now I have warnings in the Database Check run.

BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~0, value: PSAPSR3INDEX
BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~1, value: PSAPSR3INDEX
BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~4, value: PSAPSR3INDEX
BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~5, value: PSAPSR3INDEX
BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~Z02, value: PSAPSR3INDEX
BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~Z03, value: PSAPSR3INDEX
BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~Z04, value: PSAPSR3INDEX
BR0970W Database administration alert - level: WARNING, type: IN_WRONG_TABLESPACE, object: (index) SAPSR3.BSAD~Z05, value: PSAPSR3INDEX

So the table BSAD is in the correct tablespace PSAPSR3 and has TABART of APPL1 so this is in IAORA under tablespace PSAPSR3 which is correct for that data class.

But since this tables indexes are now in a new tablespace how do I get rid of these warnings?

Thanks

Craig

[Reagan · Accepted solution]

The tables TAORA, IAORA contains the mapping of the index and data tablespaces for TABART's Eg APPL1

What you did here is that you created a new tablespace PSAPSR3INDEX and moved the indexes for the table BSAD to PSAPSR3INDEX tablespace.

The table BSAD is part of the TABART APPL1 and the TABART APPL1 contains several other tables as well. You modified the TAORA and IAORA by pointing the TABART APPL1 to the newly created tablespace PSAPSR3INDEX. This is not correct. The table BSAD is still present in the PSAPSR3 and table DD09L will show you that.

In your case the approach should have been like assigning the table BSAD to a new TABART in Tx SE13 and then the TABART should be assigned to the tablespace PSAPSR3INDEX in the *ORA tables.

You should make sure that the mapping of the table BSAD to the correct TABART in table DD09L and the TABART to the correct tablespace in TAORA and IAORA are correct.

The approach what I have suggested is what I will do in case I want to move a table out of a tablespace to a new one.

[former_member204618]

Thanks guys sorry been a little busy lately.

On another note related to this, without changing DD09L how would I move ALL indexes for PSAPSR3 to new tablespace?  Since I can't simply change a few table references without changing DD09L.

Thanks

Craig

[fidel_vales]

hi,

you mentioned that you have "moved" the indexes using BRTOOLS. I doubt as the error indicate a porblem with the technical settings and BRSPACE takes care of that.

But I will not enter on that,I really think moving indexes to other tablespace does not solve any perfomance issue unless there is a deeper problem on the I/O subsystem.

Craig Armstead wrote:

Hi All,

We were having some performance problems on our 10G database and so our Oracle DBA asked me to create a new tablespace I named it PSAPSR3INDEX and rebuild a single tables indexes into it after he'd analysed the ADDM report.  So I have done this using BRTOOLS.

What kind of performance problem did you had that required moving the indexes?

is it corrected afterwards?

also, is it correct, your DB is 10GB?

[former_member204618]

Hi Fidel,

No the database isn't 10GB it's 740GB it's oracle 10g.

Don't know about whether the performance issues have resolved as a result cause I only made the changes yesterday and the business don't work weekends.  I'll find out on Monday / Tuesday if it's made any difference.

Sorry I should have mentioned when I created the new tablespace with BRTOOLS I incorrectly specified all for the class parameter and didn't realise until this error.  I've since corrected IAORA and TAORA entires as they had changed all the data classes to use this new tablespace.

Thanks

Craig

[Former Member]

hi Craig,

As mentioned by Biborka, check the oss notes and as per the Migration guide, Any new table/Indexes/tablespaces created by customer has to be maintained the below tables DD09L, DDART, DARTT, TAORA,IAORA, ISORA &TSORA and TGORA for the table space and index assignments.

As this assignments will help to maintain the ABAP dictionary and related tables/indexes in Oracle DB relations.

Once this are maintain the issue will be resolved.

Let us know if this has helped.

Regards,

Ram

[former_member204618]

Hi Ram,

I don't see how these notes help.

The table BSAD has data class APPL1 associated with tablespace PSAPSR3.

Data class APPL1 has indexes associated also with tablespace PSAPSR3, which is correct.

But since I have moved only 1 tables indexes of data class APPL1 to another tablespace how do I prevent it from complaining.  The warning is not correct as the indexes are in the correct tablespace as that's where I want them.  I don't want to move ALL indexes of data class APPL1 to this new tablespace!

Thanks

Craig

[Former Member]

Hi Craig,

good day and sorry yesterday was bit occupied with other things.

you correct in terms of table name as the table BSAD is associated with TABART & data class  APPL1 and tablespace PSAPSR3.

But in IAORA for its index BSAD~1 or BSAD~0 the assignment of TBART AAPL1 has to changed to new table space PSAPSR3INDEX from PSAPSR3 which you have created.

So by default it is checking PSAPSR3 for this index and giving this warning as the assignment is wrong.

So either you can create a Z* tabart and assign this tablespace and move the table/indexes from the existing and get this issue completely resolved.

Tagging in for expert comments and correcting me if i am wrong. 

Let me know if you need more inputs.

Regards,

Ram

[Matt_Fraser]

I appreciate the vote of confidence, but this appears to be something specific to Oracle, and I work with SQL Server, so I'm not sure I can add much value here. is probably who you want for this.

[manumohandas82]

Hi Craig ,

Is there an entry in TSORA  against the new tablespace  PSAPSR3INDEX

would you mind  posting  your TSORA , TAORA ,IAORA  TABLES

Thanks ,

Manu

[former_member207186]

Hi,

Regarding this issue you can check the below SAP Note and SAP KBA for troubleshooting:

655162 - "BRCONNECT messages in relation to technical settings"

1578001 - "BR0970W IN_WRONG_TABLESPACE, BR0989W Unknown tablespace"

Regards,

Bíborka