List Table Max ID and Row Count for AGILEOBJECTIDSEQUENCE Sequence on Agile Product Lifecycle Management (PLM) System (Doc ID 2954695.1)

List table maximum row ID and row count for each table using the AGILEOBJECTIDSEQUENCE.

 

Scope

This document applies to the following products and versions:

·         Oracle Agile PLM version 9.3.6

·         Oracle Agile PLM version 9.3.3, 9.3.4, and 9.3.5 with limited support.

 

Note: Agile PLM version 9.3.3, 9.3.4, and 9.3.5 are in Sustaining Support.  Any issue rising from the execution of the scripts attached to this Knowledge Management (KM) article cannot involve Oracle Development on these versions of the product.  Sustaining Support policy details can be obtained at https://www.oracle.com/us/support/library/057419.pdf 

 

Purpose

Sequence generators, which are database objects, provide unique integer values used by the Agile application. These are often used for applying a relationship between two objects on the system. This can be a primary key for a table or unique value for an item, such as a part number.  Sequence numbers are incremented sequentially and are independent of any transaction.  One or more transactions can concurrently increment the same sequence and neither one can obtain the value of the other transaction. This means sequence numbers acquired by each transaction may have gaps since some of the available values may have been used by other transaction, or by transactions being rolled back.

 

The primary sequence in the Agile system is called AGILEOBJECTIDSEQUENCE and it is used for generating primary keys for more than 90 tables in the database.  Systems that have been in use for many years may have depleted a large number of these unique values and once the maximum value is reached, which is 2,147,483,647, the system will come to a halt until mitigation steps are taken.  It is important to get the current state of this sequence on these older systems.  The script attached to this KM article will list the tables using this sequence with their maximum ID number along with the row count. The information gathered by this script will determine if mitigation steps are needed to extend the life of this sequence.

 

Note: The script attached to this KM article will not gather any sensitive business data. The information gathered by this script should come from a production system. It will gather only diagnostic information and it is completely safe to run on a production system while the system is in use.