Database concepts

Informix^® servers can handle multiple database entities. By default an ORACLE instance can only handle one database entity. Starting with Oracle 12c, you can use a multi-tenant database to define several pluggable databases.

ORACLE can manage multiple schemas, but by default other users must give the owner name as prefix to the table name:

SELECT * FROM stores.customer

Solution

With Oracle 10g and 11g:

In an Oracle database, each user can manage his own database schema. You can dedicate a database user to administer each occurrence of the application database.

Any user can select the current database schema with the following SQL command:

ALTER SESSION SET CURRENT_SCHEMA = "schema"

Using this instruction, any user can access the tables without giving the owner prefix as long as the table owner has granted the privileges to access the tables.

You can make the database interface select the current schema automatically with the following fglprofile entry:

dbi.database.dbname.schema = "schema"

When using multiple database schemas, it is recommended that you create them in separated tablespaces to enable independent backups and keep logical sets of tables together. The simplest way is to define a default tablespace when creating the schema owner:

 CREATE USER user IDENTIFIED BY password
    DEFAULT TABLESPACE deftablespace
     TEMPORARY TABLESPACE tmptablespace

With Oracle 12c and higher:

Oracle 12c introduced the multi-tenant database concept, where you can create several pluggable databases in a root container. Consider using this feature, if you need to create several copies of the same database entity, that can be accessed/seen as individual data sources.