Oracle Applications Tablespace Model Release 11i

Introduction

In Oracle Applications Release 11i, each Applications product has been allocated two tablespaces, one for tables and one for indexes. With 191 products in the 11.5.8 release, this has meant 382 tablespaces, in addition to the temporary tablespace, system tablespaces, and rollback segments.

The Oracle Applications Tablespace Model (OATM) introduces a new, consolidated tablespace model in Oracle Applications, that uses 12 locally managed tablespaces for all products, including the temporary tablespace, system tablespace, and undo segments. In this revised Oracle Applications Tablespace Model, each Oracle Applications database object is mapped to a tablespace based on its Input/Output characteristics. This new model allows for easier maintenance, reduces space usage, and allows for potential run-time performance gains.

The Oracle Applications Tablespace Migration Utility is an interactive menu-based PERL program including a set of sizing estimate reports. It enables customers to convert their Oracle Applications databases to the new tablespace model (OATM) for either all schemas at once or partially for select schema(s) at a time, depending on acceptable down time and available disk space.

Benefits

The Oracle Applications Tablespace Model (OATM) provides a number of benefits over the previous tablespace model.

OATM takes into account the following object I/O characteristics of an object:

• Its size
• Its lifespan
• Its access methods
• Its locking granularity

OATM facilitates:

• Administration and configuration ease for Oracle Applications tablespaces
• Locally managed tablespace extent and segment management features
• Improved space utilization after the migration
• Increased block-packing, which will reduce the overall number of buffer gets
• Taking advantage of wide stripe configurations

Fewer, Consolidated Tablespaces

The most obvious benefit is the reduction in the sheer number of tablespaces(from 300+ to twelve), which results in easier maintenance.

Real Application Cluster Support

OATM also facilitates Real Application Cluster (RAC) support. RAC is an Oracle9i Database feature that harnesses the processing power of multiple interconnected computers where all active instances can concurrently execute transactions against a shared database disk system. OATM is critical when implementing RAC on Linux, where currently there is a limitation of 255 raw devices.

Locally Mananged Tablespaces

OATM uses locally managed tablespaces and provides support for either UNIFORM or AUTOALLOCATE extent management, available with locally managed tablespaces. Locally managed tablespaces have benefits over dictionary-managed tablespaces in the previous model and allow for the sizes of extents to be determined automatically by the system (AUTOALLOCATE). Alternatively, all extents can have the same size (UNIFORM) and override object storage options, with uniform extent size.

User Extent Management, another configuration supported with Locally Managed Tablespaces, is of relevance only in case of Dictionary Tablespaces that have been migrated over to Locally Managed Tablespaces . Since the migration to OATM is performed at the object level and not at the tablespace level, User Extent Management is not relevant. The default recommended configuration in OATM is Uniform Extent Management. The migration utility recommends the default of 128k uniform extents which can be changed to suit the customer database. Note however that if you use another extent size, that size must still comply with the minimum extent size required for your Oracle Database version. If you encounter issues when using a lower size than the default, increasing the uniform extent size may help resolve those issues.

Improved Space Utilization

The tablespace migration utility migrates objects from the existing dictionary-managed tablespaces to locally managed tablespaces with automatic segment management and either uniform or autoallocated extent management. This results in improved space utilization. While migrating data and index segments from the old tablespaces to OATM, the OATM Migration Utility reclaims unused space, especially in case of fragmentation.

Product Tablespaces

In the previous tablespace model, there were two tablespaces for each Oracle Applications product, one for the tables and one for the indexes. The standard naming convention for tablespaces was to suffix the product's Oracle schema name with "D" for "Data" tablespaces and "X" for "Index" tablespaces. For example, the tablespaces APD and APX were the default tablespaces for Oracle Payables tables and indexes, respectively.

OATM, as planned to be introduced in Release 11.5.10, comprises nine default tablespaces for applications objects in addition to Undo, Temp, and System database tablespaces. Indexes on transaction tables are held in a separate tablespace dedicated for transaction table indexes, whereas all other indexes are held in the same tablespace as the parent/base table.

Following are the tablespace types available out of the box with OATM:

Tablespace Type	Tablespace Name	Contents
Transaction Tables	APPS_TS_TX_DATA	Tables that contain transactional data.
Transaction Indexes	APPS_TS_TX_IDX	Indexes on transaction tables.
Reference	APPS_TS_SEED	Reference and setup data and indexes.
Interface	APPS_TS_INTERFACE	Interface and temporary data and indexes.
Summary	APPS_TS_SUMMARY	Summary management objects, such as materialized views, and other objects that record summary information.
Nologging	APPS_TS_NOLOGGING	Materialized views not used for summary management and temporary objects.
Advanced Queuing/AQ	APPS_TS_QUEUES	Advanced Queuing and dependent tables and indexes.
Media	APPS_TS_MEDIA	Multimedia objects, such as text, video, sound, graphics, and spatial data.
Archive	APPS_TS_ARCHIVE	Archive-purge-related objects.
Undo	UNDO	Automatic Undo Management (AUM) tablespace. UNDO segments are identical to ROLLBACK segments when AUM is enabled.
Temp	TEMP	Temporary tablespace for global temporary table, sorts, and hash joins.
System	SYSTEM	System tablespace used by the Oracle Database.

Tablespace Classification

The new Oracle Applications Tablespace Model (OATM) relies on certain explicit and implicit tablespace classification rules. These implicit and explicit tablespace classification rules are determined based on storage considerations for the object type in question. The following implicit classification rules, based on object types, apply in OATM.

Object Type	Tablespace Type
Advanced Queuing (AQ) Tables	Advanced Queuing /AQ
Global Temporary Tables	The database always creates these objects in the Temporary (TEMP) Tablespace..
IOTs (Index Organized Tables)	Transaction_Tables
Materialized Views	Summary
Materialized View Logs	Summary
Domain Indexes	Transaction_Indexes
All Other Indexes	With the exception of indexes on tables classified as "Transaction_Tables", all indexes will share the same tablespace type as the base object (Table/Materialized View). Indexes on tables classified as "Transaction_Tables" will reside in the "Transaction_Indexes" tablespace type.

Explicit tablespace classification rules pertaining to other objects, such as tables, are defined internally in Oracle Applications.

Changing datatype of column from varchar2 to number when data present in that column

You can use the Oracle “alter table” syntax to change the data type for an existing column, as shown in this example:

alter table
   table_name
modify
(
   column_name    varchar2(30)
);

Of course, you must deal with existing column data.  When modifying a tables column datatype you may want to export the rows, redefine the table and then re-import you data.

Following is the one scenario where we get ORA-01722 error while changing existing columns datatype from varchar2 to number, here table contains the lacks of records.

Column that we are going to modify datatype contains only numeric records  but that data was inserted when its datatype was varchar2. Now it is challenge to modify this columns datatype. 

select * from xyz 
Suppose above table contains some records.

Create the backup table for above table by executing following command

create table xyz_bkp as select * from xyz

Modify the column using following command

alter table xyz_bkp modify abc number(20,4)

You may get the ORA-01722 error

Then check by adding new column to that table and then try to update all the records from existing column.

alter table xyz_bkp add abc_1(15,2);

update xyz_bkp

set abc_1=abc;

Here also we received the ORA-01722 error.

Now lets know about this error message.

ORA-01722 is thrown because a particular string was not able o be converted into a specific valid number when a user attempted to convert a character string. 

There are several possible resolutions to Oracle ORA-01722 in this context:

• If you are attempting an " INSERT INTO ... VALUES (...) " you need to find out which data item is invalid
• If you are trying to supply the values in a sub query which is intended to INSERT or UPDATE, you have to dig a little beeper because the character that is throwing ORA-01722 is actually hidden.  You would then need to find the row that contains a non-numeric string.  Then, you should fix or add data to resolve ORA-01722
• Instead of an INSERT or UPDATE, you attempt a SELECT.  Here, ORA-01722 is thrown most likely because of some implicit conversation in WHERE.  To resolve ORA-01722 in this context, find a numeric and character column which are being compared.

I used the following method to remove character string or any other characters other than number from that column.

update xyz_bkp

set abc_1= RTRIM

          ((LTRIM

               (TRANSLATE

                   (REPLACE (abc, 'A', ''),

                       'ABCDEFGHIJKLMNOPQRSTUVWXYZ

abcdefghijklmnopqrstuvwxyz@#$%^&*()_+-=,!\`~{}./?:";''[]'

                    || CHR (160)

                    || CHR (32)

                    || CHR (0)

                    || CHR (253),

                    'A'

                   )

               )

           )

          )      

This statement will remove the special characters or character string from abc column.

Now remove or drop the existing column 

alter table xyz_bkp drop column abc;

Rename new column with existing column's name

alter table xyz_bkp rename column abc_1 to abc

If all the above steps are successful the proceed to do same in your main table.