Re: Read Only Tablespace Issue in 8.1.5 - Any Guidance??

Darn !

You published before I had a chance to put this on my web-site. ON THE VERSION I WAS USING, the extents were logged into UET$ - so LMTs don't avoid every access to the data dictionary.

It seems that the next time SMON kicks in with its 'any temp segments' test (e.g. at database startup) AND the LMT is read-write, the entries from UET$ are applied to the bitmap header and the space is cleared.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Howard J. Rogers wrote in message <3acb0938_at_news.iprimus.com.au>...


>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.

>

>

>

>

>

>

>