Various Naming Conventions of Oracle Tables

Various Naming Conventions of oracle Tables

_ALL

These tables hold information of different operating units

Examples: HR_LOCATIONS_ALL, OE_ORDER_HEADERS_ALL, AP_AE_HEADERS_ALL, AP_AE_LINES_ALL etc.

_B

These are the base tables in Oracle. They are important because they contain data in table with all validations. If any issue occurs on these tables, then we can see a data corruption issue.

Example: MTL_SYSTEM_ITEMS_B, MTL_ITEM_CATEGORIES_B, MTL_CATEGORY_SETS_B, MTL_ITEM_REVISIONS_B, MTL_ITEM_TEMPLATES_B, QP_SEGMENTS_B, PA_PROJECT_SETS_B etc.

_A

The tables ending with _A are Audit Shadow Tables

Example: PO_VENDORS_A, FND_AUDIT_TABLES_A etc.

All Audit Trail shadow tables contain certain special auditing columns. These columns include:

• AUDIT_USER_NAME (the Application User ID, except when changes are applied using SQL*Plus, in which case it is the Oracle ID)

• AUDIT_TIMESTAMP (the date/time when the insertion occurred)

• AUDIT_TRANSACTION_TYPE (I for Insert, U for Update, D for Delete, L for Last, and C for Current)

• AUDIT_TRUE_NULLS (VARCHAR2(250) column containing a delimited list of column names that have changed from NULL)

_AVN and _ACN are Audit Shadow Views 

-V

_V tables are the views created for the base tables

Examples: MTL_ITEM_CATEGORIES_V is view for MTL_ITEM_CATEGORIES

_S

Table ending with _S are sequence tables

_TL

These are the tables corresponding to same table minus _TL, they provide multi language support

Example: MTL_ITEM_REVISIONS_TL resembles MTL_ITEM_REVISIONS but you will find language column, source_lang column in addition.

_F

These are date tracked tables. They can be found mostly in HR and Payroll Modules. EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE are two date columns which together with PK define a row uniquely.

Example: HR_S_GLOBALS_F, HR_S_FORMULAS_F etc.

_VL

_VL are the views for multi-language tables which combines row of base tables with corresponding row of _TL tables