In an Oracle database, to extract object metadata, we can use the DBMS_METADATA package. It provides many useful procedures and functions, such as GET_DDL, GET_DEPENDENT_DDL, and SET_TRANSFORM_PARAM. Additionally, some tools, like SQLcl, provide their own methods for extracting metadata—for example, the DDL command.

Starting with version 23.7, Oracle introduced another method to extract object metadata. In this version, we can use DBMS_DEVELOPER alongside DBMS_METADATA. DBMS_DEVELOPER is user-friendly, and generates output in JSON format.

The DBMS_DEVELOPER package currently supports only TABLE, INDEX, and VIEW, while DBMS_METADATA is much more powerful and supports nearly all object types.

DBMS_DEVELOPER has only one function, called GET_METADATA:

SQL> DESC DBMS_DEVELOPER
FUNCTION GET_METADATA RETURNS JSON
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 NAME                           VARCHAR2                IN
 SCHEMA                         VARCHAR2                IN     DEFAULT
 OBJECT_TYPE                    VARCHAR2                IN     DEFAULT
 LEVEL                          VARCHAR2                IN     DEFAULT
 ETAG                           RAW                     IN     DEFAULT

This function provides output at three levels:
– Basic: Returns only basic information about the object.
– Typical: Returns more detailed information about the object.
– All: Provides all available details about the object.

The output level can be specified using the LEVEL argument.

Usage Examples

The following examples help illustrate how this package works.

Example 1: Retrieving Metadata for a Table (Basic Level)

SQL> select DBMS_DEVELOPER.GET_METADATA (schema =>'VAHID' ,name =>'TB1' ,object_type =>'TABLE' ,level=>'BASIC' ) METADATA;

METADATA
--------------------------------------------------------------------------------
{"objectType":"TABLE","objectInfo":{"name":"TB1","schema":"VAHID","columns":[{"n

As you can see, the output is incomplete. We can use the JSON_SERIALIZE function to display the full output.

Example 2: Using JSON_SERIALIZE to Format Output

SQL>  select JSON_SERIALIZE(DBMS_DEVELOPER.GET_METADATA (schema =>'VAHID' ,name =>'TB1' ,object_type =>'TABLE' ,level=>'BASIC' )) METADATA;

METADATA
-----------------------------------------------------
{"objectType":"TABLE","objectInfo":{"name":"TB1","schema":"VAHID","columns":[{"name":"ID","notNull":true,"dataType":{"type":"NUMBER"}},{"name":"NAME","default":"'Vahid'\n","notNull":true,"dataType":{"type":"VARCHAR2","length":100,"sizeUnits":"BYTE"}},{"name":"LAST_NAME","notNull":false,"dataType":{"type":"VARCHAR2","length":100,"sizeUnits":"BYTE"}}]},"etag":"E8012E8ED81678A603CE5D926BCE3F30"}

Example 3: Pretty-Printing JSON Output

SQL>  select JSON_SERIALIZE(DBMS_DEVELOPER.GET_METADATA (schema =>'VAHID' ,name =>'TB1' ,object_type =>'TABLE' ,level=>'BASIC' )pretty ) METADATA;

METADATA
-----------------------------------------------------

{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "name" : "TB1",
    "schema" : "VAHID",
    "columns" :
    [
      {
        "name" : "ID",
        "notNull" : true,
        "dataType" :
        {

          "type" : "NUMBER"
        }
      },
      {
        "name" : "NAME",
        "default" : "'Vahid'\n",
        "notNull" : true,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 100,
          "sizeUnits" : "BYTE"
        }

      },
      {
        "name" : "LAST_NAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 100,
          "sizeUnits" : "BYTE"
        }
      }
    ]
  },
  "etag" : "E8012E8ED81678A603CE5D926BCE3F30"
}

Example 4: Retrieving Metadata at the ALL Level

SQL> select JSON_SERIALIZE(DBMS_DEVELOPER.GET_METADATA (schema =>'VAHID' ,name =>'TB1' ,object_type =>'TABLE' ,level=>'ALL' )pretty ) METADATA;

METADATA
-----------------------------------------------------
{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "name" : "TB1",
    "schema" : "VAHID",
    "columns" :
    [
      {
        "name" : "ID",
        "notNull" : true,
        "dataType" :
        {

          "type" : "NUMBER"
        },
        "isPk" : true,
        "isUk" : true,
        "isFk" : false,
        "hiddenColumn" : false
      },
      {
        "name" : "NAME",
        "default" : "'Vahid'\n",
        "notNull" : true,
        "dataType" :
        {

          "type" : "VARCHAR2",
          "length" : 100,
          "sizeUnits" : "BYTE"
        },
        "isPk" : false,
        "isUk" : false,
        "isFk" : false,
        "hiddenColumn" : false
      },
      {
        "name" : "LAST_NAME",
        "notNull" : false,
        "dataType" :

        {
          "type" : "VARCHAR2",
          "length" : 100,
          "sizeUnits" : "BYTE"
        },
        "isPk" : false,
        "isUk" : false,
        "isFk" : false,
        "hiddenColumn" : false
      }
    ],
    "hasBeenAnalyzed" : false,
    "indexes" :

    [
      {
        "name" : "SYS_C008430",
        "indexType" : "NORMAL",
        "uniqueness" : "UNIQUE",
        "status" : "VALID",
        "hasBeenAnalyzed" : false,
        "columns" :
        [
          {
            "name" : "ID"
          }
        ],

        "compression" : "DISABLED",
        "segmentCreated" : "NO",
        "visiblilty" : "VISIBLE",
        "toBeDropped" : false
      }
    ],
    "constraints" :
    [
      {
        "name" : "SYS_C008429",
        "constraintType" : "CHECK - NOT NULL",
        "searchCondition" : "\"NAME\" IS NOT NULL",
        "columns" :

        [
          {
            "name" : "NAME"
          }
        ],
        "status" : "ENABLE",
        "deferrable" : false,
        "validated" : "VALIDATED",
        "sysGeneratedName" : true
      },
      {
        "name" : "SYS_C008430",
        "constraintType" : "PRIMARY KEY",
        "columns" :
        [
          {
            "name" : "ID"
          }
        ],
        "status" : "ENABLE",
        "deferrable" : false,
        "validated" : "VALIDATED",
        "sysGeneratedName" : true
      }
    ],
    "segmentCreated" : "NO",

    "inMemory" : "DISABLED",
    "compression" : "DISABLED"
  },
  "etag" : "C478C48865258F4DD166DC1319195A4C"
}

Tracking Changes with ETAG

The DBMS_DEVELOPER.GET_METADATA function has another argument called ETAG. This parameter is a unique identifier assigned to the metadata document. If you examine the last output, you will see the ETAG value:

“etag” : “C478C48865258F4DD166DC1319195A4C”

If you run this query again, the ETAG value will remain unchanged unless the metadata has been modified by someone. If the ETAG matches the current version, GET_METADATA will return an empty document.

Example 5: Using ETAG for Change Detection

SQL> select JSON_SERIALIZE(DBMS_DEVELOPER.GET_METADATA (schema =>'VAHID' ,name =>'TB1' ,object_type =>'TABLE' ,level=>'ALL',  etag=> 'C478C48865258F4DD166DC1319195A4C' )pretty ) METADATA;

METADATA
-----------------------
{
}

I will remove a column from TB1 and run the query again:

SQL> alter table vahid.TB1 drop column last_name;
Table altered.

Example 6: Modifying a Table and Checking ETAG

SQL> select JSON_SERIALIZE(DBMS_DEVELOPER.GET_METADATA (schema =>'VAHID' ,name =>'TB1' ,object_type =>'TABLE' ,level=>'ALL',  etag=> 'C478C48865258F4DD166DC1319195A4C' )pretty ) 

METADATA;
METADATA
-----------------------
{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "name" : "TB1",
    "schema" : "VAHID",
….
    "compression" : "DISABLED"
  },
  "etag" : "0BFA3ACD50E33DD69F7E68897C70A7E9"
}

The ETAG in the JSON output will reflect the change, confirming the metadata update.

Conclusion

The DBMS_DEVELOPER package was introduced in Oracle 23.7 and produces metadata in JSON format. It supports only TABLE, INDEX, and VIEW. The GET_METADATA function provides output at BASIC, TYPICAL, and ALL levels. The ETAG parameter helps track changes in object metadata.