Forum Stats

  • 3,769,419 Users
  • 2,252,962 Discussions


Multiple DB's or Schemas?

dgavenda Member Posts: 21
edited Nov 25, 2008 1:12PM in General Database Discussions
I am porting an existing app from MySQL to Oracle. The current db layout has a db for every company. Within each db, there are about 5 tables. From a prior post, Justin advised to use schemas instead of creating new db's for every company. Thus, have one db with 5 tables.

Being very new to oracle and only partially thru an O'Reily Oracle book, can shed some light on schemas for this purpose? I'll need to have one schema for each company. Each schema will allow its company access to its data and no other company. Is this correct?

Also, are there examples of using schemas for this purpose?



  • dask99
    dask99 Member Posts: 335

    You are right, you can create every MySQL db on one different Oracle schema inside the only one Oracle Database.

    This schemas are independent and depends on privileges that you assign, normally, one schema can only access the objects that your own schema (except if you assign privileges)

    This link can help you,..
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    Just to re-iterate another point that I made in the other thread (assuming I'm remembering the thread properly) is that the way I would really do this is to have a single schema with a single set of tables for all companies and then use Oracle row-level security (RLS) to restrict users to seeing data only for their company. That should be the most efficient solution (and would definitely be the solution I would advocate were I designing the system).

    If you want to avoid row-level security, separate schemas would be preferrable to separate databases. Separate databases would mean separate sets of data files (including data files for TEMP, UNDO, SYSTEM) separate sets of background processes (i.e. separate PMON, ARCH, etc. processes) separate sets of memory structures (SGA, PGA, etc) and separate sets of database accounts (i.e. you'd need separate accounts for each DBA on each database, a separate set of roles, a separate set of user accounts, etc.). This is highly non-scalable. If you figure 1 GB of RAM per database as a practical lower limit (I'm hard-pressed to imagine a smaller footprint for a production database, you may well need more than that depending on your application), and given that you really, really want the entire SGA & PGA in physical RAM, you pretty quickly run into performance issues if you have more than a few databases on a server).

  • dgavenda
    dgavenda Member Posts: 21
    Is it possible to have unique fields amongst schemas even though it's part of the same table?

    For example, if there is a table called "User" which has a unique field "username" and 2 schemas have access to different parts of that table, can each schema have a user named sampleUser? Or is that a violation of being unique?
  • dask99
    dask99 Member Posts: 335
    which has a unique field "username"
    If you define a unique or primary constraint over one column of one table; there is not possible to insert a same data value , independent of how many schemas has access to the table...
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    I'm not sure I understand the question...

    A schema is a collection of objects (tables, views, etc) owned by a particular user. So a table is in exactly 1 schema-- you cannot have a single table that exists in multiple schemas.

    The namespace of object names is, generally, the schema level. So you can have N schemas in a single database each of which has their own table named USER. And each of those tables could have a column USER_NAME. And you could put a unique constraint on the USER_NAME column in each of these N tables. Of course, since there are N tables, if you ever needed to change the table structure, you would have to go to the USER table in each schema individually.

    Alternately, if you created a single table, you would create a unique constraint on the combination of COMPANY_ID and USER_NAME and use row-level security to restrict users to seeing only the rows for a particular COMPANY_ID.

  • dgavenda
    dgavenda Member Posts: 21
    So just to make sure I understand this: a database may have 0 or many schemas. Within a schema could be tables that also exist in other schemas but they are all part of the same db.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    A database will always have at least 2 schemas (SYS and SYSTEM will always own objects). Realistically, there are half a dozen more schemas that will always (or almost always exist). In order to do anything useful with the database, you would need to create at least 1 schema to hold the database objects you want to create and use.

    A schema is then a collection of objects (i.e. tables, views, etc). Two schemas may each have a table with the same name (i.e. SchemaA has a table named X and SchemaY has a table named X). Those two tables, though, are completely independent, unrelated entities.

    You can then create users (for the most part users and schemas are interchangable until you start looking at enterprise users) that have access to tables in different schemas. You can create a user U1 that has access both to the table X in schema A and to the table X in schema B. That user would then have to explicitly qualify the table name with the schema name in their queries, i.e.
    SELECT *
      FROM schemaA.X
  • dgavenda
    dgavenda Member Posts: 21
    Is there any easy way to have many users access only one schema?

    If I understand everything correctly, I'll need multiple schemas for my app and each schema will need to be accessed by multiple users that should only access that schema.

    For example: User A, B,C need access to schema S1 only. User D, E, F need access to schema S2 only.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    Sure. There are a couple of ways to do this. The simplest is probably to do something like

    - Create one (or more if there are different levels of access) role per schema, i.e.
    CREATE ROLE s1_read_only;
    CREATE ROLE s2_read_only;
    - Grant select privileges on all of S1's objects to S1_READ_ONLY. Ditto with S2's objects to S2_READ_ONLY
      FOR x IN (SELECT * FROM dba_tables WHERE owner='S1')
        EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO s1_read_only';
      END LOOP;
    - Grant these roles to your users, i.e.
    GRANT s1_read_only TO a;
    GRANT s1_read_only TO b;
    GRANT s2_read_only TO d;
    - Then create appropriate aliases for your users. You can do that with private synonyms, in User A's schema
    CREATE SYNONYM table_name FOR s1.table_name;
    or more easily by setting the CURRENT_SCHEMA either immediately after login from your application or via a login trigger, i.e.
    ALTER SESSION SET current_schema = S1
    which is similar to setting the current database in MySQL/ SQL Server/ etc.

  • dgavenda
    dgavenda Member Posts: 21
    To connect to a schema thru jdbc ones uses this:

    jdbc:oracle:thin:@[ip addr]:1521:[schema name]

    along w/ the login/passwd for that schema user.

    Is this correct?
This discussion has been closed.