Manual Space Management or Automatic Space Management

---

This is one of the biggest surprises in migrating from dictionary managed tablespaces. If you read the documentation on locally managed tablespaces, one of their biggest advantages is the performance enhancement they bring with them. All free space, percent free, and percent used information gets stored in bitmapped blocks in the tablespace rather than in the data dictionary tables. This limits the need for every insert operation to access the data dictionary to determine what blocks are candidates for the inserts, what blocks are free and used, and which are available to still be used more. This would, I believe, be the preferred method and there would be a facility for making existing tablespaces not only locally managed, but also to have their space automatically managed. Further, I would believe that, since this is the direction for future database releases, automatic would be the default method for creating them as manual is (according to OEM) maintained for backward compatibility. However, because you are migrating existing objects to a new concept in tablespaces and many of these tablespaces contain objects that already violate the new policy and because it would be extremely difficult to write the conversion program to convert these objects to be compliant with the new system, Oracle was forced to compromise with what it could and could not do. There are currently no plans (at least in 10i) to get rid of the backwardly compatible (manual space management) settings, or to create an automated way in which to transform a manual space managed to an automatic space managed tablespace.

 Alter table FND_INSTALL_PROCESSES Move tablespace fndd1 

If you want to take advantage of all of the features of locally managed tablespaces, the supported method would be to create new tablespaces, one to one with the current tablespaces. These you would want to create with all of the locally managed tablespace settings that are available deliberately set, taking none of the defaults. Then, move the objects (e.g., tables, indexes, etc.) to the new tablespaces and rebuild the indexes. Rebuilding the indexes will be the most CPU labor-intensive part of the operation. Waiting until your database is at Oracle 9i to move where the indexes are built will allow you to utilize the online rebuild of indexes feature, making this a less intrusive procedure. Then comes the task of removing the old tablespaces and datafiles.

Create the Tablespaces for New Products

Somewhere in your documentation, you will find a step that calls for creating new tablespaces for new products. Make sure that this step does not fail. If you have to, go through the script that is likely included in some patch that will be in your sets to apply and double-check that all new tablespaces get created and that they are the right size and that they are locally managed. If you only have a listing of needed new tablespaces, make sure that you do not miss any. If this step fails or gets corrupted in anyway, the subsequent steps will fail.

Upgrade Your Apps

Now comes the part that is long and tedious, upgrading your application. What all you will have to run depends on what all you have installed. Oracle has come out with an assistant that will help you decide what steps you do not have to do. The utility is called TUMS (The Upgrade Manual Script) and it is a patch that can be applied to either a 10.7 or an 11.0 environment and it will tell you which of the Category 1 through Category 6 steps you can safely skip in your particular circumstances and with your unique configuration. The details on the TUMS patch for upgrading to Version 11.5.8 can be found in Metalink Note 213275.1; the details for TUMS for upgrading to 11.5.7 can be found in Metalink Note 177255.1

Another tool that Oracle provides to assist you in the upgrade process is an Excel format spreadsheet that can help you in planning and tracking your upgrade tasks. There are preupgrade tasks included in this spreadsheet and postupgrade tasks that are segregated by type of task and release from which you are upgrading. Using this spreadsheet, you can plan who will be responsible for each task and you can later record the times that each task took in each environment as well as any problems that you encountered in the process and any other notes that you want to add. This can become part of your living documentation not only for this upgrade, but for future upgrades as well. The spreadsheet is available on the Documentation CD that is included in your installation pack and is generated from within the Upgrading Oracle Applications manual. I suggest you print this manual so that you can make notes in the margins and use it as your continual reference throughout the upgrade process. The spreadsheet generates times that it suggests for allotting to each individual task and aggregates the time for every category in what is considered effort time not clock time. Some of these processes can be run in parallel. Some of the estimates are considerably off depending on your expertise level and your relative comfort in performing the steps, but they provide an estimated target.

---