Patent Application
20080098045
Reference numbers in the drawing have three or more digits: the two right-hand digits are reference numbers in the drawing indicated by the remaining digits. Thus, an item with the reference number 203 first appears as item 203 in
Overview of the Invention
Overview of a Relational Database System in which the Invention is Implemented:
In the following, a preferred embodiment of an archiving system which solves the foregoing problems will be termed unlimited Flashback. The preferred embodiment described in the following has been implemented by modifying the Oracle 11gR1 relational database system manufactured by Oracle Corporation, Redwood City, Calif. The invention may of course be embodied in other relational database systems as well.
When system 101 is operating, programs 105 in memory 103 include an operating system 107, a relational database system program 109, and application programs 111 that employ the services provided both by operating system 107 and database program 109. Correspondingly, data 113 in memory 103 includes data for the application programs, data for the operating system, and data for the database system. Operation typically involves an application program 111, which provides a query to relational database program 109. Database program 109 executes the query on the tables in persistent storage 123 and provides the result to application program 111. Database program 109 includes Flashback archive program 108. Both RDB program 109 and application program 111 use the services provided by operating system 107, and execution of the application program or the database RDB program may involve inputs from and outputs to I/O devices and the network.
Continuing in more detail, persistent storage 123 contains two classes of objects: DB system objects, which are objects, including tables, that are defined by database system 101 and are used by database system 101 to manage and operate the database system, and user objects 129, which contain tables and other objects defined by users of the database system. Data dictionary 127 contains definitions of all of the objects in the database system.
The most important objects in database system 101 are tables. A table is a queryable set of rows. All of the rows in the set have the same columns. The columns in a table define the objects that the rows in the table may contain. There are three kinds of tables in most database systems: base tables, views, and materialized views. Base tab/es are the tables that are the actual sources of the data in the table rows returned by a query. Views are tables which do not exist in their own rights in persistent storage 123, but are instead created using data from other tables. A view is defined in the data dictionary by a query on other tables. The other tables may also be views, but the data must ultimately come from base tables. A materialized view is a view of which, for reasons of efficiency, a copy has been made in persistent memory. Data dictionary 127 contains definitions of base tables, of tables defined in terms of the base tables, and definitions of other objects that are defined for the tables. These other objects include indexes, which speed up access to the data contained in a column of a table, triggers, which define actions to be taken upon occurrence of events concerning the table, and functions, which are programs that operate on database system objects. In a preferred embodiment of database system 101, the functions are written in the well-known PL/SQL language.
Two entities in Oracle relational database systems which are particularly important to the following discussion are row identifiers and system change numbers.
Overview of Unlimited Flashback as Implemented in System 101
With unlimited Flashback, a user table in system 101 may be designated as a tracked table, i.e., a table for which changes in the values in the table's records and changes in the form of the table are tracked by the database system. One such tracked table, tracked table 131, is shown in
When a table is designated a tracked table, a Tracked Table (TT) flag 129 is set in the table's data dictionary entry 128 in data dictionary 127.
A tracked table 131 has a Flashback archive 171 associated with it in the persistent storage 123 of the relational database system. Flashback archive 171 contains three tables: history table 135 for the tracked table, DDL history table 153 for the tracked table, and TCRV table 118 for the tracked table. History table 135 tracks the contents of rows; it contains versions of all rows that have been in the tracked table while it was being tracked other than the current versions of the rows; DDL history table 163 tracks the forms of the table; it contains versions of all of the forms the table has had while being tracked. TCRV table 118 contains metadata needed to include the current rows of the tracked table in temporal queries and to make a history table row from a current row of the tracked table.
History Table 135
The rows of history table 135 contain past versions of the rows in tracked table 131. From the time at which a tracked table is designated as such, each modification or deletion of a row in the tracked table results in the creation of an entry (HTE 137) in the tracked table's history table 135 which archives the user data from the row as it existed prior to the modification. HTE 137 includes a archived user data 155 of the user data from the version of the row as it existed prior to the modification.
In addition, HTE 137 includes temporal metadata consisting of a start SCN and an end SCN: the start SCN (SSCN) 138 is the SCN of the transaction that produced the archived user data 155. The end SCN (ESCN 139) is the SCN belonging to the transaction on TT 131 that resulted in the creation of the HTE 137. The SSCN and the ESCN together thus define a period of time during which the version of the tracked table's row recorded in HTE 137 existed in the tracked table. Further fields of interest in HTE 137 are TID 140, which is the transaction identifier for the transaction on TT 131 which resulted in the tracked table's row being recorded in HTE 137, OP 141, which is the operation which the transaction performed in making the modification in TT 131 which resulted in the creation of HTE 137, and RID 142, which is the row ID of the archived row when the row was in TT 131. As will be immediately apparent, the inclusion of the SSCN and the ESCN make temporal queries of history table 135 possible, while the inclusion of the TID, OP, and RID fields in the temporal metadata make it possible to retrieve rows by RID or as they existed prior to the performance of a particular transaction or as they existed prior to a particular operation. It should further be pointed out that because history table 135 is not part of the undo log, it is not subject to being overwritten and can record versions of rows of the tracked table as long as is desired. In a preferred embodiment, HT 135 is visible to users of system 101 which have access to tracked table 131 and can be queried separately from tracked table 131.
TCRV Table 118
TCRV Table 118 provides solutions to two problems:
TCRV table 118 contains the RID, SSCN, ESCN, TID, and OP temporal metadata for every current row of tracked table 131 which has been added or modified since tracked table 131 began to be tracked. TCRVT entry (TRVTE) 161 sets RID from the added or modified rows RID, SSCN from the SCN for the committed transaction that modified the row, TID from the transaction's transaction ID, and OP from the operation the transaction performed on the row. When a temporal query is performed on the tracked table, the temporal metadata that has been saved in TCRVT 161 ensures that all of the temporal metadata is available which the foreground process requires to perform the temporal query on the versions in the tracked table as well as to create the versions in history table 135. For example, if the temporal query is an AS OF query looking for rows which were in the tracked table as of a particular SCN, the metadata in TCRV table 118 will include the metadata for current rows in the tracked table. When the row in TTE 133 is deleted or modified, the temporal metadata for the deleted or modified row in TCRV table 118 is used to make the temporal metadata for the entry for the deleted or modified row in history table 135. An important advantage of TCRV table 118 is that no temporal metadata need be added to tracked table 131 in order to include both tracked table 131 and tracked table 131's history table 135 in a temporal query on tracked table 131. It should be pointed out here that tables like TCRV table 118 could be employed to maintain all of the temporal metadata for the history enabled table in implementations in which temporal queries of the history enabled table that obtained row versions from the undo log were not available.
DDL History Table 153
DDL is the portion of the SQL query language for relational database systems which is used to define new tables and modify the definitions of existing tables. In the following, changes to tables resulting from the execution of DDL statements will be termed changes to the form of the table. An example is using DDL statements to add a column to a table or drop an existing column. As would be expected from the foregoing, DDL history table 153 has an entry, DDLHTE 154 corresponding to every form which tracked table 131 has had since the table was designated a tracked table. Each DDLHTE 154 includes temporal metadata consisting of an SSCN field which indicates when the change in form represented by the entry took place and an ESCN field which indicates when the next change in the form of tracked table 131 took place. The other fields of the entry are the name of a column which was added by the change in CN 155, the type of data in the column in TY 157, and the name of a column which was deleted by the change in HCN 159. Each addition or deletion of a column in tracked table 131 has its own entry in DDLHT 153. With the information contained in DDL history table 153 it is possible to make temporal queries of tracked table 131 across executions of DDL statements, i.e., across changes in the tracked table's form. It should be noted here that a DDL history table could be used in the manner just described with any history tables, including ones made using triggers, as described above with reference to the Kelley paper. Indeed, triggers could be used to make the entries in the DDL history table.
Conceptual Overview of Operation of Unlimited Flashback
At the highest conceptual level, the operation of unlimited Flashback is simple: A table's tracked table flag 129 in the table's DDE 128 may be set when the table is created or at any time thereafter, and once set, may be reset at any time thereafter. As long as flag 129 is set, the table is a tracked table 131 and each execution of a DDL statement which results in a modification of the tracked table's form causes a record of the modification to be written to DDLHTE 164 in the tracked table's DDL history table 153. Further, each operation performed by a transaction which modifies the data contained in a row of tracked table 131 or which causes the row to be deleted results in the creation of a HTE 137 in history table 135 corresponding to tracked table 131 which contains the row as it existed prior to the modification or deletion. The temporal metadata in the entries in history table 135, DDL history table 153, and TCRV table 118 and the temporal queries on row versions of tracked table 131 in the undo log that are permitted by limited flashback together make it possible to perform temporal queries across the current period during which the tracked table is tracked and across all of the modifications of the tracked table which occur in the current period during which the table is tracked. An important advantage of unlimited Flashback is that the version query of the undo data need only concern itself with those versions of the tracked table rows which are not already in the history table. That will generally be a much smaller set of versions than the complete set of versions in the undo data, with consequent savings in the time required for the version query of the undo data.
While a currently preferred embodiment of relational database management system 101 only permits tracking of versions of rows of the tracked table, the same techniques can be used to track versions of objects contained in the rows. In particular, partial history tracking can provide for the following:
Details of the Implementation of Unlimited Flashback
Unlimited Flashback need not use triggers to write the tables in Flashback archive 171 because the source of the information written to these tables is DBMS 101's undo log In DBMS 101, the undo log is written by a foreground process. In a DBMS in which unlimited Flashback has been enabled, when the foreground process writes the prior state of a row of a tracked table to the undo log, the foreground process marks the prior state as belonging to a tracked table. A Flashback archiver process which is separate from the foreground process and runs asynchronously to the foreground process then reads the marked portions of the undo log and writes rows in history table 135, in TCRV table 118, and in DDL history table 153 from the undo data as required by the modifications to the tracked table.
Returning to
The two SCNs are of course the temporal metadata for the row in the undo block. If any of the affected row(s) belong to a tracked table, the undo block's tracked (T) flag 148 is set. In a preferred embodiment, undo log 146 is maintained in DBS persistent storage 123; however, memory 103 includes undo log buffer 143 which contains the most recently written undo blocks 151. Because reading from memory 103 is faster than reading from DBS persistent storage 123, the frequency with which the Flashback archiver runs is selected such that the Flashback archiver is able to read the undo blocks 151 from undo log buffer 143 before they are written to undo log 146.
As part of its undo logging mechanism, the foreground process in limited Flashback maintains an undo segment transaction table which contains an entry for each transaction that has a block in undo log 146. This table is shown at 165. In unlimited Flashback, the table's entries 167 include a T flag 167 which the foreground process sets when the entry's transaction has modified a tracked table. The entry for the transaction permits location of the data for the transaction in the undo log. Other components of system 101 which are of interest in the present context are SCN to time table 126, BSCN table 130, and maximum SCN 163. SCN to time table 126 is a system table that relates SCNs to time values. The table makes it possible to express a temporal query in terms either of SCNs or time values.
A barrier SCN (BSCN) is the minimum SCN such that every ESCN in the HTEs written by a given instance of the Flashback archiver is less than or equal to the barrier SCN. In a clustered version of the Oracle 11gR1 database system, there may be several instances of the Flashback archiver. Barrier SCN table 130 contains the current BSCN for each of the instances of the Flashback archiver. When the foreground process performs a temporal query, it uses the Global barrier SCN (GBSCN), which is the current smallest BSCN in table 130. The GBSCN is the largest SCN such that all HTEs resulting from transactions with commit SCNs less than GBSCN have already been written to an HT 135. The GBSCN thus marks the point in time which separates ESCNs for tracked table rows archived in the history table from possible ESCNs for the rows currently contained in the tracked table. The foreground process uses the GBSCN in temporal queries on the tracked table to determine which part, if any, of the query is to be performed on history table 135 and which on tracked table 131. Maximum SCN 163 is finally, is an SCN maintained by DBMS 101; it is the highest SCN assigned to any committed transaction thus far by DBMS 101.
Marking Undo Blocks 151 for Tracked Tables 131:
The modifications made in the behavior of the foreground process in a preferred embodiment of unlimited Flashback are shown in simplified form in flowchart 201 in
Making Entries in History Table 153, Tcrv Table 118, and Ddl History Table 153:
What the Flashback archiver does in branch 333 depends on whether the operation that performed the modification was a DML operation, i.e., one that modified the value of a field in a row of the tracked table, or a DDL operation, i.e., one that modified the form of the tracked table (decision block 335). In the first case, the archiver takes branch 337, makes a new HTE 137 for the row's prior version (block 341) from the undo for the row and the metadata in the TCRVTE 161 for the row's prior version and deletes the TCRVTE 161 for the prior version of the modified row (block 347). Next, the Archiver creates a new TCRVTE entry 161 for the metadata belonging to the modified row (block 334), and Having made the new HTE 137, the Flashback archiver resets tracked flag 148 in the undo block (block 349). In the second case, the archiver takes branch 335 and creates a new DDLHTE for the current form of the table (block 343). When the Flashback archiver has finished processing a block of undo, the Flashback archiver resets the block's tracked flag T 148 (block 349).
The new HTE's metadata is copied from the TCRV entry for the prior state of the modified row. SSCN 138 is the SCN for the row's prior state; ESCN 139 is the SCN 147 from the undo block; OP 141 indicates the operation which produced the prior state of the row; the rest of the row contains a copy of the user data 142 in the prior state of the row.
Continuing with branch 339 and block 343, DDL history table 153's entries DDLHTE 154 contain only metadata describing each form the tracked table 131 has had since it has been tracked. In the new DDLHTE 154, the SSCN field is set to the commit SCN of the transaction making the change, CN field 155, TY field 157, and HCN field 159 are set as required by the DDL transaction, and the ESCN field is set to null. The ESCN field of the preceding DDLHTE is set to the value of the SSCN in the new entry. In a preferred embodiment, the only changes in the form of tracked table 131 which are tracked in history table 153 are adding columns to tracked table 131 and dropping columns from tracked table 131; the technique just described could of course be used with any other DDL operation.
When the Flashback archiver has processed all of the committed transactions (decision block 305), it sets the instance's BSCN to RSCN (338) and sleeps until the next time to run comes (341).
In a presently preferred embodiment, the processing shown in flowchart 301 and just described may be done by the foreground process as well as the Flashback archiver. This occurs in situation in which the Flashback archiver falls behind in processing undo blocks. The foreground process keeps track of how many transactions have their tracked flags 69 set, and when that number increases beyond a threshold, the foreground process begins processing undo blocks and continues until the number of marked transactions drops below a second threshold.
Performing Temporal Queries on a Tracked Table:
In General
When a temporal query is performed on a tracked table 131(i), DBMS 101 rewrites the query to use the information in the table 131(i)'s Flashback archive 171(i). The rewriting is done at the semantic analysis-optimizer phase of query generation. There are two kinds of rewrites 403 in a preferred embodiment. Which is done is determined by the values of the SCNs used in the temporal queries. In an AS OF query, there is only one such SCN, which is termed the QSCN in
As shown in
The DDLHTEs 154 for the relevant portion of DDLHT 131(i) are shown at 505. Starting at the bottom, there is a row with an SSCN of 03333 and an ESCN of 27322 for row D, which was in table 503(1) but was dropped at SCN 27322. The rows for columns A, B, and C have the same SSCN as row D, but since they are still in the table, their ESCN is null. The row for the addition of column E at SCN 15666 has an SSCN with that value and an ESCN of 27322, which is when the row was dropped. The top two rows are for the drop of columns D and E; since that is the current form of the table, these rows have SSCN=27322, which is the SCN for when the drop of rows D and E occurred and null ESCNs.
The results of various temporal queries on table 503 are shown at 507. The fundamental rule is that the columns of the result query will be selected from the union of all columns in the forms of the tracked table that existed during the range of SCNs specified in the query. The columns may come from the tracked table or from the tracked table's history table. In the AS OF queries, there is a single QSCN; in the VERSIONS query, there is a range of SCNs defined by LBSCN, the query lower bound SCN and UBSCN, the query upper bound SCN. The first AS OF query has a QSCN of 05000, which is within the range of SCNs during which version 503(1) of the table existed, so the result table for the AS OF query includes columns ABCD; the second AS OF query has a QSCN of 30000, which is within the range of SCNs during which current version of 503(3) has existed, so the result table includes only columns ABC. The first VERSIONS query has a LBSCN of 05000 and a UBSCN of 07000; these SCNs are within the range of SCNs during which version 503(1) existed, so the result table has columns ABCD; the second VERSIONS query has a LBSCN of 05000 and a UBSCN of 27000; these SCNs are within the range of SCNs during which version 503(1) and 503(2) existed, so the result table has columns ABCDE; the third VERSIONS query has a LBSCN of 05000 and a UBSCN of 30000; these SCNs are within the range of SCNs during which version 503(1), 503(2), and 503(3) existed, so the result table has columns ABCDE.
Example Query Rewrite
Query 609 replaces “GREATMOUNTAINEERS as of SCN 335672” from query 605 with an inline view that begins with “(Select t.ID” and ends with “NULL))))))”. The inline view contains limited Flashback versions query on current version of GREATMOUNTAINEERS 611 joined with a query that provides the corresponding rows for the versions from TCRV table 118 (615). The TCRV rows contain any temporal metadata for the rows of the current version of GREATMOUNTAINEERS 611 that is not available from the undo data, and consequently, the temporal query can be run on the inline view to find current versions of rows in GREATMOUNTAINEERS that satisfy the AS OF temporal query. The rows returned by the rewritten query is the union of the rows of history table 135(j) returned by query 607 with the rows of tracked table 131(j) returned by query 609.
Indexes on the Tables in the Flashback Archive
Since historical information can grow to be very large compared to the current information, there have to be efficient ways of indexing the historical information so that Flashback queries well into the past perform well. Here are some of the indexes which could be used and their applications.
A preferred embodiment may provide users with B-tree indexes and with spatial indexes for large data sets.
Dealing with the Storage Requirements of History Tables:
A history table 135 for a tracked table 131 will generally soon grow larger than tracked table 131; moreover, the period of time that a history table covers may be very long indeed. History tables 135 may thus be among the largest tables in the relational database system. Relational database systems manufactured by Oracle Corporation have a number of features which reduce the cost in both storage and access time of such very large tables. First, the administrator of an Oracle database system may partition a table in an Oracle relational database system into a number of smaller units. The partitions are invisible to user queries, but the program 109 which executes the queries determines which partitions are needed to run the query. The query is run on those partitions in parallel and only on those partitions. In the case of a history table 135, the table is partitioned on the ESCNs of the HTEs 137, i.e., the partitions contain history table entries belonging to non-overlapping ranges of ESCN values. Second, the administrator may assign the partitions to different storage tiers in the relational database system. Each storage tier has a different kind of persistent storage with different cost-per-bit and access speed tradeoffs. Typically, partitions with frequently-accessed data are kept in storage tiers with high-cost, high access speed persistent storage; as the data in a partition is less frequently accessed, the database manager moves the partition to cheaper and slower access tiers.
At 705 is shown a time line in which the times are expressed as increasing SCNs. The tracking which produces the example's hist_emp table (a history table for a tracked table of employees) begins at SCN 0. At that point, the Flashback archiver places hist_emp in a single partition 707(1) in TS#1. At ESCN 8, hist_emp has reached the maximum size possible in partition 707(1) and the archiver splits partition 707(1) into two partitions, 709(1 and 2). Partition 709(1) contains the rows of partition 707(1) and partition 709(2) receives the new rows that the archiver is adding to hist_emp. The process is repeated at 711, with 711(3) receiving the new rows. After splitting partition 709(2) to make 711(2 and 3), the archiver moves partition 711(1) into TS#3, which is in tier 2 storage. The process is again repeated at 713 and 715. At 715, the archiver moves partition 715(1) into TS#4 which is in tier 3 storage and moves partition 715(2) into TS#3.
Client Interfaces for Unlimited Flashback
The client interfaces for unlimited Flashback include DDL for creating, modifying, and dropping a Flashback archive as well as for enabling and disabling tracking and views that contain information about Flashback archives. Use of the interfaces and the views is governed by two privileges: the Flashback Archive privilege and the Flashback Archive Administer privilege:
This privilege can be granted and revoked as follows:
The default flashback archive is used for storing certain system and history specific metadata information. It is also used to store history for tracked tables when the user has not specified a flashback archive for that table. Users should have alter and DML privileges or be the owners of a table that that they wish to make a tracked table. Flashback privilege is necessary to access the history table as is required to perform any temporal query on the tracked table.
Creating, Modifying, and Dropping Flashback Archives
A flashback archive is created with the create flashback archive command. With this command, a flashback archive with given name and retention duration is created. The first tablespace of the flashback archive along with the maximum amount of space from this tablespace, which can be used, by this flashback archive is also specified. Also, this new flashback archive can optionally be made the default flashback archive for the system. The syntax of this command is as follows:
The attributes of a flashback archive can be modified using the alter flashback archive command. This command can be used to add/modify/remove tablespaces in the flashback archive as well as modify the retention duration, make default flashback archive and purge history. If tablespaces are removed, at least one tablespace should remain in the flashback archive, otherwise an error will be issued.
The drop flashback archive command is used to remove a flashback archive. All the historical information stored in it will be deleted. However, the tablespaces in it will not be dropped.
The syntax of the drop flashback command is as follows:
This removes flashback archive fla1 including all of the historical information stored in it. The tablespaces in fla1 will not be dropped.
Enabling and Disabling a Tracked Table 131
Enabling a table for tracking and disabling it from being tracked is done by adding a flashback archive clause to the ALTER TABLE DDL command. or in the case of a new table to the CREATE TABLE DDL command. The flashback archive clause looks like this:
This command enables or disables tracking of the history for a given table. By default, tracking will be disabled. When “FLASHBACK ARCHIVE” is specified tracking on the entire table will be performed. If “NO FLASHBACK ARCHIVE” is used tracking is disabled on the table. If the specified flashback archive does not exist then an error is issued. DBMS 101 responds to tracking being enabled by setting TT flag 129 in the tracked table's entry 128 in the data dictionary and initializing DDL history table 153. In the case of DDL history table 153, this is done by writing a row for each column of the tracked table to DDL history table 153. When tracking is disabled for a tracked table 131(i), all tables in the tracked table's Flashback archive 171(i) are dropped.
If the table was already a tracked table and a different flashback archive is specified then an error will be issued.
Views for Accessing Properties of Flashback Archives
The following views are provided:
The view DBA_FLASHBACK_ARCHIVE contains information about the flashback archives available in the system. Accessing this view requires a Flashback Archive Administer or DBA privilege. The view has the following columns:
comment on column DBA_FLASHBACK_ARCHIVE.FLASHBACK_ARCHIVE_NAME is ‘Name of the flashback archive’
comment on column DBA_FLASHBACK_ARCHIVE.FLASHBACK_ARCHIVE# is ‘Number of the flashback archive’
comment on column DBA_FLASHBACK_ARCHIVE.RETENTION_IN_DAYS is ‘Maximum duration in days for which data is retained in the flashback archive’
comment on column DBA_FLASHBACK_ARCHIVE.CREATE_TIME is ‘Time at which the flashback archive was created’
comment on column DBA_FLASHBACK_ARCHIVE.LAST_PURGE_TIME is ‘Time at which the data in the flashback archive was last purged by the system’
comment on column DBA_FLASHBACK_ARCHIVE.STATUS is ‘Indicates whether the flashback archive is a default flashback archive for the system’
The view USER_FLASHBACK_ARCHIVE contains information about the flashback archives that the user has access to. A user is shown only those flashback archives for which Flashback Archive Object privilege has been granted to the user accessing the view. This view can also be accessed by SYSDBA or users with Flashback Archive Administer privilege.
It has the following columns:
comment on column USER_FLASHBACK_ARCHIVE.FLASHBACK_ARCHIVE_NAME is ‘Name of the flashback archive’
comment on column USER_FLASHBACK_ARCHIVE.FLASHBACK_ARCHIVE# is ‘Number of the flashback archive’
comment on column USER_FLASHBACK_ARCHIVE.RETENTION_IN_DAYS is ‘Maximum duration in days for which data is retained in the flashback archive’
comment on column USER_FLASHBACK_ARCHIVE.CREATE_TIME is ‘Time at which the flashback archive was created’
comment on column USER_FLASHBACK_ARCHIVE.LAST_PURGE_TIME is ‘Time at which the data in the flashback archive was last purged by the system’
comment on column USER_FLASHBACK_ARCHIVE.STATUS is ‘Indicates whether the flashback archive is a default flashback archive for the system’
The view DBA_FLASHBACK_ARCHIVE_TS contains information about the tablespaces in the flashback archives. This will give information, with respect to flashback archive names, numbers and the tablespaces with their quotas. Access to the view requires a Flashback Archive Administer or DBA privilege. It has the following columns:
comment on column DBA_FLASHBACK_ARCHIVE_TS.FLASHBACK_ARCHIVE_NAME is ‘Name of the flashback archive’
comment on column DBA_FLASHBACK_ARCHIVE TS.FLASHBACK_ARCHIVE# is ‘Number of the flashback archive’
comment on column DBA_FLASHBACK_ARCHIVE_TS.TABLESPACE_NAME is ‘Name of a tablespace in the flashback archive’
comment on column DBA_FLASHBACK_ARCHIVE_TS.QUOTA_IN_MB is ‘Maximum space in MB that can be used for Flashback Archive from the tablespace. NULL indicates no Quota restriction.
The view DBA_FLASHBACK_ARCHIVE_TABLES contains information about all the tables that are enabled for Flashback Archive. Access to the view requires a Flashback Archive Administer or DBA privilege. It has the following columns:
comment on column DBA_FLASHBACK_ARCHIVE_TABLES.TABLE_NAME is ‘Name of the table enabled for Flashback Archive’
comment on column DBA_FLASHBACK_ARCHIVE_TABLES.OWNER_NAME is ‘Owner name of the table enabled for Flashback Archive’
comment on column
DBA_FLASHBACK_ARCHIVE_TABLES.FLASHBACK_ARCHIVE_NAME is ‘Name of the flashback archive’
comment on column DBA_FLASHBACK_ARCHIVE_TABLES.ARCHIVE_TABLE_NAME is ‘Name of the archive table containing the historical data for the user table’
The view USER_FLASHBACK_ARCHIVED_TABLES contains information about a user's tables that are enabled for Flashback Archive. A user is shown only those entries for which he has both alter privilege (or owner) on table and Flashback Archive Object privilege on the flashback archive on which the table has been archived. This view can also be accessed by SYSDBA or users with Flashback Archive Administer privilege. It has the following columns:
comment on table USER_FLASHBACK_ARCHIVE TABLES is ‘Information about the user tables that are enabled for Flashback Archive’
comment on column USER_FLASHBACK_ARCHIVE_TABLES.TABLE_NAME is ‘Name of the table enabled for Flashback Archive’
comment on column USER_FLASHBACK_ARCHIVE_TABLES.OWNER_NAME is ‘Owner name of the table enabled for Flashback Archive’
comment on column
USER_FLASHBACK_ARCHIVE_TABLES.FLASHBACK_ARCHIVE_NAME is ‘Name of the flashback archive’
comment on column USER_FLASHBACK_ARCHIVE_TABLES.ARCHIVE_TABLE_NAME is
‘Name of the archive table containing the historical data for the user table’
The foregoing Detailed Description has set forth to those skilled in the relevant technologies how to track row versions in a table by associating a Flashback archive with the tracked table, writing versions which are no longer in the tracked table and the temporal metadata for the versions to a history table in the archive and how to use the history table in a temporal query on the tracked table. The Detailed Description has further disclosed the best method presently known to the inventors of making and using Flashback archives. As disclosed, temporal queries using the Flashback archive take advantage of limited Flashback temporal queries on the tracked table which obtain versions of the tracked table's records which are not in the history table and their temporal metadata from the undo log. The Flashback archive thus makes it possible to execute temporal queries which will return versions of rows that are no longer in the undo log. A DDL history table in the archive further makes it possible to track rows across changes in the tracked table's form. In a preferred embodiment, the history table and DDL history table are constructed by a background process. The use of the temporal query on the undo log to obtain the versions of the tracked table's rows that are not yet in the history table and the use of system process to construct the history table makes it unnecessary to modify the tracked table's form in order to perform temporal queries on it. The use of a background archiving process to construct the history table, finally, keeps archive maintenance from interfering with query execution.
As will be apparent to those skilled in the relevant technologies, many embodiments of the invention other than the ones disclosed herein are possible. For example, triggers or the foreground process may be used to construct the history table. Further, the TCRV table may be used to obtain temporal metadata for all of the current rows of the tracked table. The use of the undo log in the preferred embodiment is to some extent an artifact of the prior existence of limited Flashback query; in other embodiments, other techniques for performing temporal queries on the current rows of the tracked table may be employed. Further, in other embodiments the temporal metadata may be expressed using time stamps instead of SCNs and may express a period of time in other ways than as an SSCN and an ESCN. Moreover, the additional information associated with a row version may be different from the additional information associated with the row version in the preferred embodiment.
Many of the characteristics of the preferred embodiment are of course determined by the relational database system in which the preferred embodiment is implemented. One such example is the existence of undo and its use; another is the technique used to compute GBSCN; another is the need to maintain a table that relates SCNs to time values; still another is the distribution of the history table across a set of partitions that belong to different storage tiers.
For all of the foregoing reasons, the Detailed Description is to be regarded as being in all respects exemplary and not restrictive, and the breadth of the invention disclosed herein is to be determined not from the Detailed Description, but rather from the claims as interpreted with the full breadth permitted by the patent laws.
This patent application claims priority from U.S. provisional patent application 60/862,225, Krishnaswamy, et al., Techniques for a tomatically tracking and archiving transactional data changes, filed Oct. 20, 2006. That application is incorporated by reference into the present patent application. A patent application having subject matter closely related to the present patent application is U.S. Ser. No. 11/291,446, Banerjee, et al., Database system that provides for history-enabled tables, which is assigned to the same assignee as the present patent application. U.S. Ser. No. 11/291,446 was filed Dec. 1, 2005 and published as U.S. published patent application 2007/0130226 on Jun. 7, 2007.
| Number | Date | Country | |
|---|---|---|---|
| 60862225 | Oct 2006 | US |
