Read Only Tablespace Issue in 8.1.5 - Any Guidance??

Sorry to spring this one so late, but....

If you make a tablespace read-only, it has always been possible to drop segments from within that tablespace, since that only requires updates on the SYSTEM tablespace, which is always read-write.

No problems so far.

Now make a locally-managed tablespace read only, and drop a segment created earlier. The statement will process, and the segment will be dropped (ie, a select * from dba_segments where segment_name='NAME' reports zero rows). BUT... and here's the nasty surprise... a select * from dba_extents where tablespace_name='ROTBS' will faithfully record the continued existence of the extents for the dropped table, only the segment_type is listed now as
'temporary' instead of 'table', and the segment_name is listed as '10.17'
(or some other obscure number) rather than 'tabletest' as it used to be.

To prove the point, a 'drop tablespace' command results in the error about
'cannot drop a tablespace since it contains objects' -even though you know
damn well that this was the only object in the tablespace, and was dropped ages ago!

In short, dropping a table from read only locally managed tablespace in 8.1.5 results in the extent allocations for that object being left behind in the tablespace -thus chewing up space that cannot (apparently) be freed by normal means.

The reason for this is fairly obvious... the bitmap at the header of the tablespace is not allowed to be reset as a result of the thing being read only. So whilst the ownership of the extent is deleted from the (read write) data dictionary, the bit itself cannot be updated to free the extent.

So clearly this is another lovely 8.1.5 bug (incidentally, it's been fixed in 8.1.7 as far as I can make out). And I dare say that there is a Metalink report on this (which I cannot access). And the workaround is obvious -do the drop command after temporarily turning the tablespace into read write mode. But I'm wondering if there is a way to free the extent after the damage has been done?

Amazing the things you stumble across after a year working with the damn thing!!

Regards
HJR For the record, the demo went like this...

Create tablespace blah datafile '$HOME/blah.dbf' size 1m extent management local uniform size 100k;
create table blahtest(col1 char(5)) tablespace blah; Alter tablespace blah read only;
Drop table blahtest; ["Statement Processed" Ha!] Select * from dba_segments where segment_name='BLAHTEST'; [0 rows] Select * from dba_extents where tablespace_name='BLAH' [1 row.... oooops.]

Solaris, 8.1.5

Cheers. Received on Wed Apr 04 2001 - 06:44:34 CDT