Rollbacklmt
- 1. Rollback Segments in Locally Managed Tablespace Administration Tips Creating Rollback Segments in Locally Managed Tablespace There is no fundamental problem in creating rollback segments in locally managed tablespace (LMTs). In fact, they lend themselves to being created in such tablespaces, because LMTs are designed to minimise the performance impact associated with constant acquisition and dropping of extents -and if there is one thing rollback segments are likely to find themselves doing over time, it is frequently acquiring new extents (growing) and releasing old ones (shrinking). But it is impossible to create the first rollback segment in locally managed tablespace without generating an ORA-1522: cannot use system rollback segment for non-system tablespace 'BLAH' error message'. There are two underlying reasons for this. First, the only rollback segment created inherently with the database is one called SYSTEM, stored in the SYSTEM tablespace, and known as the "system rollback segment". All other rollback segments have to be created by yourself, manually, after the database has been created and opened. The system rollback segment has always been reserved for handling the DML on the data dictionary tables (which is generated when you perform DDL elsewhere in the database). It cannot hold the rollback for any DML statements affecting data stored anywhere other than the SYSTEM tablespace. For all updates to data in a non- system tablespace, you must have a non-system rollback segment available to house the rollback associated with that update. Second, Locally Managed Tablespaces record the acquisition of extents by segments in a bitmap, stored at the beginning of the datafile with which the tablespace was first created. The bitmap is initially 64K in size, and when a segment acquires an extent (either because it is being created, and thus acquiring its initial extent, or because data is being loaded into it and thus causing it to extend and acquire a next extent), the bitmap records the fact by flipping a "0" to a "1". Put these two points together, and the problem becomes clear: creating your very first non-system rollback segment in locally managed tablespace is a clear case of Catch-22. To create such a segment, you need to update the bitmap of the LMT involved to show that extents have been acquired by a new segment. But that requires updating data in a non- system tablespace. And any updates to data in a non-system tablespace have to have their rollback stored in a non-system rollback segment -which doesn't yet exist, because that's precisely what you're trying to create in the first place!! Fortunately, the cure is easy. Just create a temporary non-system rollback segment somewhere (it will have to be a dictionary-managed tablespace, obviously), and allow that to store the rollback generated by the bitmap update when you create the first rollback segment in a locally managed tablespace. Once the new LMT rollback segment is then Copyright ? Howard Rogers 2001 10/18/2001 Page 1 of 2
- 2. Rollback Segments in Locally Managed Tablespace Administration Tips created and brought online, it can become the segment that houses all rollback generated by the creation of all other LMT rollback segments, and there is thus no need to retain the original, temporary, dictionary-managed rollback segment at all. Putting that into a sequence of simple steps, then, you might try something like this: CREATE ROLLBACK SEGMENT TEMPRBS TABLESPACE SYSTEM; ALTER ROLLBACK SEGMENT TEMPRBS ONLINE; CREATE ROLLBACK SEGMENT RBS1 TABLESPACE RBSLMT; ALTER ROLLBACK SEGMENT RBS1 ONLINE; ALTER ROLLBACK SEGMENT TEMPRBS OFFLINE DROP ROLLBACK SEGMENT TEMPRBS; CREATE ROLLBACK SEGMENT RBS2 TABLESPACE RBSLMT; CREATE ROLLBACK SEGMENT RBS3 TABLESPACE RBSLMT; ...and so on. The key thing to note here is that the temporary first rollback segment is being created within the SYSTEM tablespace. This does not make this a 'system rollback segment'. That title is reserved for the segment created at the same time as the database was created with the actual name 'SYSTEM'. So, even though housed within the system tablespace, this new rollback segment is allowed to house rollback generated by updates to data taking place in non-system tablespaces -including our subsequent update to the bitmap at the start of the "rbslmt" tablespace. Now you don't have to create the temporary rollback segment in the system tablespace - any old dictionary-managed tablespace will do. Indeed, it is usually frowned upon to create any segments of your own within the system tablespace. So if you can create it elsewhere, do so. The only reason I show the system tablespace being used here is because (1) it's guaranteed to exist; (2) it's guaranteed to be dictionary-managed and (3) the segment being created is so small that it probably won't do any damage to house it there. Copyright ? Howard Rogers 2001 10/18/2001 Page 2 of 2