Oracle rollback Segment Management (bottom)

Oracle Create rollback SEGMENT Syntax: Create [public] ROLLBACK SEGMENT rollback_segment [tablespace tablespace] [STORAGE ([Initi AL integer[k| M]] [NEXT integer[k| M]] [minextents integer] [maxtents {integer| Unlimited}] [OPTIMAL {integer[k| m]| 　　 NULL}]) Note: The rollback segment can be specified private or public at creation time and cannot be modified once created. Minextents must be greater than or equal to 2 pctincrease must be 0 optimal if you want to specify, you must be greater than or equal to the initial size of the rollback segment (specified by minextents) Recommendation: In general, Initial=next settings Optim 　　 Al parameters to conserve space use do not set maxextents for unlimited rollback segments should be created in a specific rollback segment table Space Example: Create ROLLBACK SEGMENT rbs01 tablespace RBS STORAGE (INITIAL 100K NEXT 100K minextents maxextents OPTIMAL 1000K); Make the rollback segment online when the rollback segment is created, the rollback segment is offline and cannot be used by the database, and the rollback segment must be online in order for the rollback segment to be exploited by the transaction. 　　You can use the following command to make the rollback segment online: ALTER ROLLBACK SEGMENT rollback_segment; Example: ALTER ROLLBACK SEGMENT rbs01 online; In order for the rollback segment to automatically line up when the database is started, you can list the name of the rollback segment in the database's parameter file. For example, add the following line to the parameter file: rollback_segment= (RBS01,RBS02) to modify the storage parameters of the rollback segment you can use the Alter ROLLBACK SEGMENT command to modify the storage parameters of the rollback segment (including Optimal, 　　maxextents). Syntax: ALTER ROllback SEGMENT rollback_segment [STORAGE ([NEXT integer[k| M]] [minextents integer] [maxextents {integer| Unlimited}] [OPTIMAL {integer[k| m]| ]] Example: ALTER ROLLBACK SEGMENT rbs01 STORAGE (maxextents 1000); Reclaim space for a rollback segment if the optimal parameter of the rollback segment is specified, Oracle automatically returns the rollback segment to the optimal specified location. 　　The user can also manually recycle the space of the rollback segment. Syntax: ALTER ROLLBACK SEGMENT rollback_segment SHRINK [to Integer [k| 　　M]]; 　　Note: If you do not indicate the value to Integer, Oracle will attempt to reclaim the location of the optimal. Example: ALTER ROLLBACK SEGMENT rbs01 SHRINK to 2M; 　　Take the rollback segment offline for the following two purposes, the rollback segment is offline: 1. Prevents new transactions from using the rollback segment; 2. The rollback segment must be deleted. 　　Syntax: ALTER ROLLBACK SEGMENT rollback_segment OFFLINE; 　　Example: ALTER ROLLBACK SEGMENT rbs01 OFFLINE; Note: If a transaction is using the rollback segment, the status of the rollback segment will be pending OFFLINE when the command is run. After the transaction ends, the status is changed to offline, and the status of the segment can be rolled back by V$rollstat query. Delete a rollback segment when the rollback segment is no longer needed or to be rebuilt to change the Initial,next or minextents parameters, you can delete it. To delete a rollback segment, do not allow the rollback segment to be offline. Syntax: DROP ROLLBACK SEGMENT rollback_segment; Example: DROP ROLLBACK SEGMENT rbs01; The data dictionary used for querying the information of the rollback segment: Dba_rollback_segs can query information: The identity of the rollback segment (segment_id), the name (Segment_name), the tablespace (tableSpace_name), type (OWNER), State (status). Example: Sql>select segment_name,tablespace_name,owner,status from Dba_rollback_segs; Statistics data dictionary for rollback segment: v$rollname,v$rollstat example: Sql>select n.name,s.extents,s.rssize,s.optsize,s.hwmsize,s.xacts, S.status from V$rollname n,v$rollstat s WHERE n.usn=s.usn; The current active transaction data dictionary for the rollback segment: v$session,v$transaction example: Sql>select s.username,t.xidusn,t.ubafil,t.ubablk,t.used_ublk from 　　 V$session s,v$transaction T WHERE s.saddr=t.ses_addr;　　　　　　USERNAME xidusn ubafil ubablk used_ublk------------------------------------------------SYSTEM 2 2 7 1 SCOTT 1 2 163 1 2 rows selected. 　　The number of rollback segments for OLTP systems, there are a large number of small transaction processing, generally recommended: a large number of small rollback segments; every four transactions a rollback segment, each rollback segment does not exceed 10 transactions. For batch processing, it is generally recommended that fewer large rollback segments be rolled back, and one rollback segment per transaction. 　　The problem of rollback segment and its solution problem one: The transaction requires a rollback segment space is not enough, the expression is the table space full (ORA-01560 error), rollback segment extension reached the value of the parameter maxextents (ORA-01628). 　　Workaround: Add a file to the rollback segment tablespace or make the existing file larger, and increase the value of the maxextents. Problem Two: Read consistency error (ORA-01555 SNAPSHOT TOO old) Solution: increase the value of minextents, increase the size of the area, set a high OPtimal value.