Method and mechanism for identifying transaction on a row of data

• US 7,937,368 B2
• Filed: 05/17/2006
• Issued: 05/03/2011
• Est. Priority Date: 09/29/2000
• Status: Expired due to Fees

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Jacobs, Kenneth R., Johnson, Archna Kalra, Ganesh, Amit, Klein, Jonathan, Raja, Vivekanandhan
• Primary Examiner(s): Lee; Wilson
• Assistant Examiner(s): PHAN, TUANKHANH D
• Application Number:
  US11/436,263
• Publication Number:
  US 20060212492A1
• Time in Patent Office: 1,812 Days
• Field of Search: 707/640, 707/646, 707/647, 707/648, 709/246, 709/248
• US Class Current: 707/648
• CPC Class Codes: G06F 16/2477 Temporal data queries
• CPC Class Codes: Y10S 707/99938 Concurrency, e.g. lock management in shared database
• CPC Class Codes: Y10S 707/99952 Coherency, e.g. same view to multiple users
• CPC Class Codes: Y10S 707/99953 Recoverability

Abstract

A method and mechanism for identifying one or more transactions that have modified or created an object in a database system. Also disclosed is a method and mechanism to compensate for the effects of a transaction in a database system is disclosed. The disclosed approach can be applied to perform auditing for changes and/or transactions in a database system and for identifying the scope of changes and data accesses made by a transaction in the system.

Claims

1. A computer implemented method for partially restoring a database block in a database system, comprising:

tracking a plurality of changes that have been made to the database block by a committed transaction by using at least one or more log records;

receiving a request for reversing some but not all of the plurality of changes made to the database block by the committed transaction;

identifying the some but not all of the plurality of changes, wherein

the act of identifying the some but not all of the plurality of changes comprises accessing the one or more log records that are associated with the committed transaction; and

reversing the some but not all of the plurality of changes made by the committed transaction to the database block by applying at least a portion of the one or more log records to the database block, wherein

the act of reversing the some but not all of the plurality of changes is performed by a processor while leaving one or more of the plurality of changes that are not the some but not all of the plurality of changes made by the committed transaction persisted.

2. The method of claim 1 in which a primary key is used to identify the committed transaction.

3. The method of claim 1 in which the log record is accessed one or more times to identify the some but not all changes made by the committed transactions.

4. The method of claim 3 in which the log record comprises a list of transactions that access a given data item.

5. The method of claim 4 in which the list of transactions is associated with multiple rows of data.

6. The method of claim 4 in which the list of transactions is associated with a single row of data.

7. The method of claim 3 in which the log record comprises an undo record.

8. The method of claim 1 in which a list of transactions is compiled, each transaction on the list corresponding to a related data item.

9. The method of claim 8 in which the last N transactions are listed.

10. The method of claim 1 in which a dependency graph is constructed.

11. The method of claim 1 in which the dependency graph identifies dependent transactions for the committed transaction.

18. The method of claim 1, further comprising:

identifying a data item to which one of the some but not all changes was made by the committed transaction.

19. The method of claim 18, further comprising:

identifying a data block comprising a modified data item by using an associative relation between the data block and the log record; and

identifying a second primary key by using the data block.

20. The method of claim 18, in which the act of identifying the data item comprises:

consulting one or more second log records to determine whether the one of the some but not all changes was made to the data item of the database table.

12. A computer program product comprising a tangible computer usable storage medium having executable code, which when executed by a processor, causes the processor to execute a process for partially restoring a database block in a database system, the process comprising the actions of:

tracking a plurality of changes that have been made to the database block by a committed transaction by using at least one or more log records;

receiving a request for reversing some but not all of the plurality of changes made to the database block by the committed transaction;

identifying the some but not all of the plurality of changes, wherein

the act of identifying the some but not all of the plurality of changes comprises accessing the one or more log records that are associated with the committed transaction; and

reversing the some but not all of the plurality of changes made by the committed transaction to the database block by applying at least a portion of the one or more log records to the database block, wherein

the act of reversing the some but not all of the plurality of the changes is performed by a processor while leaving one or more of the plurality of changes that are not the some but not all of the plurality of changes made by the committed transaction persisted.

14. The computer program product of claim 12, wherein the process further comprises compiling a list of transactions that correspond to a related data item.

15. The computer program product of claim 12, wherein the process further comprises identifying dependent transactions for the committed transaction.

13. A system for partially restoring a database block in a database system, comprising:

a computer system which comprises at least one processor and is programmed for performing at least;

tracking a plurality of changes that have been made to the database block by a committed transaction by using at least one or more log records;

receiving a request for reversing some but not all of the plurality of changes made to the database block by the committed transaction;

identifying the some but not all of the plurality of changes, wherein

the processor configured for identifying the some but not all of the plurality of changes is further configured for accessing one or more log records that are associated with the committed transaction; and

reversing the some but not all of the plurality of changes made by the committed transaction to the database block while leaving one or more of the plurality of changes that are not the some but not all of the plurality of changes made by the committed transaction persisted by applying at least a portion of the one or more log records to the database block.

16. The system of claim 13, further comprises means for compiling a list of transactions that correspond to a related data item.

17. The system of claim 13, further comprises means for identifying one or more dependent transactions for the committed transaction.