Toad World® Forums

How to manage items common to multiple schemas



Our application uses a number (about 6) different schemas/users.

Due to the number of items in some of them it is not really practical to put them in the same TDM model.
We are creating a TDM model for each schema.

There are a number of common items, such as tablespace definitions, user data types, etc. Our options seem to be;

  1. duplicate in each schema maintaining the code in 6 places
  2. create another schema to maintain the items and merging any changes into the model for each schema

Is there a better (less error prone) way of doing this?

Is anyone using different way of dealing with this situation?




Hi Malcolm,

the latest BETA allows you to assign existing schema to multiple objects at once (or remove existing schema if you select the --NONE-- option). See the enclosed screen shot.

This new feature may help you to work with one model and define schema at certain moment (e.g. before script generation). Of course, if your schema definition is in SQL code, then you might take advantage of scripting and write a script that will convert user defined string, something like {malcolm-schema} with selected schema.

In other words, another possibility might be a combination of the new Schema Assignment feature + custom scripts.




Could you explain in more detail what this dialog does? I can’t seem to find this dialog in the manual.


Yes, sure.

This option is avaible in the Model menu | Schema/Owner Assignment.
It allows you to assign schema/owner to your model at one jump or remove existing schema from your model at one jump.

In the Select Schema/Owner/Database box, you will see all schemas, users, owners… available in your model.
(Schema, user, owner, database - database dependent issues.)

Let’s say you reversed your Oracle model and would like to add a user to entities, and procedures. Now you don’t have to edit each entity/procedure one by one, but can simply use the Schema/Owner/Database Assignment function :

  1. Select particular user from the Select Schema/Owner/Database. (The user must exist in your model. In Oracle model, you can define users in Model menu | Users).
  2. Select particular types of objects - so, e.g. entities and procedures.
  3. Press Execute.
    Result: The particular user will be assigned to entities and procedures within a while.
    E.g. Edit an entity and see the General tab | User/Schema box - the particular user will be there.

Via this dialog, you can also remove schema/owner/database. You will just select --none-- item from the Select Schema/Owner/Database box and press Execute.

Note: This new feature is not in the Manual/Help. The docs are being updated at the moment and will be available for official release 3.1. Thanks.

If you have any questions, please write me back. Thanks.



I think maybe I didn’t explain what I need to do clearly.

We have split our database into separate models for each schema.
The reason for this is to make the models manageable with the number of objects in each schema.

Loading the biggest schema uses about 250MB memory,
if we generate this model the memory usage goes up by about 550MB.
This memory is not released until TDM is closed (this is Quest support case 583927).

There are certain items that are common to each schema model,
eg user defined data types, tablespace definitions, etc.

Ideally we do not want to duplicate these items in each model as this will lead to maintenance errors.
We need some way to share these common items with each model.

We could possibly create another model to contain the common items and merge into the other models whenever there is a change.
This is not really ideal.

Can one model reference items in another model?

Any other ideas?



Hi Malcolm,

thanks for the explanation. Now I see I misunderstood your requirement. Your idea of having a separate model and doing model merge seems to be good. Right now you cannot reference items from one model to another. Our ideas related to something like “repository” are will not appear in the product soon, sorry.

Have a nice day,



Hi moakes,

this is more-less what I was thinking of. Our main application is a big system with about 7 “mutations”. Each mutation consists about 400 standard entities and about 100 mutation specific entities.
The main goal is to use RE, create Logical Model, and have “One Model To Generate Them All”, and some way of customizing and maintaining mutation entities. One idea is to merge all models, extract some core part and try to make some order with mutation specific entities. I though about extending TDM (like “Customer Feedback” demo flash movie) and adding some checkboxes to entity form, to allow designers to choose, if they want to have this particular entity in their mutation.
So instead of DIVIDNG your model into separate model, you will MERGE them, and customize TDM to have ability to choose, what to generate, when coming to DDL generation?

Have a nice weekend :slight_smile: