DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on

DBMS_DEVELOPER.GET_METADATA in Oracle 23ai(23.7)

#oracle #database #linux #sql

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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"}
Enter fullscreen mode Exit fullscreen mode

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" }
Enter fullscreen mode Exit fullscreen mode

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" }
Enter fullscreen mode Exit fullscreen mode

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"
Enter fullscreen mode Exit fullscreen mode

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 - - - - - - - - - - - - { }
Enter fullscreen mode Exit fullscreen mode

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

SQL> alter table vahid.TB1 drop column last_name; Table altered.
Enter fullscreen mode Exit fullscreen mode

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" }
Enter fullscreen mode Exit fullscreen mode

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.

Vahid Yousefzadeh
Oracle Database Administrator
✉ vahidusefzadeh@gmail.com
Telegram channel :https://t.me/oracledb

Top comments (0)