Undo series learning-Pub brothers posts

Questioner: xmas171688

Question Time:14:45:18

Problem:

In the following two tables, V $ rollname and V $ rollstat can help me explain what each column of V $ rollstat is doing. Please help! I want to know which column is the size of the rollback segment!

Organize:

What is each column, I think, the official website is very clear: http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2143.htm#REFRN30210

Common columns in V $ rollstat:

1) USN: rollback segment identifier

2) rssize: Default size of rollback segments

3) xacts: Number of active transactions

Columns used for incremental operations within a period of time:
Writes: Number of writes to the rollback segment (unit: bytes)
Shrinks: Number of rollback segments contracted
Extends: Number of extended rollback segments
Wraps: Number of wrap
Gets: Get the number of rollback segment Headers
Waits: Number of rollback segment headers waiting

With USN, we can connect v $ rollname and V $ rollstat to view more meaningful rollback segment statistics. For example:

SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.statusFROM v$rollname n, v$rollstat sWHERE n.usn = s.usn;

Note:
By dividing the time spent by the number of flip times, you can get the average time of a rollback segment flip (WRAP. This method is usually used to specify the appropriate rollback segment size in a long query to avoid the 'snapshot too old' error. At the same time, you can check the extends and shrinks columns to see if optimal needs to be added.

 

Questioner: traveling all the way

Question Time:10:57:32

Problem:

Ladies and gentlemen, I would like to ask, which operations can be rolled back? Can I roll back after commit? Which operations will be automatically submitted?

Organize:

DML rollback
Cannot roll back after commit
DDL and DCL are automatically submitted.

 

Questioner: jvkojvko

Question Time:2011-6-28 11:06:48

Problem:

After inserting the data for 15 hours, the Data fails and is stopped manually. After the data is rolled back for one day and one night, the table is still locked. Is there a good solution? The insert data volume is about 3000 million

Organize:

First of all, we need to learn from this problem: a large volume of data is inserted, remember to commit at intervals to control the length of the transaction! Otherwise, only wait is supported.

 

Questioner:Lyxing

Question Time: 08:54:51

Problem:

The server is a Red Hat Linux Enterprise 4 and oracle9204. It is found that the rollback space of a database has been increasing, and 98.63% is used, but no exception is found. What is the problem? It turned out to be a 1g rollback, and then expanded to 2G, and now it's almost full.

Organize:

Cause:
1. You need to roll back large transactions;
2. Show parameter undo_retention; -- in this case, the value of undo_retention is reduced.
3. Descriptions of rollback segments:
Insert records rowid only
Update only records the old value of the update field (the former image ). -- Too many updates
Delete generates the most rollback records and records the whole row. -- Too many delete operations

Questioner: Lmin_432930

Question Time:16:29:46

Problem:

For Oracle rollback problems, please help answer the non-archive mode of the database. Set a tablespace test to contain the TT table. Step: 1. modify the content of the TT table, but did not submit 2. Replace the test tablespace offline normal3 and switch logfile several times until the TT table's log overwrites 4. Execute rollback5 and switch logfile several times until the current log overwrites 6. Replace the test tablespace online7, query TT content normal problem: 1. After the test tablespace is offline, the checkpoint saves the modified data to the data file, rollback, switch to clear the log, and then online the test tablespace to run normally. So how does the system know whether data modification is commit or rollback? Is there any mechanism, or is there a problem I understand? Thank you!

Organize:

1. Modify TT table content, but not submit
2. Run the test tablespace offline normal.

Offline has been implicitly committed.

 

Questioner: susuusus

Question Time:2004-11-23 09:30:35

Problem:

My system is a Windows database, oracle8.1.7. Now there is a problem: the RBS tablespace is a 2G tablespace with four rollback segments, the current situation is that after oracle is started on every boot, the RBS tablespace usage is 99% and the four rollback segments are in the offline status. I need to work online manually every time. I don't understand why and want to know how to solve it.

Organize:

1. Create a public segment and add rollback_segments = (R01, r02, r03, r04) to the init. ora file)

2. If your RBS tablespace has 2 GB, I think you can create more rollback segment, and the usage of rollback segment is very high. This is determined by the application. You can submit more. can it reduce its usage. However, this does not affect your data usage.