Streaming change logs from streaming database tables

Patent metadata

Field	Value
Publication number	US-12423293-B2
Application number	US-202318383948-A
Country	US
Kind code	B2
Filing date	Oct 26, 2023
Priority date	Oct 26, 2023
Publication date	Sep 23, 2025
Grant date	Sep 23, 2025

Abstract

As with database tables in general, a streaming table and its columns are defined within by a database. DML commands are issued against a streaming table that specify to insert rows and values into their defined columns. Change records in a change log (e.g., redo log) are generated to record the changes specified by DML commands. The change log may be used to stream data captured by the change log. However, unlike with database tables in general, the rows of a streaming table are not persistently stored in the database at commit time. Alternatively, rows are stored persistently but without values for columns defined as streaming columns. Columns defined as streaming columns may be set to values specified by DML commands but are not stored in the database; streaming column values are captured in change logs, however.

First claim

What is claimed is:

1. A method comprising:
within a database hosted by a database management system (DBMS), defining a database table as a streaming table;
within a database transaction, receiving one or more database commands to insert first rows into the streaming table;
generating change log records in a change log recording insertion of said first rows;
committing said database transaction, wherein committing said database transaction includes:
determining that said change log records are stored persistently in a change log; and
purging said first rows from memory;

wherein said DBMS prevents performing database writes that persistently store said first rows in a data block of the database,
wherein the method is performed by one or more computing devices.

2. The method of claim 1 , further comprising causing the change log records to be streamed to an external system over a network.

3. The method of claim 1 , wherein defining the database table as a streaming table further comprises:
generating metadata in a data dictionary defining the database table, wherein the metadata indicates that the database table is a streaming table.

4. The method of claim 1 , further comprising:
receiving a query within the database transaction specifying the first rows from the database table in an instance in which the database transaction has not yet been committed; and
providing data from the first rows in response to the query.

5. The method of claim 1 , further comprising:
receiving a query specifying the first rows from the database table in an instance in which the database transaction has been committed; and
providing an error message in response to the query.

6. A method comprising:
within a database hosted by a database management system (DBMS), defining a database table comprising a plurality of columns, and the plurality of columns comprising one or more streaming columns and one or more other columns;
within a database transaction, receiving one or more database commands to insert first rows into the database table;
generating change log records in a change log recording insertion of the first rows;
committing the database transaction, wherein committing the database transaction includes,
determining that said change log records are stored persistently in a change log;
purging said first rows from memory;
persistently storing, in the database, one or more values of the one or more other columns in the first rows; and
the DBMS preventing persistently storing one or more values of the one or more streaming columns in the first rows.

7. The method of claim 6 , further comprising:
providing one or more default values for the one or more streaming columns; and
in response to a query specifying the one or more streaming columns, providing the one or more default values.

8. The method of claim 6 , further comprising:
causing the change log records to be streamed to an external system over a network.

9. The method of claim 6 , further comprising:
generating metadata in a data dictionary defining the database table, wherein the metadata indicates the one or more streaming columns as streaming columns.

10. One or more computer-readable non-transitory media storing one or more sequences of instructions that, when executed by computing devices, cause:
within a database hosted by a database management system (DBMS), defining a database table as a streaming table;
within a database transaction, receiving one or more database commands to insert first rows into the streaming table;
generating change log records in a change log recording insertion of said first rows;
committing said database transaction, wherein committing said database transaction includes:
determining that said change log records are stored persistently in a change log; and
purging said first rows from memory;

wherein said DBMS prevents performing database writes that persistently store said first rows in a data block of the database,
wherein the one or more computer-readable non-transitory media is performed by one or more computing devices.

11. The one or more computer-readable non-transitory media of claim 10 , wherein the one or more sequences of instructions include instructions, that when executed by one or more processors cause the change log records to be streamed to an external system over a network.

12. The one or more computer-readable non-transitory media of claim 10 , wherein defining the database table as a streaming table further comprises:
generating metadata in a data dictionary defining the database table, wherein the metadata indicates that the database table is a streaming table.

13. The one or more computer-readable non-transitory media of claim 10 , wherein the one or more sequences of instructions include instructions, that when executed by one or more processors cause:
receiving a query within the database transaction specifying the first rows from the database table in an instance in which the database transaction has not yet been committed; and
providing data from the first rows in response to the query.

14. The one or more computer-readable non-transitory media of claim 10 , further comprising:
receiving a query specifying the first rows from the database table in an instance in which the database transaction has been committed; and
providing an error message in response to the query.

15. One or more computer-readable non-transitory media storing one or more sequences of instructions that, when executed by computing devices, cause:
within a database hosted by a database management system (DBMS), defining a database table comprising a plurality of columns, and the plurality of columns comprising one or more streaming columns and one or more other columns;
within a database transaction, receiving one or more database commands to insert first rows into the database table;
generating change log records in a change log recording insertion of the first rows;
committing the database transaction, wherein committing the database transaction includes,
determining that said change log records are stored persistently in a change log;
purging said first rows from memory;
persistently storing, in the database, one or more values of the one or more other columns in the first rows; and
the DBMS preventing persistently storing one or more values of the one or more streaming columns in the first rows.

16. The one or more computer-readable non-transitory media of claim 15 , wherein the one or more sequences of instructions include instructions, that when executed by one or more processors cause:
providing one or more default values for the one or more streaming columns; and
in response to a query specifying the one or more streaming columns, providing the one or more default values.

17. The one or more computer-readable non-transitory media of claim 15 , wherein the one or more sequences of instructions include instructions, that when executed by one or more processors cause:
causing the change log records to be streamed to an external system over a network.

18. The one or more computer-readable non-transitory media of claim 15 , wherein the one or more sequences of instructions include instructions, that when executed by one or more processors cause:
generating metadata in a data dictionary defining the database table, wherein the metadata indicates the one or more streaming columns as streaming columns.

Assignees

• Oracle Int Corp

Inventors

• Schrum Allan George
• Johnson Brandon

Classifications

• G06F16/2282

  Tablespace storage structures; Management thereof · CPC title

• G06F16/24573

  using data annotations, e.g. user-defined metadata · CPC title

• G06F16/2379

  Updates performed during online database operations; commit processing · CPC title

• G06F16/2358 Primary

  Change logging, detection, and notification (replication G06F16/27) · CPC title