Oracle Database 23c–some intriguing new features–dbms_pipe, Prometheus, pre-authenticated request URL, reset database session, Vector Search

This article is really just a quick draft to remind myself of some really interesting new Oracle Database features that I want to further investigate. Very succinctly:

• Pre Authenticated Request URLs – get a URL that when accessed results in the dataset that is result of a predefined SELECT statement. Anyone who has the URL can get the data – even if the database is in a private network. And because it is a URL that with an HTTP GET can be accessed and returns JSON, even simple web applications can use this. You can limit how long the URL is valid or how often it can be used. See: Documentation. Also potentially interesting: Cloud Links. I have not yet looked into it. Documentation is here.
• The DBMS_PIPE package is enhanced in several ways: a singleton pipe can be used to cache and retrieve a custom message and share the message across multiple database sessions with concurrent reads. It seems a little bit like a global application context but with a built in refresh capability – look here in the docs.
  Pipes traditionally were in-memory and therefore lost their state when a database instance died. Besides. messages were restricted to a single node in a RAC database. In 23c, persistent messaging has been introduced. The messages published to a pipe are persistently stored in Cloud Object Store– and this allows one or more database sessions to communicate in the same region or across regions. This makes it possible to send and retrieve very large messages, send a large number of pipe messages and send and receive messages within a single database, across multiple databases and across databases in different regions. See Docs.
• Oracle has a unified observability exporter – see GitHub – that implements a Prometheus exporter for Oracle Database. This exporter can be used – for example running in a standalone container – to collect metrics on a running Oracle Database and make these available to Prometheus and onwards for example to Grafana dashboards. The best instruction/configuration is found here on GitHub I believe.
• Reset Database Session state – see docs. When using a database session, state is created in the session and subsequent usages of that session can see the state. For example, when an application borrows and returns a connection to a connection pool, if the sessions state is not cleared, the next usage of that connection can see the session state of the previous usage. RESET_STATE is an attribute of the database service. When you use the RESET_STATE service attribute, the session state set by an application in a request is cleared when the database request ends.
• Vector Search in Oracle Database – available with 23.4 – a release that is not yet available at the time of writing (10 April 2024) – see Announcement , see webinar.