Oracle E-Business Suite Concepts

Introduction

This chapter describes the Oracle E-Business Suite data model, including schemas, Oracle user IDs, and related database server features.

Schemas

A given Oracle database can store the objects associated with a single installation of Oracle E-Business Suite. In general, product code objects are stored in the APPS schema, whereas product data objects are stored in the relevant base product schemas. These schemas are described further below.

The APPS Schema

The APPS schema has access to the complete Oracle E-Business Suite data model. It is analogous to the SYSTEM schema, which has access to the entire database. Oracle E-Business Suite responsibilities connect to an APPS schema, and the environment variable FNDNAM is set to the name of the APPS schema. The APPS schema owns all the code objects for the Oracle E-Business Suite, and has access to all data objects. There is one APPS schema for every product installation group.

Utilizing a single schema that has access to all objects avoids cross-product dependencies, and creates a hub-and-spoke access model rather than the spider web model that would otherwise be needed. The APPS schema also improves the reliability of and reduces the time needed for installation, upgrading, and patching, by eliminating the need for cross-product grants and synonyms.

The following code objects are installed in the APPS schema:

Packages
Procedures
Functions
Triggers
Views
Materialized views
Java classes
Queues

Base Product Schemas

All data objects for a product are owned by a specific schema for that product, known as the base product schema.

The following objects are installed in the base product schemas:

Tables
Sequences
Indexes
Constraints
Queues

Relationship Between APPS Schema and Base Product Schemas

The base product schemas also contain grants from various tables and sequences to the APPS schema, as well as synonyms from the APPS schema to the same objects.

Figure 3-1 APPS Schema and Base Product Schemas

the picture is described in the document text

Custom Schema Access

In some circumstances, you may wish to create a schema that has limited or read-only access to Oracle E-Business Suite data.

Warning: Since the APPS schema has all privileges to all Oracle E-Business Suite objects, you should never give users direct access to this schema.

You will need to grant access on objects to the user schema from the base product schema.

Note: You may need to re-grant access if the underlying object is dropped and recreated.

Schemas and Data Access

Some views access packages or functions, where the value returned by the package or function may depend on the environment having been set up properly. The environment is initialized automatically when accessing Oracle E-Business Suite through the Sign-On screen, or when using concurrent processing with Oracle Reports or SQL scripts.

Consequently, if you connect directly to a schema, the rows returned by the view may be different from those returned when you are running in an Oracle E-Business Suite environment. For example, a view may reference a profile option: when accessed from SQL*Plus, the site value of the profile option will be used, rather than the setting for a particular Oracle E-Business Suite user.

Oracle User IDs

Each Oracle E-Business Suite product has a default Oracle user ID, with the product abbreviation used as both the schema name and password. For example, the default Oracle user ID/password combination for Oracle General Ledger is GL/GL.

Important: For security, you should change the default passwords immediately after installation. However, Oracle recommends that you do not change the default user IDs.

A product's schema determines the ownership of the product's data objects, such as sequences, tables, and indexes. If two products are installed under the same schema, that schema owns the data objects for both products.

Since a product's data objects are created in their own schema (such as the GL schema), but the user accesses all data objects through the APPS schema, appropriate grants and synonyms are required between the APPS schema and the base product schemas (see Figure 3-1 above).

Space Management

This section discusses how the Oracle database is set up to meet the space management needs of Oracle E-Business Suite. It provides information on tablespaces, firstly outlining the basic tablespaces required, then discussing the traditional tablespace structure used to support Oracle E-Business Suite products, and finally describing the tablespace model that is used as standard with Oracle E-Business Suite Release 12.

Important: Oracle E-Business Suite Release 12 requires an Oracle database block size of 8K. No other block size may be used.

Introduction to Tablespaces

An Oracle 11g database always requires the following tablespaces to be available:

System Tablespace - This tablespace holds data dictionary tables owned by the SYS account, and is created when the database is installed.
Undo Tablespace - This tablespace holds undo (rollback) information that is used to track database changes until they are either committed or undone (rolled back).
Temporary Tablespace - Temporary tablespaces are used to sort data while it is being processed. It is possible to use a single temporary tablespace, typically called TEMP, for all Oracle E-Business Suite products. Alternatively, separate temporary tablespaces can, if desired, be created for individual products. Since users access Oracle E-Business Suite objects through the APPS schema, the temporary tablespace for that schema (initially the same as that for the Oracle Application Object Library) is used by all products.

The traditional Oracle E-Business Suite tablespace model employed separate tablespaces for a product's tables and indexes. The resulting tablespaces were named by appending 'D' for data or 'X' for an index to the product's short name or Oracle schema name. For example, APD was the tablespace for Oracle Payables data, and APX was the tablespaces for Oracle Payables indexes.

Note: For further information about tablespaces, see the Oracle Database Administrator's Guide 11g.

Employing separate table and index tablespaces for each product made it easier maintain products, and helped to improve database performance. However, with an increasing number of products, this model could easily require several hundred product tablespaces, plus a system tablespace, undo (rollback) tablespace, and temporary tablespace.

In addition, the traditional tablespace model used a database sizing factor to set the extent sizes for an Oracle E-Business Suite product's tables and indexes. The value of this factor was a percentage of the typical estimated growth rate for Oracle E-Business Suite database objects. The sizing factor affected only the size of subsequent extents, as determined by the NEXT database object creation parameter. Most objects were defined with small first extents and larger additional extents.

During installation, Rapid Install provides the option of distributing tablespaces across different disks, to reduce disk head contention and improve overall system performance. In addition to this, many production systems utilize sophisticated disk and volume management technologies at operating system level to further enhance performance.

Oracle Applications Tablespace Model

Oracle E-Business Suite Release 12 utilizes as standard a modern infrastructure for tablespace management, the Oracle Applications Tablespace Model (OATM). The Oracle Applications Tablespace Model is similar to the traditional model in retaining the system, undo, and temporary tablespaces. The key difference is that Oracle E-Business Suite products in an OATM environment share a much smaller number of tablespaces, rather than having their own dedicated tablespaces.

Oracle E-Business Suite schema objects are allocated to the shared tablespaces based on two main factors: the type of data they contain, and I/O characteristics such as size, life span, access methods, and locking granularity. For example, tables that contain seed data are allocated to a different tablespace from the tables that contain transactional data. In addition, while most indexes are held in the same tablespace as the base table, indexes on transaction tables are held in a single tablespace dedicated to such indexes.

The Oracle Applications Tablespace Model provides a variety of benefits, summarized in the list below and discussed in more detail later:

Simplifies maintenance and recovery by using far fewer tablespaces than the older model.
Makes best use of the restricted number of raw devices available in Oracle Real Applications Cluster (Oracle RAC) and other environments, where every tablespace requires its own raw device.
Utilizes locally managed tablespaces, enabling more precise control over unused space and hence reducing fragmentation.
Takes advantage of automatic segment space management, eliminating the need for manual space management tasks.
Increases block-packing compared to the older model, reducing the overall number of buffer gets and improving runtime performance.
Maximizes usefulness of wide disk stripe configurations.

The Oracle Applications Tablespace Model uses locally managed tablespaces, which enables extent sizes either to be determined automatically (autoallocate), or for all extents to be made the same, user-specified size (uniform). This choice of extent management types means that locally managed tablespaces offer greater flexibility than the dictionary-managed tablespaces used in the traditional tablespace model. However, when using uniform extents with locally managed tablespaces, the extent size must be chosen with care: too small a size can have an adverse effect on space management and performance.

A further benefit of locally managed tablespaces, and hence use of OATM, is the introduction of automatic segment space management, a simpler and more efficient way of managing space within a segment. It can require more space, but eliminates the need for traditional manual segment space management tasks such as specifying and tuning schema object storage parameters such as PCTUSED. This and related storage parameters are only used to determine space allocation for objects in dictionary-managed tablespaces, and have no meaning in the context of locally managed tablespaces.

Automatic segment space management is self-tuning, so can take into account an increase in the number of users. A further benefit in Oracle Real Applications Cluster (Oracle RAC) environments is dynamic affinity of space to instances, which avoids the hard partitioning of space inherent with the traditional use of free list groups.

Table 3-1 OATM Tablespace Types and Contents
Tablespace Type	Tablespace Contents
Transaction Tables	Tables that contain transactional data.
Transaction Indexes	Indexes on transaction tables.
Reference	Reference and setup data and indexes.
Interface	Interface and temporary data and indexes.
Summary	Summary management objects, such as materialized views, and other objects that record summary information.
Nologging	Materialized views that are not used for summary management and temporary objects.
Advanced Queueing	Advanced Queuing (AQ) tables and indexes.
Media	Multimedia objects, such as text, video, sound, graphics, and spatial data.
Archive	Tables that contain archived purge-related data.
Undo	Automatic Undo Management (AUM) tablespace. Undo segments are equivalent to rollback segments when AUM is enabled. See note below.
Temp	Temporary tablespace for global temporary table, sorts, and hash joins.
System	System tablespace used by the Oracle database.

In Oracle database server releases prior to Oracle9i, undo space management was performed using rollback segments. For clarity, this method is now referred to as manual undo management. Its successor, automatic undo management is based on the use of a small number of undo tablespaces, in contrast to the larger number of variously-sized rollback segments typically used in manual undo management.

Oracle E-Business Suite Database Organization