puzzle?

[dba_admin]

The user CCDM has a table called 'ACTIONDEF.' That table has only one extent. Why the extent does not equal to the initial extent?

SQL> select bytes from dba_extents where segment_name='ACTIONDEF' and owner='CCDM';

BYTES
----------
163840

1 row selected.

SQL> select INITIAL_EXTENT from dba_tables where table_name='ACTIONDEF' and owner='CCDM';

INITIAL_EXTENT
--------------
131072

1 row selected.

SQL>

[pando]

because you are using LMT and you have extent sizes of 128K however when you created the object you assigned 163840 bytes as initial extent

[dba_admin]

I didn't get it.

1. This is not a locally managed tablespace.

2. The init ext was set as 131072.

SQL> select INITIAL_EXTENT from dba_tables where table_name='ACTIONDEF' and owner='CCDM';

INITIAL_EXTENT
--------------
131072

Why the extent size in dba_extents shows 163840?

[pando]

then you are using minimum extent clause in tablespace

SQL> create tablespace dmtusers01
         datafile 'D:\ORACLE\ORADATA\DEV\DMTUSERS01.DBF' size 1M
         minimum extent 160K
         default storage(
         initial 128K
         next 128K
         minextents 1
         maxextents 1024
         pctincrease 0);

Tablespace created.

SQL> create table lscemp tablespace dmtusers01  as select * from emp where 1 = 0;

Table created.

SQL> select table_name, initial_extent from user_tables where table_name='LSCEMP';

TABLE_NAME                     INITIAL_EXTENT
------------------------------ --------------
LSCEMP                                 131072

SQL> select segment_name, bytes from user_extents where segment_name='LSCEMP';

SEGMENT_NAME                  BYTES
------------------------ ----------
LSCEMP                       163840

it's not a puzzle it's how you created your tablespace and Oracle rounds up the extent size

[marist89]

Originally posted by pando

it's not a puzzle

but an interesting concept to get somebody to look at the question...

[dba_admin]

Very Cool! Thank you.

[dba_admin]

I agree with your test case. However, when I test it from my db, it shows that the minimum extent is set as 0. So, there should have no "round up." Can you help to explain this? Thank you.

1 Create tablespace test_DATA_01
2 DataFile
3 '/export/home/u01/oradata/koya/users03x.dbf'
4 Size 1m AutoExtend Off
5 Default Storage (
6 Initial 131072
7 Next 131072
8 Minextents 1
9 Maxextents 1000
10 Pctincrease 0
11 )
12 ONLINE
13* PERMANENT
SQL> /

Tablespace created.

SQL> select MIN_EXTLEN "minimum extent" from dba_tablespaces where tablespace_name='TEST_DATA_01';

minimum extent
--------------
0

1 row selected.

SQL> create table testq tablespace test_data_01 as select * from emp where 1=0;

Table created.

SQL> select table_name, initial_extent from dba_tables where table_name='TESTQ';

TABLE_NAME INITIAL_EXTENT
------------- --------------
TESTQ 131072

1 row selected.

SQL> select segment_name, bytes from dba_extents where segment_name='TESTQ';

SEGMENT_NAME BYTES
--------------- ----------
TESTQ 163840

1 row selected.

SQL>
SQL>

[svk]

What is your database block size ? 32K ?

[pando]

Originally posted by dba_admin

1 Create tablespace test_DATA_01
2 DataFile
3 '/export/home/u01/oradata/koya/users03x.dbf'
4 Size 1m AutoExtend Off
5 Default Storage (
6 Initial 131072
7 Next 131072
8 Minextents 1
9 Maxextents 1000
10 Pctincrease 0
11 )
12 ONLINE
13* PERMANENT
SQL> /

Tablespace created.

we have a reading problem here... look my create tablespace syntaxis and yours. Where is your minimum extent clause?

[dba_admin]

I do NOT want to use the "minimum extent" clause. In my original question, the tablespace does NOT have "minimum extent" specified. I just try to duplicate the same environment.

So, if "minimum extent" is not specified, there should NOT have "round up." Why Oracle still made the "round up"?


For SVK:
SQL> show parameter db_block_size

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
db_block_size integer 8192