Schemas

Hello,

I am working on a multi-RDBMS-platform database project and hope someone can clarify a few points concerning Oracle 9i.

It is not possible to create an "arbitrary" schema (eg. "MYSCHEMA"), as is possible in DB2 with the CREATE SCHEMA statement?

Each oracle user has their own schema by default which their own tables (if they create any) are created in?

Can a user (eg. SYSTEM) create tables in a different schema? Does this require the CREATE SCHEMA statement or can it be done independently for each CREATE TABLE statement, etc?

This raises the question of what the appropriate "best practice" is for creating tables/views etc in an oracle database, when the intention is for many users to access the same tables. The points this raises are:

1. Since you can't create an arbitrary schema, should you instead have a specific user that "equates" to the schema you want to have. For example, I notice some of the Oracle examples refer to a "hr" (human resources) schema, which presumes there was a user named hr set-up in order to create the database.
2. Is it recommended to create [public] synonyms to map object names in the user's schema to objects in another schema? In our situation the user does not access the database directly (eg. with raw SQL) just through our application server, so it seems an administrative overhead to have to create all these synonyms. The alternatives are ALTER SESSION SET CURRENT_SCHEMA = blah or explicit qualification of the schema name in every SQL query? Does anyone care to recommend the best approach (and why)?

Many thanks

Kevin. Received on Tue Mar 25 2003 - 16:18:26 CST