| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: REP: Locally Managed Tablespaces
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
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
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
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#
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 theoracledba_at_lycos.com On Mon, 23 Apr 2001 11:01:37 Jared Still wrote:Received on Wed Apr 25 2001 - 13:11:04 CDT
>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: jkstill_at_cybcon.com
>
>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: 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).
>
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: theoracledba_at_lycos.com 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Patricia Ashe INET: pashe_at_TRUELINK.com 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras INET: Henry.Poras_at_ctp.com 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: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: mark_at_cool-tools.co.uk 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: 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).
 |
 |