Search Results hr_dm_databases_pk

Overview

The HR.HR_DM_DATABASES table is a reference data object within the Oracle E-Business Suite 12.1.1 and 12.2.2 database schema, owned by the HR schema and registered in the application as FND Design Data PER.HR_DM_DATABASES. Its documented purpose is to contain the names of all databases participating in a database farm configuration. In practice, the table acts as a registry that identifies the individual database instances that Oracle EBS Data Migration and related HR utilities use when coordinating distributed operations, sequence generation, and data movement across a farm of databases.

Based on the foreign key topology provided in the ETRM metadata, the table exhibits hub-leaning characteristics in a Data Vault model. It carries a stable, system-generated surrogate primary key and a natural business key, and it is referenced by multiple dependent tables. Where a formal Data Vault design is applied, it would be reasonable to model this object as a hub, with the referencing tables implemented as links or satellites. This is a modeling suggestion rather than a documented EBS classification.

Key Information Stored

The table is documented with ten columns. The most significant are summarized below.

• DATABASE_ID — NUMBER(15), mandatory. The system-generated primary key and the column most frequently used in joins. It is the anchor of the HR_DM_DATABASES_PK unique index.
• DATABASE_NAME — VARCHAR2(30), a unique database name intended to correspond to the TNS name of the database. This is the business-key candidate, enforced by the HR_DM_DATABASES_UK1 unique index.
• DESCRIPTION — VARCHAR2(150), free-text description of the database.
• USER_NAME — VARCHAR2(30), the APPS user name associated with the database connection.
• SECURITY_GROUP_ID — NUMBER(15), a foreign key to FND_SECURITY_GROUPS used for data security grouping.
• Standard WHO columns — LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, and CREATION_DATE provide audit traceability for each row.

The distinction between the surrogate key (DATABASE_ID) and the business-key candidate (DATABASE_NAME) is important: joins between transactional or history tables and this registry are typically performed on DATABASE_ID, while external or cross-instance identification relies on DATABASE_NAME.

Common Use Cases and Queries

The primary use case is resolving a database identifier to a human-readable instance name for reporting on database farm activity. Administrators and developers use it to audit which databases have contributed to sequence histories or sequence ranges, and to identify the APPS user configured for each registered database.

A typical lookup resolves a name to an identifier:

• SELECT database_id, database_name, description FROM hr.hr_dm_databases WHERE database_name = :tns_name;

Aggregate reporting groups activity by database:

• SELECT d.database_id, d.database_name, COUNT(h.database_id) FROM hr.hr_dm_databases d LEFT JOIN hr.hr_dm_sequence_histories h ON d.database_id = h.database_id GROUP BY d.database_id, d.database_name;

Because the table is stored in the APPS_TS_INTERFACE tablespace with PCT Free 10 and is exposed through the APPS.HR_DM_DATABASES synonym and the HR_DM_DATABASES_WHO view (installed by the WHO column handling), queries should be issued against the APPS synonym in application code rather than the HR base table.

Related Objects

The following objects reference or are referenced by HR.HR_DM_DATABASES, based on documented foreign key relationships:

• HR.HR_DM_SEQUENCE_HISTORIES — references HR_DM_DATABASES through its DATABASE_ID column, recording the origin database for sequence history records.
• HR.HR_DM_SEQUENCE_RANGES — references HR_DM_DATABASES through DATABASE_ID, associating allocated sequence ranges with the owning database.
• BISM_DATABASE — references HR_DM_DATABASES through DATABASE_ID, extending the registry into business intelligence or data migration staging contexts.
• FND_SECURITY_GROUPS — the parent of the SECURITY_GROUP_ID foreign key, controlling row-level visibility.
• APPS.HR_DM_DATABASES — the APPS synonym used by runtime code.
• APPS.HR_DM_DATABASES_WHO — the WHO-column maintenance view installed by the applications framework.

The documented dependency note confirms that HR_DM_DATABASES does not reference any database object other than the security group, which reinforces its role as a foundational registry rather than a transactional entity.