Results for “ad_snapshot_files”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AD_SNAPSHOT_FILES is a core Applications DBA (AD) repository table owned by the APPLSYS schema in Oracle E-Business Suite releases 12.1.1 and 12.2.2. It records the file-level detail captured within an Applications snapshot. In EBS, a snapshot is a point-in-time inventory of the application tier — the consolidated set of files that make up an APPL_TOP and its associated product directories — and AD_SNAPSHOT_FILES is the table that resolves each snapshot down to the individual files it contained, together with integrity, versioning, and topology attributes for those files.

The table therefore sits at the intersection of two modeling concerns: it enumerates the membership and physical characteristics of files in a snapshot, and it links snapshots to the central file registry and file version history. Under the heuristic Data Vault classification supplied in the metadata, AD_SNAPSHOT_FILES is best treated as a link table — it associates a snapshot with files (and their versions, containment parents, and APPL_TOP placements) rather than serving as a pure reference hub or a descriptive satellite. This is consistent with its dependency structure: nearly every non-key column either describes the file's state at snapshot time or points to another entity.

Key Information Stored

The table contains 23 documented columns. The most consequential are:

• SNAPSHOT_FILE_ID — the surrogate primary key, enforced by AD_SNAPSHOT_FILES_PK and mirrored by unique index AD_SNAPSHOT_FILES_U1.
• SNAPSHOT_ID — foreign key to AD_SNAPSHOTS, identifying the parent snapshot.
• FILE_ID and CONTAINING_FILE_ID — both referencing AD_FILES; FILE_ID identifies the file entry, while CONTAINING_FILE_ID models the archive or container relationship (for example, a file held inside a consolidated bundle).
• FILE_VERSION_ID — foreign key to AD_FILE_VERSIONS, tying the snapshot entry to a specific version of the file.
• APPL_TOP_ID — foreign key to AD_APPL_TOPS, indicating which application top the file resided in.
• FILE_SIZE and CHECKSUM — the physical size and integrity digest of the file as snapshot at that time, used for comparison and change detection.
• UPDATE_TYPE and UPDATE_SOURCE_ID — classification of how the file was updated and the source of that change (for example, a patch or AutoConfig run).
• FILE_TYPE_FLAG, INCONSISTENT_FLAG, and LAST_PATCHED_DATE — file category, an indicator that the file's state diverged from the expected snapshot baseline, and the last patch application date.
• SERVER_TYPE_ADMIN_FLAG, SERVER_TYPE_FORMS_FLAG, SERVER_TYPE_NODE_FLAG, and SERVER_TYPE_WEB_FLAG — flags marking the server roles or tiers on which the file is relevant.
• DEST_FILE_ID — reference to a target file, and IREP_GATHERED_FLAG, denoting whether the file was gathered into the integrated repository.

The unique index AD_SNAPSHOT_FILES_U2 on (FILE_ID, SNAPSHOT_ID, CONTAINING_FILE_ID) is the strongest business-key candidate: it guarantees that a given file appears only once per snapshot under a given container. Standard audit columns (CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY) are also present.

Common Use Cases and Queries

Administrators and DBAs use this table to compare snapshots, audit patch impact, and detect file inconsistencies. A typical query reconstructs the file listing of a snapshot:

• List files in a snapshot: SELECT sf.SNAPSHOT_FILE_ID, f.FILE_NAME, sf.FILE_SIZE, sf.CHECKSUM FROM APPLSYS.AD_SNAPSHOT_FILES sf JOIN APPLSYS.AD_FILES f ON f.FILE_ID = sf.FILE_ID WHERE sf.SNAPSHOT_ID = :snapshot_id;
• Identify inconsistent files: SELECT * FROM APPLSYS.AD_SNAPSHOT_FILES WHERE INCONSISTENT_FLAG = 'Y';
• Compare two snapshots on size/checksum to find changed files: join two filtered instances of the table on FILE_ID and APPL_TOP_ID where FILE_SIZE or CHECKSUM differ.

Because the table carries server-type flags, it also supports tier-oriented reporting — determining which files belong to admin, forms, node, or web tiers — which is useful during cloning and multi-node deployments.

Related Objects

• AD_SNAPSHOTS — parent snapshot definition, joined on SNAPSHOT_ID.
• AD_FILES — the file registry, joined on FILE_ID and again on CONTAINING_FILE_ID.
• AD_FILE_VERSIONS — version history, joined on FILE_VERSION_ID.
• AD_APPL_TOPS — application top definitions, joined on APPL_TOP_ID.
• AD_SNAPSHOT_FILES_TEMP — a staging table whose SNAPSHOT_FILE_ID references this table's primary key, used during snapshot generation and reconciliation.
• AD_SNAPSHOT_FILES_U1 / AD_SNAPSHOT_FILES_U2 — unique indexes enforcing identity and business-key uniqueness.