Method and apparatus for obtaining storage information from a transaction log

Abstract

Initially, a table in the database is baselined. The baselining function is usually built into the database system program which manages the database itself. This baselining procedure generates storage information like the number of rows in the table, the average length of a row, the number of chained/migrated rows, and the average free space per page in the table. This storage information is transferred to a monitoring routine via a transaction log. The monitoring routine accepts the storage information for the table. Next, the monitoring routine monitors the transaction log for entries which alter the storage information initially passed to it. At some point, an entity may wish to know the current storage information for the table. When this occurs, a message is passed to the monitoring routine, and the monitoring routine transfers the storage information to the requesting entity, without accessing the database.

1 . A method for obtaining and maintaining storage information related to storage characteristics of a table in a database, comprising: 
 baselining a table contained in the database, wherein the storage information is obtained;    making an entry into a transaction log, wherein the entry contains the storage information;    retrieving the storage information from the transaction log; and    periodically updating the storage information by monitoring subsequent entries in the transaction log.    
     
     
         2 . The method as recited in  claim 1 , further comprising: 
 locking a particular table to be baselined, thereby preventing modifications of the particular table;    making an entry into the transaction log that the particular table is to be baselined;    preparing a storage area to receive the storage information for the particular table; and    unlocking the particular table after it is baselined, wherein access to the particular table is restored.    
     
     
         3 . The method as recited in  claim 2 , further comprising: 
 sending the storage information to a requesting entity, wherein a portion of the storage information is row identifications; and    deleting the row identifications, wherein the requesting entity maintains the row identifications.    
     
     
         4 . The method as recited in  claim 3 , wherein the periodically updating step includes adding row identifications to the storage information when a first particular entry in the transaction log indicates a new chained row, and removing row identifications from the storage information when a second particular entry indicates a chained row has been removed.  
     
     
         5 . The method as recited in  claim 1 , wherein the storage information includes information reflecting a block count, number of rows, average row length, average free space, and number of chained/migrated rows in the table.  
     
     
         6 . The method as recited in  claim 1 , wherein a function native to the database performs the baselining step, an initial routine performs making an entry steps, and a monitoring routine performs the retrieving and periodically updating steps.  
     
     
         7 . The method as recited in  claim 1 , further comprising repeating the baselining and making an entry steps for additional tables as specified by a user.  
     
     
         8 . A computer system, containing a database, that executes a database system program for managing data contained in the database, and a storage information program that computes and monitors storage information for the database, comprising: 
 a processor; and    memory units, electrically connected to the processor, wherein the database system program directs the processor to retrieve portions of the database from the memory units for manipulation by the processor, and the storage information program directs the computer system to operate in a mode of operation to compute and monitor the storage information, wherein    a table contained in the database is baselined by the database system program, wherein the storage information is obtained;    an entry into a transaction log is made, wherein the entry contains the storage information; and    the monitoring routine retrieves the storage information from the transaction log, and periodically updates the storage information by monitoring subsequent entries in the transaction log.    
     
     
         9 . The computer system as recited in claims  8 , wherein the compute and monitor mode of operation further includes: 
 locking a particular table to be baselined, thereby preventing modification to the particular table;    making an entry into the transaction log that the particular table is to be baselined;    preparing a storage area to receive the storage information for the particular table; and    unlocking the particular table after it is baselined, wherein access to the particular table is restored.    
     
     
         10 . The computer system as recited in  claim 9 , wherein the compute and monitor mode of operation further includes: 
 sending the storage information to a requesting entity, wherein a portion of the storage information is row identifications; and    deleting the row identifications, wherein the requesting entity maintains the row identifications.    
     
     
         11 . The computer system as recited in  claim 10 , wherein the compute and monitor mode of operation further includes the monitoring routine adding row identifications to the storage information when a first particular entry in the transaction log indicates a new chained row, and removing row identifications from the storage information when a second particular entry indicates a chained row has been removed.  
     
     
         12 . The computer system as recited in  claim 8 , wherein the storage information includes information reflecting a block count, number of rows, average row length, average free space, and number of chained/migrated rows in the table.  
     
     
         13 . The computer system as recited in  claim 8 , wherein the database system program is a database system program produced by Oracle Corporation.  
     
     
         14 . The computer system as recited in  claim 8 , wherein the compute and monitor mode of operation further includes baselining each table in the database, and making an entry into the transaction log for each table baselined.  
     
     
         15 . A computer system, containing a database, that executes a database system program for managing data contained in the database, and a storage information program that computes and monitors storage information for the database, comprising: 
 means for processor information;    means for storing information, electrically connected to the processor;    means for baselining a table contained in the database, wherein the storage information is obtained;    means for making an entry into a transaction log, wherein the entry contains the storage information;    means for retrieving the storage information from the transaction log; and    means for periodically updating the storage information by monitoring subsequent entries in the transaction log.    
     
     
         16 . The computer system as recited in claims  15 , further comprising: 
 means for locking a particular table to be baselined, thereby preventing modification of the particular table;    means for making an entry into the transaction log that the particular table is to be baselined;    means for preparing a storage area to receive the storage information for the particular table; and    means for unlocking the particular table after it is baselined, wherein access to the particular table is restored.    
     
     
         17 . The computer system as recited in  claim 16 , further comprising: 
 means for sending the storage information to a requesting entity, wherein a portion of the storage information is row identifications; and    means for deleting the row identifications, wherein the requesting entity maintains the row identifications.    
     
     
         18 . The computer system as recited in  claim 17 , further comprising means for adding row identifications to the storage information when a first particular entry in the transaction log indicates a new chained row, and means for removing row identifications from the storage information when a second particular entry indicates a chained row has been removed.  
     
     
         19 . The computer system as recited in  claim 15 , wherein the storage information includes information reflecting a block count, number of rows, average row length, average free space, and number of chained/migrated rows in the table.  
     
     
         20 . The computer system as recited in  claim 15 , wherein the database system program is a database system program produced by Oracle Corporation.  
     
     
         21 . The computer system as recited in  claim 15 , further comprising means for baselining each table in the database, and means for making an entry into the transaction log for each table baselined.