Finding out what a table looks like usually means joining several dictionary views: columns, constraints, indexes. DBMS_DEVELOPER.GET_METADATA, available in Oracle AI Database 26ai, returns it all in one JSON document, ready for code, tools, and AI assistants to read.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_developer.get_metadata( name => 'OBJECT_NAME', schema => null, -- default: current schema object_type => 'TABLE', -- TABLE, INDEX, VIEW level => 'TYPICAL') -- BASIC, TYPICAL, ALL return json
Describe a Table
The first query reads a few values from the document; the second turns its column list into rows with JSON_TABLE.
Example:
select json_value(m, '$.objectType') as object_type, json_value(m,
'$.objectInfo.numRows') as num_rows,
json_query(m, '$.objectInfo.indexes[*].name' with wrapper) as indexes
from (select dbms_developer.get_metadata(name => 'AIRCRAFT',
object_type => 'TABLE') as m);
select c.*
from json_table(dbms_developer.get_metadata(name => 'AIRCRAFT', object_type => 'TABLE'),
'$.objectInfo.columns[*]'
columns (name varchar2(20) path '$.name',
type varchar2(10) path '$.dataType.type',
len number path '$.dataType.length',
not_null varchar2(5) path '$.notNull',
pk varchar2(5) path '$.isPk')) c;Output:
OBJECT_TYPE NUM_ROWS INDEXES ______________ ___________ __________________ TABLE 16 ["AIRCRAFT_PK"] NAME TYPE LEN NOT_NULL PK _______________ ___________ ______ ___________ ________ TAIL_NUMBER VARCHAR2 8 true true TYPE_CODE VARCHAR2 4 true false DELIVERED_ON DATE true false STATUS VARCHAR2 12 true false
AIRCRAFT is a table of 16 rows with one index, AIRCRAFT_PK. Each column comes with its type, length, NOT NULL flag, and whether it is part of the primary key.
Describe an Index
Example:
select json_serialize(
dbms_developer.get_metadata(name => 'AIRCRAFT_PK', object_type => 'INDEX',
level => 'BASIC') pretty) as metadata;Output:
METADATA
________________________________________________
{
"objectType" : "INDEX",
"objectInfo" :
{
"name" : "AIRCRAFT_PK",
"indexType" : "NORMAL",
"owner" : "NIMBUS",
"tableName" : "AIRCRAFT",
"status" : "VALID",
"columns" :
[
{
"name" : "TAIL_NUMBER",
"notNull" : true,
"dataType" :
{
"type" : "VARCHAR2",
"length" : 8,
"sizeUnits" : "BYTE"
}
}
]
},
"etag" : "1B9138DF44C0939F2C782B03DF568E6F"
}With level BASIC, the document lists the index type, table, status, and indexed columns, plus an ETAG value that changes when the object changes.
Things to Know
- BASIC returns the essentials, TYPICAL adds details such as statistics, and ALL returns everything available.
- The name is case-sensitive: 'aircraft' in lowercase raises ORA-03408, object not found.
- Compare ETAG values to tell whether an object changed since you last read its metadata.
Related Guides
Conclusion
DBMS_DEVELOPER.GET_METADATA returns the structure of a table, index, or view as one JSON document. Read it with JSON_VALUE and JSON_TABLE instead of joining dictionary views, and choose the level of detail you need.
