Oracle9i to manage the undo table space ____oracle

--Create the undo table space
Create undo Tablespace Undo_tbs DataFile ' size 500m;
--Toggle the Undo table Space
alter system Set Undo_tablespace = Undo_tbs scope = both;

Advice: About estimating the undo table Space >>>>>>>>>>>>>>>>>>>

The script for estimating the size of the undo table space on the data is as follows:
SELECT (UR * (UPS * dbs)) + (DBS *) as "Bytes"
From (SELECT value as UR
From V$parameter
where name = ' undo_retention '),
(SELECT SUM (undoblks)/sum
((((end_time-begin_time) *86400))) As UPS
From V$undostat),
(SELECT value as DBS
From V$parameter
WHERE name = ' db_block_size ');

The oracle9i database breaks the time barrier and provides the ability to recover data without using rollback (rollback) segments.

Oracle9i database A feature that has been highlighted is the ability to call "flash back" queries. This query can query data in the database for certain times in the past. The flash-back query is implemented using the same undo data. This undo data has been maintained by the Oracle9i database to provide transactional support and ensure consistency in multi-user read data.

Switch to automatic undo management

For Flash-back queries, you need to use automatic undo management. This means that the database must be set to write the rollback data to the undo table space instead of to the rollback segment. The Oracle9i database automatically manages the Undo table space, so you don't have to worry about creating a rollback segment and setting the appropriate size for it. All you need to do is determine the total amount of undo space you need, and then set the Undo table space size.

Web Locator Jonathan Gennick in Information and links are provided on the www.gennick.com. For more information and examples of flash-back queries, see /oramag/oracle/jan02/flashback.html?_template=/ocom/ocom_item_templates/print. For more information about Pl/sql in Otn, see: otn.oracle.com/tech/pl_sql/ Buy oracle9i /oramag/store.html?_template=/ocom/ocom_item_templates/print A multimedia report on Pl/sql /oramag/ebusiness.html?_template=/ocom/ocom_item_templates/print Oracle.com on the frontier Technology of Pl/sql /database/ An article on Pl/sql published by Oracle Publishing /oramag/oracle/01-jul/o41xml.html

When you run a DML statement, the Oracle9i database writes the undo (undo) information to the Undo table space. The undo information is retained for a period of time, specified by the user. This reservation guarantees the realization of the flash-back query mechanism. The rollback segment does not hold the retention time, which is why the undo information is written to the undo table space before the flash-back query is used.

Automatic undo management is a new feature of the Oracle9i database. The process of switching to an automatic undo management state is fairly straightforward, starting with an undo table space:

CREATE UNDO    tablespace undodatadatafile   '/oradata/undodata01.dbf ' SIZE 40M;

After you have established the Undo table space, you need to make some settings for the database instance before you use it: Specify the retention time and switch the database instance to automatic undo management mode by setting three initialization parameters, as shown in the following example:

Undo_management = Autoundo_retention = 1800undo_tablespace = Undodata

The Undo_retention parameter indicates the amount of time to retain the undo information from the committed transaction before it is allowed to overwrite. Specifies the retention time in seconds. The default value is 900 seconds (15 points), and this article is set to 1800 seconds (30 points).

Undo_retention and Undo_tablespace are two dynamic parameters, and undo_management is not. Therefore, in order for the settings that switch to automatic undo management to take effect, you need to shut down and restart the database instance.

It is important to set the size of the undo table space correctly. The new features in the Oracle9i database--v$undostat view is helpful. V$undostat returns a row of data that has been retained for the past 24 hours every 10 minutes. From the Undoblks column, you can know the number of blocks of data that the undo data occupies at each time interval. Let's take a look at the undo rate and a factor: the amount of time you want the undo data to remain and add a safety factor that is large enough. For example, in my test instance, undo (undo) consumes a maximum of 189 pieces in any 10-minute period. Given that the three 10-minute interval is the retention time of 30 minutes, the undo table space I need is as follows:

	189 * 8K (block size) * 3 (10-minute 	intervals) = 4,644,864 bytes

It is far below the 40MB I assigned when I created the Undo table space.

authorize access to Dbms_flashback

Another prerequisite for using the Oracle9i database Flash query is that you must have execute access authorization to the Dbms_flashback package. The package is SYS-owned. After you log in as SYS, authorize the following:

GRANT EXECUTE on Dbms_flashback to   Gennick;

Create a flash-back query

In order to perform a flash-back query, you need to call your query as a parameter and invoke the two procedures of the Dbms_flashback software package. When returned from a procedure, it can be returned by the specified Date/time value, or by the number of system changes (SCN). To specify the Date/time value, you can use Dbms_flashback. Enable_at_time, as shown in Listing 1. Listing 2 in the next section will show you how to do a flash-back query based on the SCN.

Run in Dbms_flashback, as shown in Listing 1. Enable_at_time and Dbms_flashback. The Disable query returns the correct result for the last 10 minutes. After you start the flash-back query mode, you can only run the SELECT statement. In the use of dbms_flashback. Disable you cannot run the INSERT, UPDATE, and DELETE statements until you exit the Flash query mode.

recover data using a flash-back query

The techniques used in Listing 1 are of limited use. It allows you to view past data, but you cannot restore the data to the current data. The real strength of a flash-back query is that it can establish relationships between past data and current data, or restore past data back to current data.

The key to processing both past and current data is the cursor (cursors). After a cursor is opened in the Flash-back query state, even if the dbms_flashback is invoked. DISABLE, the cursor is still in that state. In this way, to restore past data to the current data, you should start the Flash query mode, open a cursor to return the data you want to process, and then perform a flashback query pattern. The cursor continues to return past data, and the DML statement that is now established affects the current data.

An example of recovering data that was accidentally deleted using a flash-back query is given in Listing 2. In this example, the current SCN (available from Dbms_flashback) is recorded first. Get _system_ change_number). The SCN is the basis for returning to the Undo error deletion (delete). Pay attention to the use of procedural pl/sql. In this case, you must use procedure encoding. Because the cursor must be opened in Flash query mode, you must end the Flash query mode before you can run the DML statement to recover the lost data. Cannot pass any type of INSERT ... SELECT from statement to recover lost data.

deep understanding of flash-back queries

The result of the flash-back query is essentially always based on the SCN. The Oracle9i database tracks the SCN every 5 minutes and records the last 5 days of operational information. Enable_at_time uses this record to view the SCN associated with the time you specify and to use this SCN as the basis for the established flash-back query. There are two kinds of situations here. One scenario is that if you want to return to a state that was five days old, you must determine and use the SCN. In another case, the time you send to Enable_at_time is within the last five days. The situation is shown in Listing 3. Note that although I returned to 2:37-later than I deleted all rows of data in the table-the flash-back query still shows a non-zero number of rows. The effective flashback time is over before the time I have specified. If you want to get accurate data when you return, you need to make the SCN.

Listing 4 shows that the Sysdate function always returns the current system date, even if it is used for a flash-back query. When you use Sysdate, there are a number of results for the query. If you have a query that returns all of the transactions that were performed today, and the query uses Sysdate to determine today, what will happen if you flash back to yesterday? This requires consideration of the effect of using sysdate in all of the flash-back queries performed.

Similar to the Sysdate function, regardless of whether it is in a flashback query mode, Dbms_flashback. The Get_system_change_number function always returns the current SCN.

The Flash query mode can only be entered at the start of a transaction. If you have used a DML statement to change the data, you must perform a commit before you can make a flash back query. In addition, if you modify the table structure, the return time cannot be earlier than the time of the last change. This is because the flash-back query uses the data in the current data dictionary. Even simple changes to a column's data type limit the ability of a flash-back query.

is there a future for flash-back queries?

Obviously, the flashback query is very useful for querying the most recent data. However, in some applications (such as software that tracks medical records), all historical data needs to be preserved. These applications must rely on triggers and audit tables (or similar mechanisms) to track all past changes.

Flash-back queries can enable users to recover lost or corrupted data themselves without having to wake up the DBA in the middle of the night. The answer is yes. But it is not as easy as installing oracle9i databases or authorizing access to dbms_flashback. To make this feature truly useful to end users, programmers should deliberately write software with flash-back query capabilities.