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

Thanks Jonathan.

Pesky critters, huh?!

Regards
HJR "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:986385034.3897.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> 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.
> >
> >
> >
> >
> >
> >
> >
>
>
Received on Wed Apr 04 2001 - 15:51:44 CDT