WebCenter Sites Database Tables

43.1 Cache Management Tables

WebCenter Sites delivers the CacheManager, a page caching utility that manages both the WebCenter Sites page cache and the Satellite Server caches.

Because cached pages have to expire both when their freshness date expires and when an asset that the page refers to in some way is changed, the CacheManager keeps track of expiration times and the dependencies that exist between the pages and pagelets stored in the cache. It stores this information in the SystemPageCache and SystemItemCache tables.

Every WebCenter Sites system uses a CacheManager, which means that these tables grow dynamically on any system—development, management, or delivery. The following table describes how cache-tracking tables grow.

Table 43-1 Cache Management Tables

Table	Number of Rows
SystemPageCache	One row for every cached page. When a page expires, the row is removed.
SystemItemCache	One row for each asset, pagelet, or other item that is referenced by a cached page in the SystemPageCache table. For example, if a cached page was created from a page asset that has associations to three article assets, there would be four rows for that cached page.

Table

Number of Rows

SystemPageCache

One row for every cached page.

When a page expires, the row is removed.

SystemItemCache

One row for each asset, pagelet, or other item that is referenced by a cached page in the SystemPageCache table. For example, if a cached page was created from a page asset that has associations to three article assets, there would be four rows for that cached page.

43.2 Approval System Tables

The WebCenter Sites approval system keeps track of each asset that has been approved, the dependencies that approved assets have on other assets, and the targets for which assets are approved. It stores this information in the ApprovedAssets and ApprovedAssetDeps tables.

These tables have the potential to grow very large on a management system, but are not used on a delivery system. The following table describes how approval system tables grow.

Table 43-2 Approval System Tables

Table	Number of Rows
ApprovedAssets	One row for each asset that has been approved, for each target destination. That is, if an asset has been approved for two destinations, that asset has two rows in this table.
ApprovedAssetDeps	One row for each asset dependency for each approved asset, for each target destination. For example, if an approved asset is dependent on four other assets, it has four rows in this table. If that same asset is approved to two destinations, it has one row each for each dependency for each destination.

Table

Number of Rows

ApprovedAssets

One row for each asset that has been approved, for each target destination. That is, if an asset has been approved for two destinations, that asset has two rows in this table.

ApprovedAssetDeps

One row for each asset dependency for each approved asset, for each target destination.

For example, if an approved asset is dependent on four other assets, it has four rows in this table. If that same asset is approved to two destinations, it has one row each for each dependency for each destination.

43.3 Publishing System Tables

The WebCenter Sites publishing system keeps track of when assets were published, and where they were published to. It stores this information in the PubKey and the PublishedAssets tables.

As the number of assets in your WebCenter Sites management system increases, so does the number of rows in these tables. The following table describes how publishing system tables grow.

Table 43-3 Publishing System Tables

Table	Number of Rows
PubKey	For mirror publishing: one row for every asset that is mirrored, for each target destination. For export publishing: one row for each page that is created during the export. That is, if 14 assets are rendered into 1 page, the table holds 1 row—not 14—for the entire group of assets.
PublishedAssets	For mirror publishing: one row for every asset that is mirrored, for each target destination. For export publishing: one row for each asset that is exported. To continue the preceding example, if 14 assets are rendered onto 1 page, the table holds 1 rows (one for each asset) for that page.

Table

Number of Rows

PubKey

For mirror publishing: one row for every asset that is mirrored, for each target destination.

For export publishing: one row for each page that is created during the export. That is, if 14 assets are rendered into 1 page, the table holds 1 row—not 14—for the entire group of assets.

PublishedAssets

For mirror publishing: one row for every asset that is mirrored, for each target destination.

For export publishing: one row for each asset that is exported. To continue the preceding example, if 14 assets are rendered onto 1 page, the table holds 1 rows (one for each asset) for that page.

43.4 Workflow Tables

The workflow system keeps track of all the assets that are involved in a workflow process at any given time. It stores this information in the Assignment and WorkflowObject tables.

As the number of assets that are placed in a workflow process increases, so does the number of rows in these tables. The following table describes how workflow tables grow.

Table 43-4 Workflow Tables

Table	Number of Rows
Assignment	One row for each workflow assignment. For example, if an asset is assigned to four users during the course of a workflow process, that asset has four rows in the table. These rows are not deleted when the workflow process is completed for the asset.
WorkflowObjects	One row for each asset in workflow. When an asset leaves workflow, the row is deleted.

Table

Number of Rows

Assignment

One row for each workflow assignment. For example, if an asset is assigned to four users during the course of a workflow process, that asset has four rows in the table.

These rows are not deleted when the workflow process is completed for the asset.

WorkflowObjects

One row for each asset in workflow.

When an asset leaves workflow, the row is deleted.

43.5 Basic Asset Tables

A basic asset type has one primary storage table. For example, the primary storage table for the article asset type is named Article; the primary storage table for the HelloArticle asset type is named HelloArticle.

As the number of assets of a single type increases, so does the size of the table that holds assets of that type. The primary storage table for a basic asset has one row for each asset of that type.

43.6 Flex Asset Tables

Each asset type in a flex family has several database tables. The three types of tables in any flex family that can potentially grow quite large are as follows:

The primary storage table for the flex asset type. For example, the primary storage table for the avisports sample site asset type named article is AVIArticle.
The _AMap tables for flex asset or flex parent asset types. (For example, AVIArticle_AMap.)
The _Group tables for flex parent asset types. (For example, ArticleCategory_Group.)
The _Mungo table for the flex asset type. (For example, AVIArticle_Mungo.)
The _Mungo table for the flex parent asset type. (For example, ArticleCategory_Mungo.)
The Mungo_Blobs table.

The following table describes how flex asset tables grow.

Table 43-5 Flex Asset Tables

Table	Number of Rows
FlexAssetType (For example: AVIArticle)	One row for every asset of this type.
FlexAssetType_AMap (for example: AVIArticle_AMap)	One row per attribute value—whether the attribute value is inherited or directly assigned—for each of the assets of that type.
FlexAssetType_Group (for example: ArticleCategory_Group)	One row per ancestor relationship between flex parent asset and flex asset—includes rows for grandparent, great-grandparent, and so on, relationships.
FlexAssetType_Mungo (for example: AVIArticle_Mungo)	One row for every attribute value for each asset of this type. In some cases, this equation can result in more than 10 million rows.
FlexParent_Mungo (for example: ArticleCategory_Mungo)	One row for every attribute value for every parent asset of this type.
Mungo_Blobs	One row for every attribute value saved for an attribute of type blob.

43.7 Visitor Tables (Engage)

Oracle WebCenter Sites: Engage captures visitor information and stores it in the visitor data tables. These tables store information such as session IDs for visitors so that they can be linked with their previous sessions and values for the attributes that represent the data you are collecting.

As the number of visitors who visit your online site increases, so do the rows in these tables. The following table describes how visitor tables grow.

Table 43-6 Visitor Tables in Engage

Table	Number of Rows
scratch	One row for each visitor context session object that is created for a visitor. Visitor context session objects are things like promotion lists, segment lists, shopping carts, and so on. There are at least five rows added to this table for each visitor in each session.
VMVISITOR	One row for each visitor for each browser session. Engage creates a unique visitor ID for each visitor for each session.
VMVISITORALIAS	In database-centric visitor tracking, one row is created for each visitor for each browser session, even for returning visitors. This method creates one row per visit. Memory-centric visitor tracking creates one row per visitor, regardless of the number of returning visits. Note: A row holds the name/value pair of an alias and the visitor ID (also listed in VMVISITOR) that marks a session.
VMVISITORSCALARVALUE	One row for each visitor attribute value (except for attributes of type binary) that is saved. Note: This table is not populated when memory-based visitor tracking is enabled.
VMVISTORSCALARBLOB	One row for each visitor attribute value of type binary (also referred to as scalar objects) that is saved. Note: This table is not populated when memory-based visitor tracking is enabled.
VMz ------------	These are dynamically generated tables that are created when values for a history attribute are saved. Engage creates one table for each history type and adds a row to the table each time a record of that type is saved.

This section covers the following topics:

43.7.1 Managing the Attribute Tables

Because the tables that hold attribute values can grow very quickly, you should purge inactive data from them regularly.

You use the following Engage XML object method or its JSP equivalent to delete inactive data from these tables:

<VDM.FLUSHINACTIVE STARTDATE="cutoffDate"/>

Inactive visitor data is data marked with a visitor ID that is not connected through an alias to data that you consider current. You set a cutoff date (STARTDATE) for Engage to use. All visitor data recorded before that date is deleted from the previously listed visitor tables unless it is linked through an alias with data recorded after that date.

There are several ways to use the VDM.FLUSHINACTIVE tag. For example:

You can create an administrative element that calls the tag and prompts you to enter the cutoff date.
You can provide it with an equation that calculates a cutoff date based on some parameter so that you can set it up to run as an automatic event at a regularly scheduled time. To set it up as an automatic event, use the WebCenter Sites APPEVENT tag (which functions like kron). For information about this tag, see the Tag Reference for Oracle WebCenter Sites Reference and About Adding to the System Tables in the Developing with Oracle WebCenter Sites.

Note that the value that you pass to the STARTDATE parameter must be in epoch time. You can use the WebCenter Sites DATE.CONVERT tag to obtain an epoch value for the date to use. For example:

<DATE.CONVERT VARNAME="flushtime" 
   YEAR="four digit year" MONTH="number in the range 1-12"
   DAY="number in the range 1-31" 
   [HOUR="number in the range 0-11 where 0 is midnight" AMPM="am
   or pm" MINUTE="number in the range 0-59" TIMEZONE="timezone"]/>
<VDM.FLUSHINACTIVE STARTDATE="Variables.flushtime"/>

For more information about these tags, see the Tag Reference for Oracle WebCenter Sites Reference.

43.7.2 Managing the Session Objects (Scratch) Table

The scratch table can grow quickly because at least five session objects are stored for each visitor in each session. Each object has a timestamp; you should purge old objects regularly, based on their timestamps.

To delete old session objects from the scratch table:

Use the following Engage XML object method or its JSP equivalent to delete.
```
<SESSIONOBJECTS.FLUSH TIMESTAMP="cutoffTime"/>
```
Because session objects includes carts, you must set the cutoff time to represent the point at which you consider a cart to be abandoned.

The value that you pass to the STARTDATE parameter must be in epoch time. You can use the WebCenter Sites DATE.CONVERT tag to obtain an epoch value for the date to use. For an example, see the code example for the VDM.FLUSHINACTIVE tag provided in the preceding section.

For more information about these tags, see the Tag Reference for Oracle WebCenter Sites.

Note:

This object method does not affect the VMSCALARBLOB table, which means that it does not delete carts that you have stored as a visitor attribute of type binary (a scalar object).

43.7.3 Deleting Unnecessary .class Files

Engage is a Java-based application and it generates Java .class files each time an event occurs:

A segment, recommendation, or promotion is created.
A product is configured for a related items recommendation.
A segment, recommendation, or promotion is calculated or called by Engage.

Typically, old .class files are deleted when a segment, recommendation, promotion, or product is updated and are then replaced with new .class files. However, if the segment, recommendation, product, or promotion is in use when an updated version is published, Engage cannot delete the old .class file because it is locked.

The old .class files can build up, filling up the disk and using memory. Therefore, depending on how much development work you are doing and how frequently you publish to the delivery system, you must manually delete these .class files at a regularly scheduled time.

Complete the following steps to delete old .class files:

Use the Property Management Tool to examine the vis.genclasspath property and note the directory name designated by that parameter. This is the directory where Engage stores .class files.
During a quiet time on your site, shut down and restart each instance of JRE runtime that is running. This process releases any old .class files that the JRE runtime has locked.
Using any file management tool, navigate to the directory that holds the .class files and delete the contents of this directory.

Engage regenerates any .class files that it requires.