Oracle returning segment management (below)

zhaozj2021-02-16  76

Create rollback Syntax: CREATE [PUBLIC] ROLLBACK SEGMENT rollback_segment [TABLESPACE tablespace] [STORAGE ([INITIAL integer [K | M]] [NEXT integer [K | M]] [MINEXTENTS integer] [MAXTENTS {integer | UNLIMITED}] [Optimal {Integer [K | M] | NULL}])] Note: The rollback segment can specify private or public when creating, once created will not be modified. MINEXTENTS must be greater than or equal to 2 PctinCrease must be 0 Optimal If you want to specify, it must be greater than or equal to the initial size of the rollback segment (specified by mineltnce): Initial = next settings Optimal parameters to save space Use MaxExtents is unlimited Rollback segments should be created in a specific rollback segment table space instead: CREATE ROLLBACK segment rbs01 TableSpace Rbs Storage (Initial 100K Next 100K Mineltts 10 MaxExtents 500 Optimal 1000K); make the rollback segment online When it is created, The grouse is offline, cannot be used by the database, in order to make the returning segment is utilized, the rollback must be returned online. You can use the following command to return the rollback segment online: ALTER ROLLBACK Segment Rollback_SEGMENT online; example: ALTER ROLLBACK segment rbs01 online; In order to automatically online when the rollback segment is started at the database, the name of the return segment can be listed in the database parameter file. . For example, add the following line in the parameter file: rollback_segment = (RBS01, RBS02) Modify the storage parameters of the returning segment You can modify the storage parameters of the returning segment using the alter rollback segment command (including Optimal, MaxExtents). Syntax: ALTER ROLLBACK SEGMENT rollback_segment [STORAGE ([NEXT integer [K | M]] [MINEXTENTS integer] [MAXEXTENTS {integer | UNLIMITED}] [OPTIMAL {integer [K | M] | NULL}])] Example: ALTER ROLLBACK SEGMENT RBS01 Storage (MaxExtents 1000); Space for recycling the rollback segment If you specify the Optimal parameter of the rollback segment, Oracle will automatically reclaim the position to the Optimal specified. The user can also manually recover the space of the rollback segment. Syntax: ALTER ROLLBACK Segment Rollback_SEGMENT SHRINK [To Integer [K | M]]; Description: If you do not specify the value of To Integer, Oracle will attempt to reclaim the location of Optimal.

Example: Alter Rollback Segment RBS01 Shrink TO 2M; enabling the returning segment to reach the following two purposes Offline: 1. Block new transactions from using the rollback segment; 2. The rollback segment must be deleted. Syntax: ALTER ROLLBACK Segment Rollback_SEGMENT OFFMENT ROLLBACK_SEGMENT OFFMENT RBACK_SEGMENT OFFMENT RBA: ALTER ROLLBACK Segment RBS01 Offline After the event is over, the status will be changed to OFFLINE, and the status of the rollback segment can be queried by V $ ROLLSTAT. Delete Rolling Segments You can delete it when you need to be rebuilt or rebuild to change the initial, next, or mineltents parameter. To delete the rollback segment, do not allow the rollback segment to leave. Syntax: DROP ROLLBACK SEGMENT rollback_segment; example: DROP ROLLBACK SEGMENT rbs01; rollback information query the data dictionary used: DBA_ROLLBACK_SEGS information can be queried: rollback of identity (SEGMENT_ID), name (SEGMENT_NAME), where the table space (TABLESPACE_NAME) , Type (OWNER), Status. Example: SQL> Select segment_name, tablespace_name, Owner, Status from DBA_ROLLBACK_SEGS; Rouvery Statistics Data Dictionary: V $ ROLLNAME, V $ ROLLSTATATAT: SQL> Select 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; Roll Rolling Segment Current Activity Data Dictionary: V $ Session, V $ Transaction : 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 Number Planning For OLTP system, there is a lot of small transaction processing, general recommendations: many small back to roll; every four transactions; each rollback Do not exceed ten transactions. For batch processing, general recommendations: less big return segments; each transaction is a rollover segment. Question and Solutions Question and Solution Problem One: The spatial space required by the transaction requires not enough, which is expressed as a table space (ORA-01560 error), the rollback segment extends to the value of the parameter maxExtents (ORA-01628).

转载请注明原文地址:https://www.9cbs.com/read-15095.html

New Post(0)