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.
Vahid Yousefzadeh
Oracle Database Administrator
✉ vahidusefzadeh@gmail.com
Telegram channel :https://t.me/oracledb
Top comments (0)