DBMS_DEVELOPER.GET_METADATA in Oracle 23ai(23.7)

By -

 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

Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Oracle 23ai — Track Table and Partition Scan Access

By -
Image
  In Oracle 23ai, a new view named   [dba|all|user]_table_access_stats   has been introduced, enabling users to view the access history of tables and partitions. This view shows how many times each table or partition has been scanned in each instance and the last time a table was accessed. SQL > create table jadval(id number, name varchar2( 100 ), last_name varchar2( 100 )); Table created. SQL > insert into jadval values ( 1 , 'payan' , 'rafat' ); 1 row created. SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; no rows selected SQL > select * from jadval; ID NAME LAST_NA ---------- ------- ------- 1 payan rafat SQL > select TABLE_NAME, PARTITION_NAME, INSTANCE_ID, READ_COUNT, LAST_ACCESSED_TIME from user_table_access_stats; TABLE_NAME PARTITION_NAME INSTANCE_ID READ_COUNT LAST_ACCESSED_TIME ---------- --------------- ----------- --------...
Read more