Miladin Modrakovic's Blog: Oraclue

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.