Oraclue

Oracle internals, debugging and undocumented features

Deffered Segment Creation

Oracle 11g release 2 introduced new initialization  parameter DEFERRED_SEGMENT_CREATION .

This parameter specifies the semantics of deferred segment creation. If set to true ( which is DEFAULT ) then segments for non-partitioned tables and their dependent objects (LOBs, indexes) will not be created until the first row is inserted into the table.

According to Oracle documentation  when you create a table with deferred segment creation (the default), the new table appears in the *_TABLES views, but no entry for it appears in the *_SEGMENTS views until you insert the first row.
There is a new SEGMENT_CREATED column in *_TABLES, *_INDEXES, and *_LOBS that can be used to verify deferred segment creation.

However I do not trust any new feature until I test it. I  will use simple example to test it.

I am running Oracle 11g version 2

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production
and connected as SYS user

SQL> show user
USER is “SYS”

Check that tablespace is LOCALLY managed:

SQL> select  TABLESPACE_NAME ,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN SEGMEN
—————————— ———- ——
SYSTEM                         LOCAL      MANUAL
SYSAUX                         LOCAL      AUTO
UNDOTBS1                       LOCAL      MANUAL
TEMP                           LOCAL      MANUAL
USERS                          LOCAL      AUTO

and I will create table test with only one column and stored in tablespace users:

SQL> create table test ( a number) tablespace users;

Table created.

Now according to Oracle documentation this table should be listed in _TABLES views but not in _SEGMENTS views!

SQL> SELECT segment_created
FROM user_tables
WHERE table_name = ‘TEST’;

SEG

YES

but segment_created= YES?

and again

SQL> SELECT segment_name
FROM user_segments
WHERE segment_name = ‘TEST’;

SEGMENT_NAME
————————-
TEST

table shows up in user_segment views. So  SYS user is exception.

For regular users:

SQL> show user
USER is “MILADIN”
SQL> create table test ( a number) tablespace users;

Table created.

SQL> SELECT segment_created
FROM user_tables
WHERE table_name = ‘TEST’;  2    3

SEG

NO

SQL>  SELECT segment_name
FROM user_segments
WHERE segment_name = ‘TEST’;   2    3

no rows selected

SQL> insert into test values (1);

1 row created.

SQL> SELECT segment_created
FROM user_tables
WHERE table_name = ‘TEST’;  2    3

SEG

YES

SQL> SELECT segment_name
FROM user_segments
WHERE segment_name = ‘TEST’;  2    3

SEGMENT_NAME
——————————————————————————–
TEST

SQL>

works as expected.

Now let me  test ALTER TABLE MOVE command.

SQL> drop table test;

Table dropped.

SQL> create table test ( a number) tablespace users;

Table created.

SQL> SELECT segment_created
FROM user_tables
WHERE table_name = ‘TEST’;   2    3

SEG

NO

SQL> alter table test move tablespace users;

Table altered.

SQL> SELECT segment_created
FROM user_tables
WHERE table_name = ‘TEST’;   2    3

SEG

YES

SQL> SELECT segment_name
FROM user_segments
WHERE segment_name = ‘TEST’;  2    3

SEGMENT_NAME
——————————————————————————–
TEST

Using ALTER TABLE MOVE on table with no segment will determine creation of new segment although DEFFERED_SEGMENT_CREATION=TRUE.

Well Oracle notes  that when you issue an ALTER TABLE … MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.

Advertisements

2 responses to “Deffered Segment Creation

  1. Noons October 13, 2009 at 9:15 pm

    The problem of course is that Oracle is not following their own advice.
    If you specify the deferred segment creation for the move of the table, although the manual says it can be done, it won’t be.
    In other words: the storage properties of the MOVE won’t override the table creation.
    Check Nick Gasparotto’s blog for this same problem, a month ago or so.

  2. oraclue October 13, 2009 at 9:23 pm

    Thanks Nuno.I will definitelly check Nick’s blog.I saw similar discussion on OTN I believe.And of course new Oracle featuers are always fun and not predictible. 🙂

    Miladin

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: