Oracle | DBMS_DEVELOPER

Oracle DBMS_DEVELOPER Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose Similar functionality to DBMS_METADATA.GET_DDL with a JSON API

AUTHID CURRENT_USER

Data Types -

Dependencies

DBMS_DEVELOPER_LIB

Documented Yes

First Available 23ai

Pragma PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

Security Model Owned by SYS with EXECUTE granted to PUBLIC

Source {ORACLE_HOME}/rdbms/admin/dbmsinfo.sql
{ORACLE_HOME}/rdbms/admin/prvtinfo.sql

GET_METADATA (new 23ai)

Undocumented get_metadata( name IN VARCHAR2, schema IN VARCHAR2 := NULL, object_type IN VARCHAR2 := NULL, level IN VARCHAR2 := 'TYPICAL', -- values BASIC, TYPICAL, ALL etag IN RAW := NULL) RETURN JSON; PRAGMA SUPPLEMENTAL_LOG_DATA(get_metadata, READ_ONLY);

CREATE TABLE c##scott.test AS SELECT * FROM dba_tables WHERE 0-1;

      

      SELECT dbms_developer.get_metadata('TEST', 'C##SCOTT', 'TABLE', 'ALL');

      

      BMS_DEVELOPER.GET_METADATA('TEST','C##SCOTT','TABLE','TYPICAL')

      --------------------------------------------------------------------------------

      {"objectType":"TABLE","objectInfo":{"name":"TEST","schema":"C##SCOTT","columns":

      [{"name":"USERNAME","notNull":true,"dataType":{"type":"VARCHAR2","length":128,"s

      izeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"USER_ID","not

      Null":true,"dataType":{"type":"NUMBER"},"isPk":false,"isUk":false,"isFk":false},

      {"name":"PASSWORD","notNull":false,"dataType":{"type":"VARCHAR2","length":4000,"

      sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"ACCOUNT_STAT

      US","notNull":true,"dataType":{"type":"VARCHAR2","length":32,"sizeUnits":"BYTE"}

      ,"isPk":false,"isUk":false,"isFk":false},{"name":"LOCK_DATE","notNull":false,"da

      taType":{"type":"DATE"},"isPk":false,"isUk":false,"isFk":false},{"name":"EXPIRY_

      DATE","notNull":false,"dataType":{"type":"DATE"},"isPk":false,"isUk":false,"isFk

      ":false},{"name":"DEFAULT_TABLESPACE","notNull":true,"dataType":{"type":"VARCHAR

      2","length":30,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"nam

      e":"TEMPORARY_TABLESPACE","notNull":true,"dataType":{"type":"VARCHAR2","length":

      30,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"LOCAL_TE

      MP_TABLESPACE","notNull":false,"dataType":{"type":"VARCHAR2","length":30,"sizeUn

      its":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"CREATED","notNull"

      :true,"dataType":{"type":"DATE"},"isPk":false,"isUk":false,"isFk":false},{"name"

      :"PROFILE","notNull":true,"dataType":{"type":"VARCHAR2","length":128,"sizeUnits"

      :"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"INITIAL_RSRC_CONSUMER_

      GROUP","notNull":false,"dataType":{"type":"VARCHAR2","length":128,"sizeUnits":"B

      YTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"EXTERNAL_NAME","notNull":

      false,"dataType":{"type":"VARCHAR2","length":4000,"sizeUnits":"BYTE"},"isPk":fal

      se,"isUk":false,"isFk":false},{"name":"PASSWORD_VERSIONS","notNull":false,"dataT

      ype":{"type":"VARCHAR2","length":17,"sizeUnits":"BYTE"},"isPk":false,"isUk":fals

      e,"isFk":false},{"name":"EDITIONS_ENABLED","notNull":false,"dataType":{"type":"V

      ARCHAR2","length":1,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},

      {"name":"AUTHENTICATION_TYPE","notNull":false,"dataType":{"type":"VARCHAR2","len

      gth":8,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"PROX

      Y_ONLY_CONNECT","notNull":false,"dataType":{"type":"VARCHAR2","length":1,"sizeUn

      its":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"COMMON","notNull":

      false,"dataType":{"type":"VARCHAR2","length":3,"sizeUnits":"BYTE"},"isPk":false,

      "isUk":false,"isFk":false},{"name":"LAST_LOGIN","notNull":false,"dataType":{"typ

      e":"TIMESTAMP WITH TIMEZONE","fractionalSecondsPrecision":9},"isPk":false,"isUk"

      :false,"isFk":false},{"name":"ORACLE_MAINTAINED","notNull":false,"dataType":{"ty

      pe":"VARCHAR2","length":1,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":f

      alse},{"name":"INHERITED","notNull":false,"dataType":{"type":"VARCHAR2","length"

      :3,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"DEFAULT_

      COLLATION","notNull":false,"dataType":{"type":"VARCHAR2","length":100,"sizeUnits

      ":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"IMPLICIT","notNull":f

      alse,"dataType":{"type":"VARCHAR2","length":3,"sizeUnits":"BYTE"},"isPk":false,"

      isUk":false,"isFk":false},{"name":"ALL_SHARD","notNull":false,"dataType":{"type"

      :"VARCHAR2","length":3,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":fals

      e},{"name":"EXTERNAL_SHARD","notNull":false,"dataType":{"type":"VARCHAR2","lengt

      h":3,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"PASSWO

      RD_CHANGE_DATE","notNull":false,"dataType":{"type":"DATE"},"isPk":false,"isUk":f

      alse,"isFk":false},{"name":"MANDATORY_PROFILE_VIOLATION","notNull":false,"dataTy

      pe":{"type":"VARCHAR2","length":3,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,

      "isFk":false},{"name":"PROTECTED","notNull":false,"dataType":{"type":"VARCHAR2",

      "length":3,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"

      READ_ONLY","notNull":false,"dataType":{"type":"VARCHAR2","length":3,"sizeUnits":

      "BYTE"},"isPk":false,"isUk":false,"isFk":false},{"name":"DICTIONARY_PROTECTED","

      notNull":false,"dataType":{"type":"VARCHAR2","length":3,"sizeUnits":"BYTE"},"isP

      k":false,"isUk":false,"isFk":false},{"name":"CLOUD_MAINTAINED","notNull":false,"

      dataType":{"type":"VARCHAR2","length":3,"sizeUnits":"BYTE"},"isPk":false,"isUk":

      false,"isFk":false},{"name":"APPLICATION","notNull":false,"dataType":{"type":"VA

      RCHAR2","length":3,"sizeUnits":"BYTE"},"isPk":false,"isUk":false,"isFk":false}],

      "hasBeenAnalyzed":false,"constraints":[{"name":"SYS_C008538","constraintType":"C

      HECK - NOT NULL","searchCondition":"\"USERNAME\" IS NOT NULL","columns":[{"name"

      :"USERNAME"}],"status":"ENABLE","deferrable":false,"validated":"VALIDATED","sysG

      eneratedName":true},{"name":"SYS_C008539","constraintType":"CHECK - NOT NULL","s

      earchCondition":"\"USER_ID\" IS NOT NULL","columns":[{"name":"USER_ID"}],"status

      ":"ENABLE","deferrable":false,"validated":"VALIDATED","sysGeneratedName":true},{

      "name":"SYS_C008540","constraintType":"CHECK - NOT NULL","searchCondition":"\"AC

      COUNT_STATUS\" IS NOT NULL","columns":[{"name":"ACCOUNT_STATUS"}],"status":"ENAB

      LE","deferrable":false,"validated":"VALIDATED","sysGeneratedName":true},{"name":

      "SYS_C008541","constraintType":"CHECK - NOT NULL","searchCondition":"\"DEFAULT_T

      ABLESPACE\" IS NOT NULL","columns":[{"name":"DEFAULT_TABLESPACE"}],"status":"ENA

      BLE","deferrable":false,"validated":"VALIDATED","sysGeneratedName":true},{"name"

      :"SYS_C008542","constraintType":"CHECK - NOT NULL","searchCondition":"\"TEMPORAR

      Y_TABLESPACE\" IS NOT NULL","columns":[{"name":"TEMPORARY_TABLESPACE"}],"status"

      :"ENABLE","deferrable":false,"validated":"VALIDATED","sysGeneratedName":true},{"

      name":"SYS_C008543","constraintType":"CHECK - NOT NULL","searchCondition":"\"CRE

      ATED\" IS NOT NULL","columns":[{"name":"CREATED"}],"status":"ENABLE","deferrable

      ":false,"validated":"VALIDATED","sysGeneratedName":true},{"name":"SYS_C008544","

      constraintType":"CHECK - NOT NULL","searchCondition":"\"PROFILE\" IS NOT NULL","

      columns":[{"name":"PROFILE"}],"status":"ENABLE","deferrable":false,"validated":"

      VALIDATED","sysGeneratedName":true}]},"etag":"840CEDAA935D07D1D942FB53EA496A1B"}