Tablespace of new secondary index. oracle table

[former_member325638]

Hi All

We have recently created custom index in ECC system on table GLPCA.  After creation of index in development system; we moved the transport further.

The system schema is SAPERP.  Usually default tablespace for data is PSAPERP and for indexes in PSAPERPI. But the newly created index fell in tablespace 'PSAPERPUSR' instead of PSAPERPI.

Could you please advise how its decided which tablespace; the new secondary index will fall into.  As per few SDN blogs; I found; it should be inside default tablespace PSAP<..> I.   Few mention about tables IADB6 and TADB6; but nothing clear from there too.

No tablespace was intentionally specified while creating index; but we have got concerns about tablespace falling in different tablespace

Could anyone please check and advise which tablespace the secondary index falls into and on what logic.

Thanks

Varun

[Former Member · Accepted solution]

You wrote you are on Oracle.

So you better forget about tables IADB6 and TADB6.

Read about tables IAORA and TAORA instead.

In the 'technical settings' for your table have a look at 'Data Class'.

Table IAORA should tell you the tablespace name for the indexes of your table.

Field TABART is 'Data Class'.

Field TABSPACE is tablespace name.

Hope this helps

PS:

May I ask why you are rating your own question?

Shouldn't this be left to others?

[former_member325638 · Accepted solution]

Hi Josef

Thanks for looking into this and replying. I have checked tables IAORA and TAORA. In both the tables; the fields USER and USER1have corresponding tablespace PSAPERPUSR. Table contents below

"

TABART  TABSPACE

APPL0   PSAPERPI

APPL1   PSAPERPI

APPL2   PSAPERPI

CLUST   PSAPERPI

DDIM    PSAPERPI

DFACT   PSAPERPI

DODS    PSAPERPI

POOL    PSAPERPI

SDIC    PSAPERPI

SDOCU   PSAPERPI

SLDEF   PSAPERP701I

SLEXC   PSAPERP701I

SLOAD   PSAPERPI

SPROT   PSAPERPI

SSDEF   PSAPERP701I

SSEXC   PSAPERP701I

SSRC    PSAPERPI

TEMP    PSAPERPI

USER    PSAPERPUSR

USER1   PSAPERPUSR

ZCOEP   PSAPCOEPI

ZSWCO   PSAPSWWCONTI

ZZUPI   PSAPZUPIAI"

So; does it mean that all the secondary indexes will fall under PSAPERPUSR by default as they are user developed.

The rating was done by mistake while scrolling down my message. Once done; didn't find any button to undo that. Only option is to change the rating but could not make it blank

Thanks

Varun

[Former Member · Accepted solution]

So; does it mean that all the secondary indexes will fall under PSAPERPUSR by default as they are user developed.

No; as I wrote, it depends on the 'Technical settings' of the respective table, as it can be seen in transaction SE11 for example.

I just looked in one of our ECC systems: Table GLPCA has APPL1 as 'Data Class'. Please have a look at your system!

According to your list an index for 'Data Class' = APPL1 should be created in tablespace PSAPERPI.

One more question:

How was your index created?

In the SAPGUI, with transaction SE11 for example?

Or was it created by database tools like sqlplus in advance?

(Somehow I suspect the latter ...)

regards

[former_member325638 · Accepted solution]

Thanks a lot Josef for the details from which I could make out where index should fall in by default.

In our system too GLPCA has data class as APPL1.

We created the index in SE11 in development system. There its inside PSAPERP according to the table and data class in dev. But it took a very long time to move the transport; being a huge table and index. So; we took help from our database team to create it using sql and later we moved the transport. So; your suspect is right there too.

One last doubt; whether we should move it to correct tablespace now or it can be left as such. Both tablespaces have different space allocations

Thanks

Varun

[Former Member · Accepted solution]

Well, I think you may leave it as it is.

I could imagine two possible reasons for moving the index:

1) Space problems in tablespace PSAPERPUSR.

2) A pedantic boss or auditor, liking to have everything at its correct place.

regards

[former_member325638 · Accepted solution]

Thanks a lot Joseph for all the details you shared about the tablespaces and how the tables/indexes are falling under different tablespaces