For the Decision Support Oracle environment:

• For ODS objects, use a schema structure that matches (in structure, not necessarily the exact names) that of the source data.
• For DSS objects, use 1 schema called:
• DSS

• While there had initially seemed to be some logic to having a set of schemas that corresponded to our Sybase databases (which group objects by application area), I could find few advantages to this approach.
• Having fewer schemas has its advantages. If all DSS data is in one schema, there are no schema navigation issues when retrieving data from the database. A user can "put themselves in" that schema as the default (rather than the users schema), and then there is no cross-schema querying, and no need to qualify object names with schema names. (One can code "selectfrom table1, table2" rather than "selectfrom schema1.table1, schema2.table2") (The command "ALTER SESSION SET CURRENT_SCHEMA = <schema name>" changes the default schema.) (Public synonyms are another, and better, way to hide the details of schema location from the user. But since there are no compelling reasons to have many DSS schemas, the main usefulness of public synonyms ends up being to avoid needing the "ALTER SESSION SET CURRENT_SCHEMA = " statement)
• Object names must be unique within a schema, and we do have some duplicate table names across our DSS and historical / archive DSS Sybase databases. (Also across our ODS and DSS databases, but there are other issues with ODS tables as wellsee below.) Initially, I had proposed DSS and DSSHIST schemas to avoid name collisions between current and historical tables. But there are only 5 tables with name collisions in our current Sybase DSS databases (all in cm_py_rec_dss_d). Since there are so few, I propose we deal with these by changing the table names for the 5 historical versions when we convert to Oracle, thus allowing us to consolidate all DSS tables into one DSS schema.
• The proposal is to have a schema structure for ODS tables that matches the structure on the source side. This is for a couple of reasons. One is because Oracle Export / Import is a likely candidate for this Oracle to Oracle data replication, and Export / Import can be managed on the schema level. While Export / Import are very flexible, and we could probably accomplish what we want without taking advantage of schema-level Export / Import, doing so may greatly simplify things. Another reason for this proposal is that we wont be controlling schema setups on the operational side, and it is very possible that operational objects could end up being owned by several users (in several schemas). By duplicating the operational schema structure in the ODS, we will insure that we will not have any name collision problems. Although having multiple ODS schemas raises the join issue discussed above, this is not such a significant issue for ODS tables, because only programmers, power users and data managers should be accessing ODS tables anyway. (The whole idea of the ODS is that it matches the operational side, so for it to match in schema structure actually conforms to this logic.)

• We need for the test and development environments to be in separate databases / instances. If we implement multiple development / test environments in one database, we would need separate development and test schemas (such as DSSDEV and DSSTEST) corresponding to every production schema (such as DSS in this example). This would create migration issues that would be avoided by having separate databases for dev, test and prod, which would allow us to use the exact same schema names in each environment.

• Query perspective: As detailed above, having all DSS objects in one schema simplifies query writing. In this plan, conversion of Sybase sql to Oracle would require remove <dbname..tablename> type syntax (this cant be avoided), but would avoid having to replace this with <schemaname.tablename> type syntax.
• Synonyms: If we were forced by other constraints to have multiple DSS schemas, public synonyms could be used to avoid having to include the schemas in the object references. While this will not be necessary, since the proposal is to have just one DSS schema, I would still propose that we create a public synonym for every table and view, to avoid the necessity to code the "ALTER SESSION SET CURRENT_SCHEMA = " statement prior to query execution.
• Grants: Grants are performed at the object level, not the schema level, so how we organize objects under schemas doesnt seem to have much impact, except that it might be somewhat easier to manager grants with fewer rather than more owners/schemas.
• Backup / recovery: This is a tablespace level activity, and the schema organization is not an issue.
• Data migration: As detailed above, Export / Import can operate at the schema level, and it is suggested that the ODS objects be organized to take advantage of this.
• Taking objects "offline / online": This is a tablespace level activity.
• Manageability when build datagroups: Schema organization alternatives dont seem to have any significant impact on this.

• Data dictionary views: Aside from the Export / Import options, the only advantage I could find for having many schemas (like our many Sybase databases) was that Oracle has a lot a Data Dictionary views that will report on things at the schema level. This did not seem to be compelling enough to create a bunch of schemas. In the following list the ALL_* views report on the whole database and the corresponding USER_* views would provide the same information, but just about the objects in the current schema.

ALL_OBJECTS, USER_OBJECTS

ALL_CATALOG, USER_CATALOG

ALL_TABLES, USER_TABLES

ALL_TAB_COLUMNS, USER_TAB_COLUMNS

ALL_TAB_COMMENTS, USER_TAB_COMMENTS

ALL_COL_COMMENTS, USER_COL_COMMENTS

ALL VIEWS, USER_VIEWS

ALL_INDEXES, USER_INDEXES

ALL_IND_COLUMNS, USER_IND_COLUMNS

USER_CLUSTERS

USER_CLU_COLUMNS

ALL_SEQUENCES, USER_SEQUENCES

ALL_SYNONYMS, USER_SYNONYMS

ALL_DEPENDENCIES, USER_DEPENDENCIES