Oracle Database 26ai introduces DBMS_DEVELOPER, a package that lets applications and developer tools retrieve structured metadata about database objects in JSON format. Starting with Oracle Database 19c Release Update 19.28, it is also available in Oracle Database 19c.

As you know, Oracle provides several ways to provide ways to retrieve metadata and inspect database objects such as SQL*Plus, SQL Developer, Oracle SQL Developer for VS Code, and SQLcl. For example, SQLcl provides commands such as INFO and DDL:

SQL> info hr.countries

The output looks like …

TABLE: COUNTRIES
         LAST ANALYZED:2025-02-05 16:20:42.0
         ROWS         :25
         SAMPLE SIZE  :25
         INMEMORY     :DISABLED
         COMMENTS     :country table. References with locations table.
Columns
NAME           DATA TYPE           NULL  DEFAULT    COMMENTS
*COUNTRY_ID    CHAR(2 BYTE)        No               Primary key of countries table.
 COUNTRY_NAME  VARCHAR2(60 BYTE)   Yes              displayed
 REGION_ID     NUMBER              Yes              Region ID for the country. Foreign key to
                                                    region_id column in the departments table.
Indexes
INDEX_NAME            UNIQUENESS    STATUS    FUNCIDX_STATUS    COLUMNS
_____________________ _____________ _________ _________________ _____________
HR.COUNTRY_C_ID_PK    UNIQUE        VALID                       COUNTRY_ID

References
TABLE_NAME    CONSTRAINT_NAME    DELETE_RULE    STATUS     DEFERRABLE        VALIDATED   GENERATED
_____________ __________________ ______________ __________ _________________ ___________ ____________
LOCATIONS     LOC_C_ID_FK        NO ACTION      ENABLED    NOT DEFERRABLE    VALIDATED   USER NAME

You can also use DDL to generate the object definition:

SQL> ddl hr.countries

This is the output:

CREATE TABLE "HR"."COUNTRIES"    
("COUNTRY_ID" CHAR(2) COLLATE "USING_NLS_COMP" CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE,         
"COUNTRY_NAME" VARCHAR2(60) COLLATE "USING_NLS_COMP",         
"REGION_ID" NUMBER,          
CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") ENABLE,         CONSTRAINT "COUNTR_REG_FK" FOREIGN KEY ("REGION_ID")           
  REFERENCES "HR"."REGIONS" ("REGION_ID") ENABLE    
)  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE   
ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1   
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)   
TABLESPACE "USERS"  
PCTTHRESHOLD 50;    
COMMENT ON COLUMN "HR"."COUNTRIES"."COUNTRY_ID" IS 'Primary key of countries table.';    
COMMENT ON COLUMN "HR"."COUNTRIES"."COUNTRY_NAME" IS 'displayed';    
COMMENT ON COLUMN "HR"."COUNTRIES"."REGION_ID" IS 'Region ID for the country. Foreign key to region_id column in the departments table.';    
COMMENT ON TABLE "HR"."COUNTRIES"  IS 'country table. References with locations table.'; 
Operation completed successfully

Another important option is the PL/SQL package DBMS_METADATA. It is particularly useful when you need object definitions in DDL or XML format, or when you want to use the metadata to recreate an object. For more information, see the Further Readings section below.

So, what about this new DBMS_DEVELOPER package? In the sections below, we will explore how to use it and demonstrate its capabilities with several practical examples.

This post is organized into the following sections:

  • Getting started with DBMS_DEVELOPER
  • Retrieving Metadata for Tables at the BASIC Level
  • Retrieving Metadata for Tables at the TYPICAL Level
  • Using ETAG with GET_METADATA
  • Retrieving Metadata for Views at the ALL Level
  • Retrieving Metadata for JSON Duality Views at the ALL Level

Getting started with DBMS_DEVELOPER

You might now be wondering: why use the new DBMS_DEVELOPER package? Here are some of its key advantages:

  • Very fast retrieval
  • Structured JSON output
  • Documented JSON schema for the supported object types and detail levels
  • Least-privilege access, typically SELECT or READ
  • Change detection using the ETAG parameter
  • One interface across object types and different detail levels

These characteristics make DBMS_DEVELOPER especially useful for IDEs, database browsers, code-generation tools, automation, and other applications that need up-to-date database object metadata.

Currently, the package includes a single function: GET_METADATA. You provide the information needed to identify the object, and the function returns a JSON document containing its metadata. The syntax is as follows:

DBMS_DEVELOPER.GET_METADATA (  
name            IN VARCHAR2,  
schema          IN VARCHAR2 DEFAULT NULL,  
object_type     IN VARCHAR2 DEFAULT NULL,  
level           IN VARCHAR2 DEFAULT 'TYPICAL'  
etag            IN RAW      DEFAULT NULL)
RETURN JSON;

So, how do you use it? The object_type argument currently supports TABLE, INDEX, and VIEW. The level argument controls how much detail is returned: BASIC, TYPICAL, or ALL. The etag argument identifies a specific version of the document, allowing an application to determine whether two document versions contain the same content.
The structure of the returned JSON depends on the selected object type and detail level. Oracle documents the corresponding JSON schemas in the following tables:

Let’s start with a simple example using a nonprivileged user named DEVMETA. This user has only the privileges needed for our examples, including select privilege for the HR.COUNTRIES table, the HR.EMP_VIEW_DETAILS view, and the CHUNKS_DV JSON duality view.

If you don’t already have this user, create it as follows:

create user devmeta identified by '&password';  
grant connect to devmeta;
grant select on &objectname to devmeta;

Retrieving Metadata for Tables at the BASIC Level

For our first example, we’ll retrieve metadata for the HR.COUNTRIES table at the BASIC level. To make the output easier to read, we’ll use JSON_SERIALIZE with the PRETTY option, which formats the JSON document as human-readable text.

SET LONG 10000 HEADING OFF

select JSON_SERIALIZE(
            DBMS_DEVELOPER.GET_METADATA(
                schema => 'HR',
                name   => 'COUNTRIES',
                level  => 'BASIC'  ) pretty) result;

Executing this query produces the following JSON document:

{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "name" : "COUNTRIES",
    "schema" : "HR",
    "columns" :
    [
      {
        "name" : "COUNTRY_ID",
        "notNull" : true,
        "dataType" :
        {
          "type" : "CHAR",
          "length" : 2,
          "sizeUnits" : "BYTE"
        }
      },
      {
        "name" : "COUNTRY_NAME",
        "notNull" : false,
        "dataType" :
        {
          "type" : "VARCHAR2",
          "length" : 60,
          "sizeUnits" : "BYTE"
        }
      },
      {
        "name" : "REGION_ID",
        "notNull" : false,
        "dataType" :
        {
          "type" : "NUMBER"
        }
      }
    ]
  },
  "etag" : "C430DC814B06E0408ADF8515377CD2CF"
}

The resulting JSON document at level BASIC is based on the following JSON schema for the object type TABLE (see also the documentation).

{
"$schema": "https://json-schema.org/draft/2020-12/schema",
"$id":"https://oracle.com/schema/23.26.2/DBMS_DEVELOPER.GET_METADATA/BASIC/object_info/table",
"title": "DBMS_DEVELOPER.GET_METADATA/BASIC/OBJECT_INFO/TABLE",
 "description": "Information for a table object",
 "type": "object",
 "properties": {
        "name": {
            "description": "Table name",
            "type": "string"
        },
        "schema": {
            "description": "Table schema",
            "type": "string"
        },
        "columns": {
            "description": "Table columns",
            "type": "array",
            "items": {
                "$ref": "../column"
            }
        }
    },
    "required": [
        "name",
        "schema",
        "columns"
    ]
}

Retrieving Metadata for Tables at the TYPICAL Level

Now let’s try out the default value TYPICAL. As you can see we do receive more information such as constraints, number of rows, indexes etc.

SET LONG 10000 HEADING OFF

select JSON_SERIALIZE(
            DBMS_DEVELOPER.GET_METADATA(
                schema => 'HR',
                name   => 'COUNTRIES',
                level  => 'TYPICAL'     ) pretty) result;

Here is an excerpt of the resulting JSON document.

...
{   
   {
        "name" : "REGION_ID",
        "notNull" : false,
        "dataType" :
        {
          "type" : "NUMBER"
        },
        "isPk" : false,
        "isUk" : false,
        "isFk" : true
      }
    ],

    "numRows" : 25,
    "external" : "NO",
    "constraints" :
    [
      {
        "name" : "COUNTRY_ID_NN",
        "constraintType" : "CHECK - NOT NULL",
        "searchCondition" : "\"COUNTRY_ID\" IS NOT NULL",
        "columns" :
        [
...
"etag" : "C430DC814B06E0408ADF8515377CD2CF" }

USING ETAG with GET_METADATA

Let’s see how ETAG works in practice. An ETAG identifies a specific version of an object’s metadata and allows an application to determine whether that metadata has changed since a previous GET_METADATA call.
Therefore, the rules are as follows:

  • If the supplied ETAG matches the current metadata ETAG, GET_METADATA returns an empty JSON document.
  • If the supplied ETAG does not match, GET_METADATA returns the current metadata document, including the updated ETAG.

To demonstrate this behavior, the HR user will first modify the COUNTRIES table by adding an annotation to the COUNTRY_NAME column:

alter table hr.countries modify country_name annotations (add display 'CNAME');

This change updates the table’s metadata and therefore produces a new ETAG. Reconnect as DEVMETA and call DBMS_DEVELOPER.GET_METADATA again, leaving the level argument at its default value, TYPICAL. Then compare the returned ETAG with the value from the previous call.

SET LONG 10000 HEADING OFF

select JSON_SERIALIZE(
            DBMS_DEVELOPER.GET_METADATA(
                   schema => 'HR',
                   name   => 'COUNTRIES') pretty) result;
{
  "objectType" : "TABLE",
  "objectInfo" :
  {
    "schema" : "HR",
    "columns" :
    [
      {
        "name" : "COUNTRY_ID",
        "notNull" : true,
        "dataType" :
        {
          "type" : "CHAR",
          "length" : 2,
          "sizeUnits" : "BYTE"
        },
        "isPk" : true,
        "isUk" : true,
        "isFk" : false
      },
     
    ...
    ],
    "lastAnalyzed" : "2026-09-16T09:40:51",
    "sampleSize" : 25,
    "indexes" :
    [
      {
        "name" : "COUNTRY_C_ID_PK",
        "indexType" : "NORMAL",

        "uniqueness" : "UNIQUE",
        "status" : "VALID",
        "lastAnalyzed" : "2026-09-16T09:40:51",
        "numRows" : 25,
        "sampleSize" : 25,
        "partitioned" : false,
        "columns" :
        [
          {
            "name" : "COUNTRY_ID"
          }
        ]
      }
    ],
    "avgRowLen" : 16,
    "name" : "COUNTRIES",
    "temporary" : "NO"
  },
  "etag" : "A217DC0C71E237A0E751ECABA7D1FB1A"
}

The returned document includes the new annotation, and its current ETAG value “A217DC0C71E237A0E751ECABA7D1FB1A”.

Now pass the current ETAG value to the GET_METADATA.

select JSON_SERIALIZE(
            DBMS_DEVELOPER.GET_METADATA(   
               schema      => 'HR',   
               object_type => 'TABLE',   
               name        => 'COUNTRIES',   
               etag        => 'A217DC0C71E237A0E751ECABA7D1FB1A' ) 
returning clob pretty) result; 

RESULT 
-------------------------------------------------------------------------------- { }

Because the supplied ETAG matches the current value, the function returns an empty JSON document {}.

Retrieving Metadata for Views at the ALL Level

In the next example, let’s use the view EMP_DETAILS_VIEW from schema HR at level ALL. Information about referenced objects is added to the output (e.g. information about tables referenced by a view).

SET LONG 10000 HEADING OFF

select JSON_SERIALIZE (
        DBMS_DEVELOPER.GET_METADATA
                      (schema      => 'HR',                                             
                       object_type => 'VIEW',
                       name        => 'EMP_DETAILS_VIEW',
                       level       => 'ALL' )
returning clob pretty) result;
...
"constraints" :
    [
      {
        "name" : "SYS_C0037613",
        "constraintType" : "VIEW READONLY",
        "status" : "ENABLED",
        "deferrable" : false,
        "validated" : "NOT VALIDATED",
        "sysGeneratedName" : true
      }
    ],
    "references" :
    [
      {
        "schema" : "HR",
        "name" : "COUNTRIES",
        "objectType" : "TABLE"
      }
    ],
    "editioningView" : false,
    ...

Retrieving Metadata for JSON Duality Views at the ALL Level

In this final example, let’s look at a JSON Duality View. Assume that the view CHUNKS_DV has been created in the AIUSER schema. When you request metadata at the ALL level, the result includes detailed information about the view, including the JSON schema that describes the structure of the documents it supports.
You can retrieve this schema separately by using DBMS_JSON_DESCRIBE function.

select JSON_SERIALIZE (DBMS_JSON_SCHEMA.DESCRIBE('CHUNKS_DV')pretty) result;

Here is an excerpt of the result:

{
  "title" : "CHUNKS_DV",
  "dbObject" : "AIUSER.CHUNKS_DV",
  "dbObjectType" : "dualityView",
  "dbObjectProperties" :
  [
    "insert",
    "update",
    "delete",
    "check"
  ],
  "type" : "object",
  "properties" :
  {
    "_id" :
    {
      "type" : "number",
      "extendedType" : "number",
      "dbAssigned" : true,
      "dbFieldProperties" :
      [
        "check"
      ]
    },
    "_metadata" :
    {
      "etag" :
      {
        "type" : "string",
        "extendedType" : "string",
        "maxLength" : 200
      },
      "asof" :
      {
        "type" : "string",
        "extendedType" : "string",
        "maxLength" : 20
      }
    },
    "name" :
    {
      "type" : "string",
      "extendedType" : "string",
      "maxLength" : 500,
      "dbFieldProperties" :
      [
        "update",
        "check"
      ]
  ...

You can also retrieve the JSON schema together with the view’s other metadata by using DBMS_DEVELOPER.GET_METADATA.

select JSON_SERIALIZE (DBMS_DEVELOPER.GET_METADATA
                              (schema      => 'AIUSER',
                               object_type => 'VIEW',
                               name        => 'CHUNKS_DV',
                               level       => 'all' )
returning clob pretty) result;

Because a JSON Duality View is represented as a VIEW object, specify object_type => 'VIEW'. With level => 'ALL', GET_METADATA returns the most comprehensive metadata available, including the JSON schema, in a single call.

{
  "objectType" : "VIEW",
  "objectInfo" :
  {
    "name" : "CHUNKS_DV",
    "schema" : "AIUSER",
    "columns" :
    [
      {
        "name" : "DATA",
        "notNull" : false,
        "dataType" :
        {
...
 ],
    "jsonSchema" :
    {
      "title" : "CHUNKS_DV",
      "dbObject" : "AIUSER.CHUNKS_DV",
      "dbObjectType" : "dualityView",
      "dbObjectProperties" :
      [
        "insert",
        "update",
        "delete",
        "check"
      ],
      "type" : "object",
      "properties" :
      {
        "_id" :
        {
          "type" : "number",
          "extendedType" : "number",
          "dbAssigned" : true,
          "dbFieldProperties" :
          [
            "check"
          ]
        },
        "_metadata" :
        {
          "etag" :

This example shows how GET_METADATA can provide both general object metadata and JSON schema information without requiring a separate DBMS_JSON_SCHEMA.DESCRIBE call.

For more details and additional examples, see the Oracle documentation.

Further Readings