TRANSFERRING RECORDS BETWEEN TABLES USING A CHANGE TRANSACTION LOG

• US 20090106196A1
• Filed: 10/19/2007
• Published: 04/23/2009
• Est. Priority Date: 10/19/2007
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Mehta, Suhas R., Gutlapalli, Hari K., Kothari, Shirish K., Pak, Wai
• Granted Patent: US 8,682,859 B2
• Time in Patent Office: Days
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27

Abstract

A method and apparatus for transferring records between database tables using a transaction log. In one embodiment of the method, a first application generates a transaction for deleting a record from a first table in a database. In response to the generation of the first transaction, a copy of the record is stored in an entry of a transaction log before the record is deleted from the first table. A second application generates an instruction for copying the record from the transaction log to a second table, and the record is copied from the entry to the second table.

Claims

1. A method comprising:

a first application generating a transaction for deleting a record from a first table in a database;

deleting the record from the first table;

in response to the generation of the first transaction, storing a copy of the record in an entry of a transaction log before the record is deleted from the first table;

a second application generating an instruction for copying the record from the transaction log to a second table;

copying the record from the entry to the second table.

2. The method of claim 1 wherein the first and second applications are distinct.

3. The method of claim 1 further comprising:

generating a first message in response to generating the transaction, wherein the first message comprises a table name of the first table and an identifier of the record in the first table, and wherein the first message comprises information that indicates that the record was or will be deleted;

transmitting the first message to a computer system that implements a search engine.

4. The method of claim 3 wherein the search engine generates a first index identifier of the record using the first table name and the identifier of the record in the first table.

5. The method of claim 4 further comprising removing all instances of the first index identifier from a search index.

6. The method of claim 1 further comprising:

generating a second message in response to generating the instruction, wherein the second message comprises the table name of the second table and the identifier of the record in the second table, and wherein the second message comprises information that indicates that the record has been inserted into the second table;

transmitting the second message to the computer system that implements the search engine.

7. The method of claim 6 wherein the search engine generates a second index identifier of the record using the second table name and the identifier of the record in the second table.

8. The method of claim 4 further comprising adding at least one instance of the second index identifier to the search index.

9. A method comprising:

a first application generating a first instruction for copying a record from a first table in a database to an entry of a transaction log;

in response to the generation of the first instruction, copying the record from the first table to the entry;

a second application generating a second instruction for copying the record from the transaction log to a second table;

copying the record from the entry to the second table.

10. The method of claim 9 wherein the first and second applications are distinct.

11. The method of claim 9 further comprising:

generating a message in response to generating the second instruction, wherein the message comprises a table name of the second table and an identifier of the record in the second table, and wherein the message comprises information that indicates that the record was inserted into the second table;

transmitting the message to a computer system that implements a search engine.

12. The method of claim 11 wherein the search engine generates an index identifier of the record using the second table name and the identifier of the record in the second table.

13. The method of claim 12 further comprising adding at least one instance of the index identifier to a search index.

14. A computer readable medium (CRM) storing instructions, wherein a method is implemented in response to executing the instructions, the method comprising:

receiving a first instruction for deleting a record from a first table in a database, wherein the first instruction is received from a first application;

deleting the record from the first table;

storing a copy of the record in an entry of a transaction log before the record is deleted from the first table;

receiving a second instruction for copying the record from the transaction log to a second table, wherein the second instruction is received from a second application;

copying the record from the entry to the second table.

15. The CRM of claim 14 wherein the first and second applications are distinct.

16. The CRM of claim 14 wherein the method further comprises:

generating a first message in response to receiving the first instruction, wherein the first message comprises a table name of the first table and an identifier of the record in the first table, and wherein the first message comprises information that indicates that the record was or will be deleted;

transmitting the first message to a computer system that implements a search engine.

17. The CRM of claim 16 wherein the method further comprises

generating a second message in response to receiving the second instruction, wherein the second message comprises the table name of the second table and the identifier of the record in the second table, and wherein the second message comprises information that indicates that the record has been inserted into the second table;

transmitting the second message to the computer system that implements the search engine.