This is a new API available in Oracle Database 23ai, specifically version 23.7. We make this available in a few formats, including our FREE Edition which we also distribute as a VirtualBox appliance.
Using DBMS_DEVELOPER to find out more about your tables, views, and other database objects
DBMS_DEVELOPER.GET_METADATA (23.7 New Features Docs)
You are probably familiary with the DBMS_METADATA package as it’s quite handy. We use it internally here to do things like make data pump work and generating DDL for objects, so you can see the SQL behind your TABLE in tools like SQL Developer and SQLcl.
It has a couple of limitations, from the developer perspective.
- it’s a bit slow, esp on the first request/execution
- the privilege model requires DBA’ish level access
- it’s about is generally SQL or SXML vs JSON
The DBMS_DEVELOPER addresses all of these points!
For the initial release, it has as single FUNCTION, GET_METADATA. However, I expect this package to have lots and lots of goodies added over time, so keep an eye out! If you have ideas for features to add to this package, feel free to leave a comment here.
Let’s try it out!
What’s it do? Well, it will tell you a LOT of information about your database object. You’re used to seeing this information in the form of reports or screens in tools like SQL Developer or APEX.
Simpler Security Model
I’m logged in as HR, which has very low or minimal level privs.
Using a DBA account, I’m going to grant READ & SELECT on SH.SALES to HR. Quick sidebar: we introduced the READ privilege in 12.1. Have you started using it? It allows a database user to query a table WITHOUT having the ability to also LOCK the object. Tim shows you how this works on his Oracle-Base site.
Let’s make sure the GRANT has ‘worked.’ by querying SH.SALES as HR.
Great, now let’s use DBMS_METADATA to get the DDL for the SALES table.
It’s not working, we get a ORA-31603, as the package requires higher level access than the READ or SELECT on the object permits.
Alright, let’s try our new DBMS_DEVELOPER package.
It works, we get a JSON object, with lots of data, but did you see how fast it is?
Compare this to DBMS_METADATA and asking for the DDL on a local table, HR.EMPLOYEES, where the security model problem becomes moot –
That’s about…20,000% slower. Our tools (SQL Developer, SQLcl, …) can now leverage this new interface to get interesting information, including the create table ddl, on objects to developers faster, and without DBA level privs (or SELECT CATALOG).
Rich, Useful Metadata
Let’s look at the output. Well, that’s going to be challenge, we have 300+ lines of pretty-print formatted JSON information about our TABLE! But I’ll try anyway 🙂
It’s broken down into several categories:
- objectInfo (columns, object level stats)
- indexes
- constraints
And this this is JUST the ‘typical’ level of metadata for our object!
If I run the function again, and this time as for the ‘ALL’ level of detail, you can see for the columns there are an additional 6 or so attributes, basically the column level stats.
Summary
- the package is FAST
- the information is readily accessible for developers
- the amount of information is incredible
If you’re building utilities or services that require information for database objects in your Oracle Database 23ai (and higher) system, the DBMS_DEVELOPER package will quickly become your best friend.
7 Comments
Suggestions for the DBMS_DEVELOPER package:
GET_DEPENDENCIES(,,, GENERATE_DDL[NO{default}/YES] )
Generate a list of all objects that OBJECT_NAME is dependent. The FULL_TREE parameter indicates if we want just the objects which the OBJECT_NAME depends directly (DEFAULT), or if we also want the dependencies of the dependences (in an ascending tree fashion). The GENERATE_DDL parameter allows and facilitate to build scripts by already preparing the necessary CREATE OBJECT statements.
GET_REFERENCES(,,,GENERATE_DDL[NO{default}/YES])
Analog as the previous one, but descending: generate a list with all objects that OBJECT_NAME references. Exactly the same idea, the FULL_TREE parameter is used to return either the objects which the OBJECT_NAME references directly (DEFAULT), or to also get the references of the references (descending tree). The GENERATE_DDL parameter works like the previous one.
Intuitively, these functions would help finding dependencies and references of any given object. The FULL_TREE parameter would help identifying possible broken/missing further references or dependencies in the situations where we have to recreate/rebuild/recompile specific objects. The output could even be neatly formatted with a LEVEL column, facilitating analyses.
A problem with such functions would be defining the scope of the dependencies/references: foreign keys are not “real database objects”, but they kind of depend on two “objects”: their own table and the referenced constraint. Should they be returned by the function, or just returning its table is enough? Also the object grants (again, not real database objects), upon recreating an object it’s necessary to regrant the permissions. If the function will return the privileges, should it dig deeper (or upper) into the possible roles as well?
Weird, the site changed my notation, it took away the parameters because I used the greater/less signs. Instead of empty commas, it should be:
GET_DEPENDENCIES(SCHEMA, OBJECT_NAME, FULL_TREE[NO{default}/YES], GENERATE_DDL[NO{default}/YES] )
GET_REFERENCES(SCHEMA, OBJECT_NAME, FULL_TREE[NO{default}/YES], GENERATE_DDL[NO{default}/YES] )
Will Oracle 23ai Developer Free Edition be updated to 23.7?
That’s literally what this is.
It would be helpful if the website provided information on which version corresponds to which platform. For example, is 23.6 the version for ARM (aarch64).
There is no clear indication that it has changed.
Very nice!
Wen v23 on-premises?
That’s a great question.
But the dbms_developer pkg may be coming to 19c, stay tuned.