Lmt

[badrinathn]

Hi,

I have read these lines about LMT many times:
"If you create a new table, the first extent will be 64KB in size. Subsequent extents will also be 64KB, until the table reaches 1MB in size. At that point the size of subsequent extents for the table will be increased to 1MB. When the table reaches 64MB in size, the extent size will be increased again to 8MB. Finally, if the table reaches a size of 1GB, the extent size will be increased one last time, to 64MB."

But I have a problem. I have imported 2 tables from a DMT tablespace to LM tablespace.

I see one table (with about 20 extents) following the above rule. While another table has only 4 extents and all the extents are 1MB. I am using LMT with AUTOALLOCATE option.

How did oracle chose to make 4 extents of 1MB, which I was suppose to create 64k chunks...

Thanks
Badrinath

[adewri]

Originally posted by badrinathn
How did oracle chose to make 4 extents of 1MB, which I was suppose to create 64k chunks...

Thanks
Badrinath

Paste the statement that you used to create both tablespaces...

[abhaysk]

Originally posted by adewri
Paste the statement that you used to create both tablespaces...

One of you are wrong, either you or Badrinath.

PS his part of post

Originally posted by badrinathn
I have imported 2 tables from a DMT tablespace to LM tablespace.

Which suggests he imported both tables to same TS (LMT).

Abhay.

[adewri]

Originally posted by abhaysk
One of you are wrong, either you or Badrinath.

PS his part of post



Which suggests he imported both tables to same TS (LMT).

Abhay.

What ever, i asked him to paste the statement used to creat the tablespace.

Also do the import with indexfile=filename option so that we can see what create table command is issued by the import.

[jmodic]

Check the discussion on page 3 of this thread (along with Jonathan Lewis findings):

http://www.dbasupport.com/forums/sho...0&pagenumber=3

[badrinathn]

Sorry for getting back so late:
Thank you very much for all your suggestions.

Now: Here is the tablespace stm

Create tablespace TEST datafile '/u01/prodwm6/dbfs/gimtest01_data01.dbf' size 100M
extent management local autoallocate;

I am creating 2 tables (Item_master & Inventory Master) with the following command from the indexfile of import.


REM CREATE TABLE "GIMPROD2"."ITEM_MASTER" ("REGION_CODE" VARCHAR2(5),
REM "DIVISION_CODE" VARCHAR2(4), "MANU_KEY" VARCHAR2(4), "PRC"
REM VARCHAR2(4), "PART_NO" VARCHAR2(30), "PART_DESC" VARCHAR2(255))
REM PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 4382720
REM FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "GIMPRO_DATA" LOGGING
REM NOCOMPRESS ;
REM ... 91674 rows


REM CREATE TABLE "GIMPROD2"."INVENTORY_MASTER" ("REGION_CODE"
REM VARCHAR2(5), "DIVISION_CODE" VARCHAR2(4), "PRC" VARCHAR2(4),
REM "PART_NO" VARCHAR2(30), "PART_DESC" VARCHAR2(255), "QTY_ON_HAND"
REM NUMBER(8, 0), "UNIT_PRICE" NUMBER(15, 3), "CURRENCY_CODE"
REM VARCHAR2(5), "LEAD_DAYS" NUMBER(3, 0), "UNIT_OF_MEASURE" VARCHAR2(4),
REM "PRODUCT_STATUS" VARCHAR2(2), "INSERT_TIME" DATE) PCTFREE 10 PCTUSED
REM 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 71680 FREELISTS 1 FREELIST
REM GROUPS 1) TABLESPACE "GIMPRO_DATA" LOGGING NOCOMPRESS ;
REM ALTER TABLE "GIMPROD2"."INVENTORY_MASTER" MODIFY ("INSERT_TIME"
REM DEFAULT sysdate) ;
REM ... 33456 rows

My extent allocations are

ITEM_MASTER 0 1048576
ITEM_MASTER 1 1048576
ITEM_MASTER 2 1048576
ITEM_MASTER 3 1048576
ITEM_MASTER 4 1048576

INVENTORY_MASTER 0 65536
INVENTORY_MASTER 1 65536
INVENTORY_MASTER 2 65536


Could you please clarify. I went through the explaination of Jurji, but want to understand in my case.

Thank you.

Badrinath

[jmodic]

Generaly speaking, it goes like this:

- When creatin segments, if you specify INITIAL extent size that is smaller than 1M, oracle creates it with the appropriate number of 64 K extents.
- If your INITIAL is beween 1M and 8M, oracle will create it using 1M extents
- If INITIAL is between 8M and 64M, it will use 8M extents
- and so on....

In your case, with INVENTORY_MASTER you specified INITIAL=71680, so oracle will start building the table using 64K extents. With ITEM_MASTER, you specified INITIAL=4382720, so Oracle immediately started using 1M extents.

[badrinathn]

Excellent- Thank you very much Jurji.

Badrinath

[adewri]

Originally posted by jmodic
In your case, with INVENTORY_MASTER you specified INITIAL=71680, so oracle will start building the table using 64K extents. With ITEM_MASTER, you specified INITIAL=4382720, so Oracle immediately started using 1M extents.

Exactly what i was doubting... I think im late now :(