RE: locally managed autoallocate (was: Separate Indexes and Data)

Well, sort of. If I had a 9i DB to play with, I'd try this myself...

1. Create an SMT.
2. Create 50 tables in that TS.
3. Drop every even table just created.
4. Extend one of the remaining tables until you can't.

Depending on the size of the TS created and the blocksize, you could have a situation where there's enough freespace in the TS, but not contiguous. Thus, fragmentation.

Granted, this is isn't a typical case, but over a year or two, I could see it happening here.

Maybe I'll install 9iR2 at home tonite to try this...<sigh>

Rich

Rich Jesse                           System/Database Administrator
rjesse_at_qtiworld.com                  Quad/Tech Inc, Sussex, WI USA

> -----Original Message-----
> From: Niall Litchfield [mailto:niall.litchfield_at_dial.pipex.com]
> Sent: Thursday, October 02, 2003 5:10 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: locally managed autoallocate (was: Separate Indexes and
> Data)
>
>
> > > -----Original Message-----
> > > From: Jesse, Rich
> > > Sent: Wednesday, October 01, 2003 9:49 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: RE: locally managed autoallocate (was: Separate
> Indexes and
> > > Data)
> > >
> > > Theoritically, perhaps, but what if an existing table needs
> > > to auto-extend
> > > at 1M and all that's left in the table is 16 (or whatever)
> >
> > (blush) Obviously, that's supposed to say "left in the tableSPACE".
>
> 9.0.1 'unable to allocate extent'
>
> C:\Documents and Settings\Niall>sqlplus niall/niall
>
> SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 2 21:59:45 2003
>
> Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
>
>
> Connected to:
> Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
> With the Partitioning, OLAP and Oracle Data Mining options
> JServer Release 9.2.0.1.0 - Production
>
> SQL> create tablespace test_auto
> 2 datafile 'c:\oracle\oradata\home9i\test_auto.dbf' size 1152k
> 3 extent management local;
>
> Tablespace created.
>
> SQL> create table t1(n number,charcol char(200))
> 2 tablespace test_auto
> 3 ;
>
> Table created.
>
> SQL> select count(*) from dba_extents where segment_name='T1';
>
> COUNT(*)
> ----------
> 1

>  

[snip]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).