I haven't got this back on the list yet - and haven't recieved a message
from the list since about 9 this morning - so I thought I would resend this
just in case..
------
Right,
I have done a little diggin in to this and the upshot is that if you create
a LMT with UNIFORM extent sizes, then the tablespace will adere to those
extent sizes no matter what you specifiy in the INITIAL storage parameter.
I have a LMT already created with a UNIFORM extent size of 128k:
SQL> select tablespace_name,
2 initial_extent,
3 next_extent,
4 extent_management
5 from DBA_TABLESPACES
6 where tablespace_name = 'LOCAL';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN
------------------------------ -------------- ----------- ----------
LOCAL 131072 131072 LOCAL
I created a table in LOCAL as follows:
SQL> select table_name,
2 tablespace_name,
3 initial_extent,
4 next_extent
5 from DBA_TABLES
6 where table_name = 'EXTENT_TEST';
TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT
----------------- ------------------------------ -------------- -----------
EXTENT_TEST LOCAL 1048576 131072
You notice that DBA_TABLES *DOES* report that this table does in fact show
extent sizes of 1MB.
Now, I have a tool that can show a tablespace map of both dictionary and
locally managed tablespaces, this reported that the table EXTENT_TEST has a
total of 8 extents each at a size of 128K - indicating that Oracle seems to
take the initial extent size given by the create table statement, and makes
up that initial extent with a number of the uniform extents.
I also took Jared's statement, and if I am correct this will give you a row
for each extent allocated (correct me if I'm wrong Jared), so based on this
I did the following;
1 select count(*) from (select /*+ ordered use_nl(e) use_nl(f) */
2 ds.owner, ds.segment_name, ds.partition_name,
3 ds.segment_type, ds.tablespace_name,
4 e.ktfbueextno, f.file#, e.ktfbuebno,
5 e.ktfbueblks * ds.blocksize,
6 e.ktfbueblks, e.ktfbuefno
7 from sys.sys_dba_segs ds, sys.x$ktfbue e, sys.file$ f
8 where e.ktfbuesegfno = ds.relative_fno
9 and e.ktfbuesegbno = ds.header_block
10 and e.ktfbuesegtsn = ds.tablespace_id
11 and e.ktfbuesegtsn = f.ts#
12 and e.ktfbuefno = f.relfile#
13 and bitand(NVL(ds.segment_flags, 0), 1) = 1
14 and ds.segment_name = 'EXTENT_TEST'
15* )
SQL> /
COUNT(*)
----------
8
The tool that I use does not show any other extent sizes other than than
128K. This pretty much seems to that LMT's with UNIFORM extent sizes
actually DO stick that uniform size.
Sorry for the long reply.. I had a bit of spare time :)
HTH
Mark
-----Original Message-----
Sent: Tuesday, April 24, 2001 10:46
To: Multiple recipients of list ORACLE-L
I haven't tried it, but I recall hearing that all extents really are 1M, it
is just the data dictionary displays that are confusing. If you create a
table with initial=next=1.5M, I believe dba_tables will show the create
parameters, while dba_extents will show the actual extent sizes. I don't
have a good test database right now to try this. Maybe I have the dictionary
tables wrong (I guess I could check their sources).
Henry
-----Original Message-----
Sent: Tuesday, April 24, 2001 4:47 PM
To: Multiple recipients of list ORACLE-L
I am doing some research on locally managed tablespaces prior to using them.
Looking for problems, etc. This is the first negative response I have heard
about them. Perhaps, storage parameters were specified on the create table?
Patricia
-----Original Message-----
Sent: Tuesday, April 24, 2001 12:52 PM
To: Multiple recipients of list ORACLE-L
A couple of threads here and in other lists have mentioned this. A person
puts LMTSs up and sets uniform extents on and sets it to, say 1M. Later they
notice that not all extents are 1M.
Thanks,
Earl
---
TheOracleDBA
[EMAIL PROTECTED]
On Mon, 23 Apr 2001 11:01:37
Jared Still wrote:
>On Monday 23 April 2001 11:40, The Oracle DBA wrote:
>> Are you using uniform extents? We are told they are not really = and
>> actually are not the same at all over time.
>>
>
>Do you have a source for this information? A number of us have been
>using LMT's with uniform extents, and have not seen that.
>
>Jared
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Jared Still
> INET: [EMAIL PROTECTED]
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (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).
>
Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: The Oracle DBA
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Patricia Ashe
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Henry Poras
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mark Leith
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
