ODI Master: Creating Repository Storage Spaces FAQS

1. What databases can I use to store Oracle Data Integrator repositories?

Oracle Data Integrator repositories can be installed on database engines supported by Oracle Fusion Middleware 12c. You can find the latest list of supported database versions and requirements on the Oracle Fusion Certification page.

2. Why should repositories be stored separately from application data?

Oracle recommends storing repositories separately from application data for reasons related to maintenance, backup, and recovery. This can be achieved by using a different schema (for Oracle databases) or a different database (for Microsoft SQL Server).

3. Can I store my Master and Work Repositories in the same schema?

Yes, the Master repository can be stored in the same schema as the Work repository. However, you cannot create two different work repositories in the same schema.

4. How do I create storage spaces for repositories in Oracle?

In Oracle, you need to create a schema for each repository (e.g., one for the master repository and one for the work repository).
You can create schemas using the SQL commands provided, such as:

SQL> create user MY_SCHEMA identified by MY_PASS

default tablespace MY_TBS

temporary tablespace MY_TEMP;

SQL> grant connect, resource to MY_SCHEMA;

SQL> grant execute on dbms_lock to MY_SCHEMA;

Replace MY_SCHEMA, MY_PASS, MY_TBS, and MY_TEMP with the appropriate schema name, password, tablespace, and temporary tablespace for your setup.

5. What are the recommended permissions for repository schemas in Oracle?

CONNECT: To allow users to connect to the schema.
RESOURCE: To allow the schema to create objects like tables and views.
EXECUTE ON DBMS_LOCK: To grant the necessary permissions for the DBMS_LOCK package.

6. Can I create multiple Work Repositories in the same schema?

No, you cannot create two different work repositories in the same schema. Each work repository must be stored in a separate schema if needed.

7. How do I handle storage space when creating repositories?

When creating repositories, ensure that you allocate sufficient storage space for the schemas. The Repository Creation Utility (RCU) can be used to manage storage automatically, but if you're doing it manually, you need to configure tablespaces and schemas carefully to ensure proper storage allocation.

8. What is the role of the temporary tablespace (MY_TEMP) in Oracle?

The temporary tablespace is used to store temporary data for the schema. It is necessary for tasks like sorting or large intermediate result storage during queries or transactions.

9. Can I store the Master and Work Repositories on different databases?

Yes, the Master and Work Repositories can be stored in different databases as long as they are on databases supported by Oracle Fusion Middleware 12c. However, ensure that the connection and permissions are properly set up.

ODI Master