My account Book a demo
Oracle E-Business Suite Data Model & Metadata KB

Data Dictionary in Oracle e-Business Suite

Oracle EBS has a huge and complex database schema with over 20k tables. It is quite hard to navigate it and find the data in tables and columns. What you need for this task is a data dictionary for Oracle EBS database. It comes in a few flavors and I will break it donw for you here.

1. Oracle database Data Dictionary Views

To find the data you can use standard system catalog / data dictionary views built in every Oracle database. This is what you are probably already using while working with Oracle or other databases.

Navigate with database tool

To browse schema you can use any database tool for this, like Oracle SQL Developer. Those tools however have limited capabilities to search schema.

Query with SQL

To get more insight you can use SQL queries to the data dictionary views to analyze and extract information about schema.

Bonus: Free database of convenient queries to Oracle data dictionary

What you get

Standard Oracle data dictionary gives you list of tables, columns, data types, views, trigges, stored procedures and all the schema elements. However, it doesn't have all you need.

What it is missing is:

  • foreign keys constraints, which makes it hard to find table relationships,
  • table and column comments in the schema, which makes it hard to understand the purpose of the data.

There is a solution to this - second data dictionary - FND tables.

2. Oracle EBS data dictionary (FND tables)

Oracle EBS is shipped with its own data dictionary on top of standard Oracle database system catalog. It is accessible through tables in FND schema.

What you get

Those tables hold additional information:

  • descriptions of tables, views, columns, etc.
  • foreign keys

Tables

Data is available in following tables:

  • FND_TABLES - tables
  • FND_VIEWS - views
  • FND_COLUMNS - table and column views
  • FND_FOREIGN_KEYS - foreign keys
  • FND_FOREIGN_KEY_COLUMNS - foreign key columns
  • FND_INDEXES - unique and non-unique indexes
  • FND_INDEX_COLUMNS - index columns

Resources on EBS data dictionary

3. Referece documentation

  • Oracle eTRM (e-Technical Reference Manuals) - for Oracle customers only
  • Technical Reference Manuals (PDF) - available online if you Google it (probably against Oracle's will)
  • Various EBS derrived products doucmetation availabl on Oracle website - here's a sample of data dictionary for Pocurement application

What you get

  • More context
  • Database diagrams
  • Convenient searchable form