Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Locally managed tablespace / truncate BUG ?

Re: Locally managed tablespace / truncate BUG ?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 14 Apr 2004 14:12:26 +0000 (UTC)
Message-ID: <c5jgsa$7d0$1@sparta.btinternet.com>

Notes in-line

-- 
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland  http://www.index.is/oracleday.php
June  2004      UK - Optimising Oracle Seminar


"G Dahler" <yellow-shark_at_spamex.com> wrote in message
news:XGafc.12839$vF3.1228622_at_news20.bellglobal.com...

>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> a écrit dans le message de
> news:c5hf4j$r50$1_at_sparta.btinternet.com...
> >
> > You could execute:
> >
> > alter table t1 deallocate unused keep 500k;
> >
> > That should take it down to just one extent
> > in your 1MB uniform LMT.
> >
> > --
> > Regards
> >
>
> It worked like a charm, even in my autoallocate LMT ! After truncating the
> table and using your sql statement, It even actually CHANGED the initial
> extent of the table to something smaller, and it also got rid of 20
> allocated extents !
>
> The question is: Why doesn't the "truncate" command, with the "drop
storage"
> option does not behave the same....
>
It's just the way the TRUNCATE command is defined.
> Another funny thing is that if you use:
>
> alter table t1 deallocate unused keep 512k you end up with a single extent
> of 576K in user_extents (Weird size for auto allocate LMT, never seen this
> before) and an initial extent of 576K in user_segments.
>
If your release of Oracle is the latest for your version, then I'd call that one in as a bug. It shouldn't be happening (although it is a multiple of 64K, so not entirely arbitrary). There were some early bugs with parallel CTAS trimming extents to odd sizes, even in uniform LMTs, but they should all have been fixed by now.
> And if you use alter table t1 deallocate unused keep 1M, you end up with 2
> extents, one of 1M, the other of 64K, and the initial extent in
user_extents
> becomes 1M+64K, that is 1114112...
>
This one is reasonable. The first block of the segment is taken up by the segment header (and the first three or four could be taken up by the seg header and a few bitmap blocks under ASSM) so you need a little over a MB if you want to keep a MB. And autoallocates allow for 1MB and 64KB extents, so your result is a good way of matching your request closely
>
>
Received on Wed Apr 14 2004 - 09:12:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US