Toad World® Forums

Attempting to assign Schema to Indexes

Hello,

I have multiple workspaces under one model, where each workspace is a separate schema with tablespaces. I have been able to write a script that iterates through the indexes of an entity and assign the tablespace for each based on the entities schema. However I haven’t been able to assign the schema to each index. It is available in the drop down box on each index, but I have been unable to access it through scripting.

I have attempted to use the Scripting Reference Guide and have had some success, but not in this instance.

Database: Oracle 12c

Toad Data Model: version 5.4.6.12

Any and all assistance would be greatly appreciated.

Hello Robert,

easiest way is use method LinkObject:

Index.LinkObject(User);
User.LinkObject(Index);

to remove assign:

Index.RemoveObjectLink(User);
User.RemoveObjectLink(Index);

Daril

Thanks Daril,

I was able to use your suggestions to update all the indexes in the model based on their parent entities schema. There is probably a better way of accomplishing the task, but it was and still is a learning curve.

Snippet of the code:

Entity = Model.Entities.GetObject(e);

function main(){
var e, i, a, Entity, EntitySchema, ESchema, ESchemaObj, EIndex, IndexItem;

Model.Lock();
for (e=0; e < Model.Entities.Count; e++)
{
Entity = Model.Entities.GetObject(e);
Entity.Lock();

ESchema = [Entity.DBOwner.Name](http://entity.dbowner.name/);
ESchemaObj = Model.Users.GetObjectByName(ESchema);

for (a=0; a < Entity.Indexes.Count; a++){
  EIndex = Entity.Indexes.GetObject(a);
  EIndex.Lock();

  if(EIndex != null){
    if(EIndex.DbOwner == null){
      EIndex.LinkObject(ESchemaObj);
    }
  }
  EIndex.UnLock();
}
Entity.UnLock();

}
Model.UnLock();
}

Hello Robert,

only few notes to your code.

  1. Entity.DbOwner is same object as Model.Users.GetObjectByName(Entity.DBOwner.Name). So you need not search in Model list.

  2. It can be state, when entity has not assigned schema. In this case Entity.DbOwner.Name cause error.

  3. You can use methods Lock and UnLock, but when you walk through all model and you locking a lot of objects it is better use method Model.RefreshModel() on end of script.

  4. When you LinkObject A to B, you need link B to A too. Otherwise, when you run test of model (consistence test) you get error
    “Failed test TCSBaseTest.TestPairingObjects”
    “Nonpaired objects “TPERIndex.Index1” and"TUser.User2”, property “DbOwner”. Back link is missing."
    You can run this test from popup Menu on Model. For example Model Explorer or Application View(Result is in Message Explorer).

So there is alternative code:

function main(){
var e, i, a, Entity, ESchemaObj, EIndex;

for (e=0; e < Model.Entities.Count; e++)
{
Entity = Model.Entities.GetObject(e);

**if (Entity.DBOwner!=null)**
{
  **ESchemaObj = Entity.DBOwner;**      

  for (a=0; a < Entity.Indexes.Count; a++){
    EIndex = Entity.Indexes.GetObject(a);

    if(EIndex != null){
      if(EIndex.DbOwner == null){
        EIndex.LinkObject(ESchemaObj);
        **ESchemaObj.LinkObject(EIndex);**
      }
    }  
  }  
}

}

Model.RefreshModel();
}

Daril