Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

"Null" value in next_extent dba_segments !!

SOLVED
Go to solution
Chris Fung
Frequent Advisor

"Null" value in next_extent dba_segments !!

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,
3 REPLIES
Steve Steel
Honored Contributor
Solution

Re: "Null" value in next_extent dba_segments !!

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


If you want truly to understand something, try to change it. (Kurt Lewin)
Yogeeraj_1
Honored Contributor

Re: "Null" value in next_extent dba_segments !!

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Indira Aramandla
Honored Contributor

Re: "Null" value in next_extent dba_segments !!

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.








Never give up, Keep Trying