Oracle undo complexity-oracle Core Technology Reading Notes 4, undo-oracle

Oracle undo complexity-oracle Core Technology Reading Notes 4, undo-oracle

I. Overview

Undo stores old data. For example, if you modify A record to change abc of column A to def, abc is saved in undo. There are two purposes: 1. if your transaction has not been committed, but you have modified column A, when someone else reads this data, they should not be able to see the modified content def, and they should only see abc, at this time, you need to read undo to get abc. 2. If your transaction fails later, you need to roll back column A from the modified value def to the previous value abc, and abc should also be retrieved from undo. This article briefly introduces how read consistency (that is, the uncommitted content that someone else cannot read) and rollback (transaction failure, rollback modification records) are implemented.

Ii. read consistency

If the transaction is started at on session 1, on Session 2 changes A piece of data from A to B, and then on Session 2 changes the same piece of data from B to C, session 2 has not been submitted. When the session reads this data at, A should be read, but the cached data has been changed to C. How can we get. In this way, oracle finds this data in the cache and finds that this data has been modified by another session, and the other session has not been submitted. The modification time is, it is later than when my session is started at, so I find the undo record corresponding to the modification of another session in the transaction slot of the data block. At this time, the undo block will be read, if undo has been refreshed and output to the disk, you need to read it again from the disk. At this time, we found to modify the corresponding undo block, and found the old data as B. If the undo record is before, we do not need to look forward, but we found it, we also need to check whether there is an undo record before this undo record (the undo record address itself will record the previous undo record). We can find the undo record. Then we can find that there is no more, so the old data A in the undo record is the data we are looking for. It sounds like a detour. Give a picture:

Iii. rollback

Rollback means that the transaction fails, and we need to roll back all the modifications made by this transaction. When talking about read consistency, we mainly focus on finding a single block where data is located, and then finding a linked list of all the undo records of this block. From the beginning of the block, keep searching until you locate an old enough undo data. In fact, rollback is similar. rollback refers to the transaction history. We need all the undo records in the transaction to record the pointer linked list in reverse sorting and roll back from the last modification, always roll back to the first modification made by the transaction.

Rollback and read consistency look similar. In fact, there is an important difference. read consistency means that we generate a copy of the data block in the memory and apply undo records to the copy block, once the related operation is completed and the old data is obtained, the block copy will be quickly discarded, because our goal is to get the old data, and it is not really necessary to change the data. Rollback is different. rollback is to get the real data block and apply the undo record to restore it.

1. the rollback operation is performed on the current data block. When undo is applied to the current data block, the final modification must be made to the disk. Read consistency is not required.

2. the rollback operation is the current data block, so when we change it (rollback is changed), redo will be generated. As for whether to generate undo, This is not verified.

3. the rollback operation is not only consistent with reading, but may also need to read the undo data from the disk, and may also need to read the modified data from the disk, because if the modified data has been refreshed and output to the disk, you also need to re-tune it from the disk and then roll back its value to the old value. If the transaction is long and the transaction fails at the end, the cost is very high. The rollback takes about the same time as the normal transaction, or even more.

How to change the complexity of oracle Database User passwords

Oracle Password Complexity settings (Oracle_Password_Complexity)

I. Oracle_Password_Complexity:

SQL> alter system set resource_limit = true;

SQL> @ $ ORACLE_HOME/RDBMS/ADMIN/utlpwdmg. SQL → [verify_function | verify_function_11G]

SQL> alter profile default limit password_verify_function verify_function;

# Cancel the Oracle Password Complexity check:
SQL> alter profile default limit password_verify_function null;

SQL> SELECT profile, resource_type, resource_name, limit FROM dba_profiles WHERE resource_type = 'Password' AND profile = 'default ';

1. FAILED_LOGIN_ATTEMPTS: the user is locked after the logon attempt fails n times.

2. PASSWORD_LOCK_TIME: the number of failed logon attempts reaches the specified number of times. The user lock duration, in the unit of "Day.

3. PASSWORD_LIFE_TIME: the lifecycle of the user password.

4. PASSWORD_GRACE_TIME: indicates the number of days that a user's password can be used after its life cycle is exceeded. A prompt indicating that the password is about to expire is displayed during the logon period.

5. PASSWORD_REUSE_TIME: specifies the number of days before the password cannot be reused.

6. PASSWORD_REUSE_MAX: the number of times that the password must be changed before it reaches the time specified by PASSWORD_REUSE_TIME.

For example, if PASSWORD_REUSE_TIME = 30 and PASSWORD_REUSE_MAX = 10, you can reuse the password after 30 days. The password must be changed more than 10 times.

7. PASSWORD_VERIFY_FUNCTION: Oracle allows passing complex PL/SQL password verification scripts as parameters to PASSWORD_VERIFY_FUNCTION. In addition, it provides a default script, but users can create their own verification rules or use third-party software for verification.

8. Password Verify Function:

When you create a password verify function for verifying the user password, this function can verify the following password characteristics:

1. The minimum number of characters for the password.

2. The characters that the password must contain, such as when a password shoshould contain a specific number of numeric, alphabetic or... The remaining full text>

Oracle undo-Optimization Problems

Give you a command:

Log On As SYSDBA and run the following command in SQL PLUS:

Purge recyclebin;

Then you can check the size of UNDOTBS.

I hope it will help you.