Index tablespace empty

[jorisfrenkel]

We have a number of user schemas in our enterprise geodatabase, and all of them have two tablespaces in our Oracle database, one for data and one for indexes. Recently a new user schema was created, but for some reason the indexes get stored in the tablespace reserved for the data. I guess the user schema has not been created correctly. Any ideas what could have gone wrong?

We usually do not use keywords, so the defaults are used. When creating indexes for feature classes in the other user schemas they are stored in the index tablespace, but not for this one which was created recently.

Joris Frenkel, Staatsbosbeheer, the Netherlands

[EmadAl-Mousa]

Hi,

first, its better to customize a configuration keyword in the dbtune file for each schema (actually, while defining it for each schema you will be needing to reference the data tablespace and the index tablespace in the entries).

second, your current setup would be OK as long as you have sufficient space.

third, always when you create the schema user account in Oracle grant it unlimited quota in data tabelspace and in the index table space:

alter user schema_account quota unlimited on tablespace TS_SCHEMA_DATA_01
alter user schema_account quota unlimited on tablespace TS_SCHEMA_INDEX_01

fourth point, if you want to correct this setup to match your previous setup then create a new fresh schema user account (it will have a different account name) and then setup a DBTUNE keyword for this schema, and then start copying from your OLD schema to the New one through ArcCatalog (copy/paste) your layers.

Hope this helps.

Regards,
Emad

[jorisfrenkel]

Hi Emad,

Thanks for your reply.

I am indeed starting to see the advantage of using configuration keywords. I guess using them I would have been able to avoid this issue.

Our DBA actually discovered this issue when exploring the causes of a performance problem. He thinks storing the data and the indexes in one tablespace might be the cause of this. I will do some additional tests for his hypothesis.

Why do you recommend using unlimited quota? I think it wouldn't do much harm in my particular case, but that's probably not enough to convince my DBA.

Regards,
Joris

[jorisfrenkel]

I did some more research on this.

Moving the indexes to the other tablespace (using the SQL Alter command) does not have any effect on the performance problem. I will start a separate thread for this performance issue.

Joris