"Null" value in next_extent dba_segments !!

[Chris Fung]

Hi there,

I just found that some of the tables in our production database with "Next_Extent" have no values i.e. "Null".

Could anyone tell me what does it mean ? How this could be happened ??

Cheers,

Chris,

[Steve Steel · Accepted solution]

Hi


See

http://www.csee.umbc.edu/help/oracle8/server.815/a67774/migdata.htm

ALL_TABLESPACES

DBA_TABLESPACES

USER_TABLESPACES


NEXT_EXTENT

PCT_INCREASE
These columns return a null if the tablespace is locally managed and uses the AUTOALLOCATE option, because the system chooses the extent sizes, and the algorithm cannot be explained in terms of NEXT_EXTENT and PCT_INCREASE.


steve Steel

[Yogeeraj_1]

hi,

Partitioned tables show this behaviour!

see below:
============================================================
yd@YDDB.MU> CREATE TABLE table2003
( x int,
y int,
z DATE
)
PARTITION BY RANGE (z)
( PARTITION tab_2003_m1 VALUES
LESS THAN(to_date('01-feb-2003','dd-mon-yyyy')),
PARTITION tab_2003_m2 VALUES
LESS THAN(to_date('01-mar-2003','dd-mon-yyyy')),
PARTITION tab_2003_m3 VALUES
LESS THAN(to_date('01-apr-2003','dd-mon-yyyy')),
PARTITION tab_2003_m4 VALUES
LESS THAN(to_date('01-may-2003','dd-mon-yyyy'))
)
/

Table created.

Elapsed: 00:00:00.16
yd@YDDB.MU> select table_name, next_extent
from user_Tables
2 3 where next_extent is null;

TABLE_NAME NEXT_EXTENT
______________________________ ___________
TABLE2003

Elapsed: 00:00:00.02
yd@YDDB.MU> CREATE TABLE table2003b
2 ( x int,
3 y int,
4 z DATE
5* )

Table created.

Elapsed: 00:00:00.03
yd@YDDB.MU> select table_name, next_extent
2 from user_Tables
3 where next_extent is null;

TABLE_NAME NEXT_EXTENT
______________________________ ___________
TABLE2003

Elapsed: 00:00:00.00
yd@YDDB.MU> c/null/not null
3* where next_extent is not null
yd@YDDB.MU> /

TABLE_NAME NEXT_EXTENT
______________________________ ___________
DEMO 65536
PLSQL_PROFILER_DATA 65536
PLSQL_PROFILER_RUNS 65536
PLSQL_PROFILER_UNITS 65536
T 65536
T1 65536
T2 65536
T4 65536
TABLE2003B 65536

9 rows selected.

Elapsed: 00:00:00.02
yd@YDDB.MU>
============================================================


hope this helps!
Yogeeraj

[Indira Aramandla]

Hi chris,

The reason for the tables showing null fro next_extent is those tables are created in Locally Managed Tablespaces. For locally managed tablespaces, the storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid as the extents are managed locally

You can verify if the tablespace is locally managed

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT FROM DBA_TABLESPACES

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
LOCAL_TBS LOCAL

From oracle 8i onwards, when you create a tablespace you can choose to use the dictionary-managed option where you define the storage clause (NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS), or use the locally managed options.

A Locally Managed Tablespace is a tablespace that manages its own extents maintaining a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. Space management is much simpler and more efficient in locally managed tablespaces.

You can specify autoallocate or uniform extent sizes in locally managed tablespaces.

* AUTOALLOCATE (system managed) specifies that the tablespace is system managed. Users cannot specify an extent size.

* UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes. The default SIZE is 1 megabyte.

If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default.

Note: there are few restrictions:- Temporary tablespace cannot be created as locally managed tablespace. You should stick with dictionary managed.