How to Generate Data Dictionary in Oracle?

Data Dictionary in Oracle

Because it serves as the repository for all of the system’s metadata, exploring Oracle’s Data Dictionary is essential to comprehending how the database functions. The Data Dictionary (DD), which is frequently referred to as Oracle’s internal directory, contains important details about the database’s objects, security, physical structure, and dynamic performance measures.

Understanding Oracle’s Data Dictionary

In essence, metadata is data about data, and it comes from the Oracle Data Dictionary. Important information about each object in the database is recorded, such as the object owner, definition, and associated privileges.

The DD itself is made up of numerous viewpoints that are typically divided into three major groups according to the prefix that appears in their names:

1. USER_ views: Views that show details on items that belong to the user account that is now linked are called USER_views.
2. ALL_views: These comprise the current user’s objects as well as details about objects to which the user has been granted access (either directly or through PUBLIC).
3. DBA_ views: These include all database objects, irrespective of who owns them, and are usually accessible only with DBA rights.

The Data Dictionary’s static views, which include objects like tables, indexes, and users, are based on internal Oracle tables that are mostly kept in the SYSTEM tablespace. These tables include USER$, TAB$, and IND$. Usually, scripts like catalog.sql are used to create the database, which instantiates these views.

By utilising these Data Dictionary views, we can acquire important knowledge about the code and structures present in the database.

Inspecting Database Objects with USER_OBJECTS

A detailed list of all objects held by the currently connected schema can be viewed using the USER_OBJECTS view, also referred to by its public synonym OBJ. It is among the most popular perspectives in the Data Dictionary.

USER_OBJECTS provides information about various object types, including tables, indexes, synonyms, sequences, procedures, functions, packages, triggers, views, and abstract datatypes. Key information retrieved includes the object’s name (Object_Name), type (Object_Type), creation timestamp (Created), and its current status (Status), indicating if it is VALID or INVALID. The Last_DDL_Time column records the timestamp for the most recent DDL command executed on the object.

Creating a basic table and a function (a procedural object) initially, then querying USER_OBJECTS to confirm their existence and status, will illustrate exploration.

Code: Create table and function

-- 1. Create a table for demonstration
CREATE TABLE DD_TEST_TABLE (
    ID NUMBER(10),
    NAME VARCHAR2(50)
);

-- 2. Create a simple function
CREATE OR REPLACE FUNCTION GET_NAME (p_id IN NUMBER)
RETURN VARCHAR2
IS
  v_name DD_TEST_TABLE.NAME%TYPE;
BEGIN
  SELECT NAME INTO v_name FROM DD_TEST_TABLE WHERE ID = p_id;
  RETURN v_name;
END GET_NAME;
/

Code: Query USER_OBJECTS

SELECT object_name, object_type, status, created
FROM user_objects
WHERE object_name IN ('DD_TEST_TABLE', 'GET_NAME')
ORDER BY object_type, object_name;

Output (Conceptual):

Inspecting Tables with ALL_TABLES

Although USER_OBJECTS verifies the presence and type of an object, views such as USER_TABLES (or its synonym, TABS) offer far more detailed table-related information. The ALL_TABLES view broadens this scope by showing details about every table that the current user is authorised to access for DML operations. Finding tables in the database that belong to other users is made much easier using this.

You would normally use USER_TABLES if you are only interested in tables that belong to your current schema. ALL_TABLES has an Owner column in addition to the columns found in USER_TABLES since it can hold entries for numerous users.

We can verify the ownership and general condition of our newly created table by running a query against ALL_TABLES.

Code: Query ALL_TABLES

SELECT owner, table_name, tablespace_name
FROM all_tables
WHERE table_name = 'DD_TEST_TABLE';

Output (Conceptual):

Inspecting Database Code with USER_SOURCE

The database’s data dictionary contains the source code for any stored program units, including functions, packages, triggers, and procedures. Any procedural object that a user owns can have its source code queried using the USER_SOURCE view.

The source code for objects is broken down into separate records in USER_SOURCE, with key columns providing the object Name, object Type, the Line number of the code, and the Text content of that line.

We query USER_SOURCE in order to obtain and examine the code for the GET_NAME function that was previously built. The Text column is usually selected by queries against this view, and in order to present the source code logically, they must be arranged by Line number.

Code: Insert data into the table for completeness

Although inserting data is not required for viewing metadata, DML statements are used to manipulate data in existing schema objects.

INSERT INTO DD_TEST_TABLE (ID, NAME) VALUES (1, 'METADATA_ROW');
COMMIT;

Code: Query USER_SOURCE

SELECT line, text
FROM user_source
WHERE name = 'GET_NAME'
AND type = 'FUNCTION'
ORDER BY line;

Output:

The PL/SQL source code that was initially placed in the data dictionary is successfully retrieved by this output. For troubleshooting, dependency analysis, and application maintenance, having this kind of access to source code is essential. Similar access is provided by ALL_SOURCE and DBA_SOURCE, for example, but across all objects in the database or all objects accessible to the user, respectively.

In conclusion, database professionals can directly access the metadata supporting their applications by utilising views such as USER_OBJECTS, ALL_TABLES, and USER_SOURCE to explore the Oracle Data Dictionary. This allows for effective object inspection, object status verification, and stored procedural code review.