- Create undo tablespace undo_tbs datafile '' size 500m; - Switch UNDO tablespace ALTER system set undo_tablespace = undo_tbs scpe = Both;
Ask for advice: About estimation UNDO table space >>>>>>>>>>>>>>>>
In the information, it sees the size of the estimated undo table space as follows: SELECT (UR * (UPS * DBS)) (DBS * 24) AS "Bytes" from (SELECT VALUE AS URFROM V $ Parameterwhere Name = 'undo_Retention') (SUM (SUM (Undoblks) / SUM ((end_time-begin_time) * 86400))) AS UPSFROM V $ undostat), (Select Value As dbsfrom v $ parameterwhere name = 'db_block_size');
Oracle9i database breaks time barriers that provide functions that do not use rollback (red return) segments and implement data recovery.
The Oracle9i database is characterized by a key preview to provide the ability to "flashback" queries. This query can query the data in the past some time in the database. Flash query is implemented using the same "undo" data. This "undo" data has been maintained by Oracle9i database to provide transaction support and ensuring multi-user reading data consistency.
Switch to automatic undo management
In order to make a flashback query, automatic undo management is required. That is to say, the database must be set to write the rollback data to "Undo" table space instead of writing to the roll band. Oracle9i Database Auto Manage "Undo" tablespace, so you don't have to worry about the establishment of rollback segments and set appropriate sizes for them. All jobs that need to be done is to determine the total amount of the required "undo" space, and set the size of the "Undo" table space accordingly.
Web locator
Jonathan Gennick provides information and links on www.gennick.com. For more information and instances of flash queries, see / Orshback.html?_template=/ocom/ocom_item_templates/print. OTN For more information on PL / SQL, see: OTN. oracle.com/tech/pl_sql/ purchase Oracle9i /oramag/store.html?_template=/ocom/ocom_item_templates/print multimedia coverage of PL / SQL is /oramag/ebusiness.html?_template=/ocom/ocom_item_templates/print Oracle.com About PL / SQL Frontier Technology / Database / Oracle Publish Published about PL / SQL Articles / ORAMAMAG/OrCle/01-jul/o41XML.html When running DML statements, the Oracle9i database will "Undo" information Enter "Undo" table space. This "Undo" message will remain for a while, and the time is short by the user. This reservation guarantees the implementation of the flashback query mechanism. Rollback segments do not support retention time, which is why you want to write "Undo" information to "Undo" table space before using the flashback query. Automatic undo management is the new feature of the Oracle9i database. Switching to automatic undo management status is quite simple, first create a "undo" table space:
Create undo tablespace undodataDatafile '/oradata/undodata01.dbf'size 40m;
After the "Undo" table space is established, you need some settings to the database instance before use: Specify the retention time, switch the database instance to the automatic undo management mode - complete the three initialization parameters, as follows Example:
Undo_management = autoundo_retention = 1800Undo_tablespace = undodata
The undo_retrion parameter indicates the time required to be reserved before the transaction processing from the submitted transaction processing is allowed to rewrite. Specifies the retention time in seconds. The default is 900 seconds (15 points), which is set to 1800 seconds (30 points).
Undo_retention and undo_tablespace are two dynamic parameters, while undo_management is not. Therefore, in order to make the settings to switch to automatic undo management, you need to turn off and restart the database instance.
Correctly setting "Undo" table space is very important. The new feature in the Oracle9i database is very helpful to this. V $ undostat returns data returned in the past 24 hours every 10 minutes. From the undoblks column, you can know the number of data blocks occupied by "Undo" data within each time interval. Let's take a look at "Undo" rate and one factor: I hope "Undo" data is retained time and add a sufficiently large security factor. For example, in my test instance, in either 10 minutes, "Undo" consumption is up to 189. Considering that three 10 minutes interval is 30 minutes, it is known that the "undo" table space I need is as follows: 189 * 8K (block size) * 3 (10-minute interval) = 4,644,864 bytes
It is much lower than the 40MB I assigned when establishing "Undo" tablespace.
Authorization to DBMS_FLASHBACK
Another essential condition for flashback query using Oracle9i Database is that you must have an Execute Access Authorization for the DBMS_FLASHBACK package. This package is all SYS. After SYS is logged in, authorize as follows:
Grant Execute On DBMS_FLASHBACK TO GENNICK;
Establish a flashback query
To make a flashback query, you need to call your query as a parameter, call two processes of the DBMS_FLASHBACK package. When returning from the process, it can be returned according to the specified DATE / TIME value, and can be returned according to the number of system changes (SCN). To specify a DATE / TIME value, DBMS_FLASHBACK.Enable_at_time can be used, as shown in Listing 1. Listing 2 in the next section will explain how to perform SCN-based flash queries.
As shown in Listing 1, the query running in dbms_flashback.enable_at_time and dbms_flashback.disable returns the correct result of the past 10 minutes. After starting the flashback query mode, you can only run the SELECT statement. Insert, Update, and DELETE statements cannot be run before using dbms_flashback.disable exiting the flash query mode.
Recover data using flashback query
The use of techniques used in Listing 1 are very limited. It allows you to view past data, but you cannot restore these data to current data. The true power of the flash query is in that it can establish a relationship between the past data and the current data, or recover the past data into current data.
The key to handling past data and current data is a cursor (CURSORS). After opening a cursor in the flashback query, the cursor is still in this state even if DBMS_FLASHBACK.DISABLE is called. In this way, to restore the past data to the current data, the flashback query mode should be started, open a cursor to return the data to be processed, and then perform the flashback query mode. The cursor will continue to return the past data, and the DML statement that is now created will also affect the current data.
A sample of data using a flashback query recovery is prevented by recovering an unexpectedly deleted data. In this example, the current SCN (available from DBMS_FLASHBACK.GET _SYSTEM_ CHANGE_NUMBER). The SCN is the basis when returning to the unrecordable deletion (delete). Note the usage process PL / SQL. In this case, the process coding must be used. Because the cursor must be opened in the flash query mode, the DML statement must be run after ending the flashback query mode to recover lost data. The lost data cannot be resumed by any type of INSERT ... SELECT FROM statement. In-depth understanding of flash inquiry
The results of the flash query are always based on the SCN basis. The Oracle9i database tracks an SCN every 5 minutes and records the last 5 days of operation information. Enable_at_time uses this record to view SCNs associated with your specified time and use this SCN as the foundation of the established flashback query. There are two situations here. One case is that if you want to return to a five-day state, you must be determined and used. Another situation is that the time value you transmitted to enable_at_time is within the last five days. This situation is shown in Listing 3. Please note: Although I returned to 2: 37 - this time is later than I delete the operation of all the data lines in the table, the flashback query still shows a number of rows that are not zero. Effective Flashback Time is over earlier than 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 flashback queries. When using sysdate, there are a variety of results. If there is a query that returns all the transaction processing issued today, and the query uses sysdate to determine "Today", then if you return to yesterday, what kind of result will be obtained? This requires the use of sysdate in all flash queries performed.
Similar to the sysdate function, whether it is in a flashback query mode, the dbms_flashback.get_system_change_number function always returns the current SCN.
You can only enter the flashback query mode at the beginning of the transaction. If the DML statement has been used to change the data, you must perform the flashback query after performing it. In addition, if you modify the table structure, the return time cannot be earlier than the last time change. This is because the flashback query uses data in the current data dictionary. Even if it is just a simple change to a column data type, it will limit the ability of the flashback query.
Is it a future inquiry?
Obviously, the flashback query is very useful for querying the most recent data. However, in some applications (such as software tracking for medical records), all historical data needs to be retained. These applications must follow all changes in the past by trigger and audit forms (or similar mechanisms).
Whether the flashback query allows the user to restore the lost or damaged data, do not have to wake up DBA in the middle of the night? The answer is yes. But it doesn't look like installing Oracle9i databases or authorization access to dbms_flashback. In order to make this feature truly useful to end users, the programmer should specially write software with flashback query.
