Oracle Database ORA-01555 snapshot old (undo tablespace: undo tablespace)

Oracle Database ORA-01555 snapshots are old (undo tablespace: UNDO tablespace) UNDO tablespaces are used to store UNDO data, oracle writes the old data of these operations to the undo segment when performing DML operations, to ensure rollback or consistent reading, the temporary tablespace is mainly used for querying and storing some buffer data. You have heard that UNDO is also a temporary table, probably because neither of the two tablespaces saves data permanently. Explain oracle undo tablespace is used to store undo data. When you perform DML operations (insert, update, delete), oracle writes the old data of these operations to the undo segment. The role of undo data 1. When the DML operation is executed to modify data, the old data is stored in the undo segment. Old data can be rolled back if the data is submitted, the rollback segment is not full, or the rollback segment is time-out. 2. read consistency before data is committed after the DML operation is passed, the data read by other users is old data in the rollback segment. Use undo parameter 1. undo_management this initialization parameter is used to specify the undo data management mode. If you want to use the automatic management mode, you must set it to auto. If you want to use the manual management mode, you must set this parameter to manual. When you use the automatic management mode, oracle uses the undo tablespace management, in manual management mode, oracle uses rollback segments to manage undo data. Note that when the automatic management mode is used, if the initialization parameter UNDO_TABLESPACE is not configured, oracle will automatically select the first available UNDO tablespace to store UNDO data. If there is no available UNDO tablespace, oracle uses the SYSTEM rollback segment to store UNDO records and records warnings in the ALTER file. 2. UNDO_TABLESPACE this initialization parameter is used to specify the UNDO tablespace to be used by the routine. When using the automatic UNDO management mode, you can configure this parameter to specify the UNDO tablespace to be used by the routine. in the RAC (Real Application Cluster) structure, because an UNDO tablespace cannot be used by multiple routines at the same time, each routine must be configured with an independent UNDO tablespace. 3. UNDO_RETENTION this initialization parameter is used to control the maximum retention time of UNDO data. The default value is 900 seconds. From 9i, you can specify the retention time of undo data by configuring this initialization parameter, so as to determine the earliest time point that can be viewed by the Flashback Query feature (Flashback Query. manage rollback segments manually:

SQL> show parameter undo; NAME TYPE VALUE types ------------- ------------------------------ undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS1SQL> show parameter transactions; name type value ------------------------------------ ----------- ------------------------------ transactions integer 187 ---------- Number of transaction connections supported by the system preparation. Transactions_per_rollback_segment integer 5 -------------- the number of transaction connections supported by each rollback segment. Number of rollback segments = 187/5 SQL> show parameter rollback; NAME TYPE VALUE items ----------- ---------- values string LOWrollback_segments string ---------------------- set the number of rollback segments transactions_per_rollback_segment integer 5SQL>

 

When the database is started, it determines whether the private rollback segment meets our transaction requirements. If yes, the database is opened; otherwise, the database will go to the public rollback segment. Solution: User user1 updates the table. User user2 reads data in the table before submitting the table, and reads data in large batches (for example, it takes 3 minutes) in the past three minutes, what is the impact of the submission of user1? How can we ensure read/write consistency at this time? At this time, the DBMS should ensure that there is a large enough undo tablespace to store the values before the modification, so as to ensure that the data read by user2 is consistent data before the modification. the next read is the updated data. the ora-01555 snapshot is old because the undo space is not large enough, some of the undo data is overwritten, the user cannot get the data before the modification. Undo data can be divided into three types: Active undo: undo data of uncommitted transactions, which cannot be overwritten and used to roll back rollback transactions. Expired undo: undo data of the committed transaction, which can be overwritten. Undo that has not expired: the transaction has been committed, but before the transaction is committed, some queries are in progress, it needs to read the data before the submission, this part of the data is not expired data. If this part of undo data is overwritten, A ora-01555 error occurs. One solution is to specify the undo tablespace parameter UNDO_TABLESPACE and set the undo tablespace management method to AUTO Scaling: UNDO_MANAGEMENT = AUTO. This method may produce the following results: Because the undo tablespace contains too many undo data that has not expired (unexpired), the new transaction cannot write undo data to it, A ORA-30036 error occurs in transaction.