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 will be available on generic Linux platforms in January and soon on AIX and Windows

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"}