About Tablespaces and Datafiles

Oracle stores data in tables that are allocated to tablespaces. Each tablespace contains at least one datafile.

Several tablespaces are part of an ENOVIA Live Collaboration/Oracle database:


  • SYSTEM—When Oracle is installed, this tablespace is created. It contains the catalog information for all the schemas (or Users) within the database. The size of this tablespace depends on if Oracle is used solely for ENOVIA, or if other applications are also using the instance. To allow for upgrades to Oracle we suggest a size of 75?MB.
  • TEMP—Oracle requires a temporary workspace tablespace for performing functions such as re-indexing a vault and performing certain queries. Also, Oracle sorts or joins make use of this tablespace. ENOVIA Live Collaboration may perform joins, if a foreign vault is in place (utilizing an Adaplet to another database).
  • ENOVIA—When the ENOVIA user is created, a tablespace is assigned to hold the schema definitions defined by the Business Administrator. This is the tablespace assigned to the Administration vault.
  • ROLLBACK—A tablespace that is dedicated to holding rollback segments should be created.
  • VAULTS—Two tablespaces may be assigned during creation of each vault--one to hold the index information and one to hold the data tables. If the tablespaces are not specified in the definition, the user's default tablespace is used. We recommend that unique data and index tablespaces are assigned. These datafiles should be on separate disk spindles, if in a non-raid system. While large vaults should have tablespaces defined specifically for them, smaller vaults may share tablespaces with other smaller vaults, but keeping the index and data of each vault separate.

Tablespaces are configured using SQL or Storage Manager with parameters that include the initial size and the size to which they can grow.

Datafile Sizing

To determine datafile sizes for the vault tablespaces, consider the following:


  • How many business objects will this vault contain within one year? two years?
  • For the most commonly-used type, how many attributes, how large a description field, and how much history will be retained per business object?

After large-scale data loading, data to index size ratios are 1 to 1.5, but a vault re-index will most likely make this ratio more of the order of 1 to 1.

With this in mind, our sample scripts shown in "Sample DB2 Database Creation Script" presume that vault Vault1, which uses vault1-ts for data and vault1-Its for index, will have 300,000 business objects after two years of dataload/production usage. Assume the average business object takes 2 KB of data and 3 KB of index space, so data files are created of size 600 MB for data and 900 MB for index.

Tablespace Extent Sizing

When ENOVIA Live Collaboration schema is first created, MX tables are created in Oracle. MX tables derive their default storage size settings from the tablespace values. Once in production, these tables probably will not grow large in size. Depending on the number of program objects, and the complexity of the schema, the default ENOVIA Live Collaboration User tablespace is not likely to exceed 60 MB.

When a vault is created, LX tables are created and their default storage values are also derived from the tablespace values. Theses vault tables may experience dramatic growth.

You can run the validate upgrade command to determine how many of each type of table exists in any version. It will output a list of all tables.

Oracle Corporation's Tuning white papers offer some suggestions concerning extent sizings:


  • Use initial and next size of 160k, 5120k, or 160m.
  • Use pct-increase of 0.
  • Ideally, no tables should have a very large number of extents.

One recommendation is that you should never have more than 10 extents per table.

So, initial = next = 160k is set for all tablespaces. However, examination of the LX tables indicates that certain tables, such as LXOID (the master index of all vault OIDS), LXBO (business object definition table), LXSTRING (string attributes), LXHIST (history table), and possibly LXDESC, LXRO, LXFILE (description, relationship, and attached file reference tables) experience growth. So for vaults which anticipate more than 10,000 business objects, ENOVIA suggests a next extent size of 5120k.

Note: If a database has hundreds or thousands of extents per table, performance will be slow. An Oracle export followed by a database re-creation (import) will help. Contact ENOVIA Technical Support or the ENOVIA Infrastructure Team for advice.