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

Home -> Community -> Mailing Lists -> Oracle-L -> FW: REP: Locally Managed Tablespaces

FW: REP: Locally Managed Tablespaces

From: Mark Leith <mark_at_cool-tools.co.uk>
Date: Wed, 25 Apr 2001 13:00:13 -0700
Message-ID: <F001.002F20F0.20010425085600@fatcity.com>

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
theoracledba_at_lycos.com



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: 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).
Received on Wed Apr 25 2001 - 15:00:13 CDT

Original text of this message

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