//
you're reading...
Administration, SQL

Deferred Segment Creation

Let’s have a look on the following code:

dbadmin@O11R2>SELECT * FROM v$version;
BANNER
----------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE   11.2.0.1.0    Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production

dbadmin@O11R2>CREATE USER tarzan IDENTIFIED BY jungle;
User created.

dbadmin@O11R2>GRANT CONNECT, CREATE TABLE TO tarzan;
Grant succeeded.

dbadmin@O11R2>SELECT username, default_tablespace, temporary_tablespace
              FROM dba_users
              WHERE username='TARZAN';

USERNAME     DEFAULT_TABLESPACE       TEMPORARY_TABLESPACE
------------ ------------------------ --------------------------
TARZAN       USERS                    TEMP

dbadmin@O11R2>CONN tarzan/jungle;
Connected.

tarzan@O11R2>SELECT tablespace_name, bytes, max_bytes FROM user_ts_quotas;
no rows selected

tarzan@O11R2>CREATE TABLE t (r1 NUMBER);
Table created.

Since user A has no quota on users tablespace, how can he create a object? Hmmm

tarzan@O11R2>SELECT table_name, tablespace_name, segment_created
             FROM user_tables;
TABLE_NAME                     TABLESPACE_NAME                SEG
------------------------------ ------------------------------ ---
T                              USERS                          NO

When we issue CREATE TABLE command, Oracle allocates a segment to the table. Isn’t it?

tarzan@O11R2>INSERT INTO t VALUES(20);

INSERT INTO t VALUES(20)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Confused…

From 11g release 2, Oracle has introduced a new feature called Deferred Segment Creation.

When we create heap-organized tables, database delay the table segment creation until the first row is inserted. That means only metadata is created when you create a table.

You can change the default behavior of the DEFERRED_SEGMENT_CREATION initialization parameter by using ALTER SESSION, ALTER SYSTEM.

dbadmin@O11R2>SHOW PARAMETER DEFERRED_SEGMENT_CREATION
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
deferred_segment_creation            boolean     TRUE

dbadmin@O11R2>ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;
System altered.

dbadmin@O11R2>SHOW PARAMETER DEFERRED
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
deferred_segment_creation            boolean     FALSE

dbadmin@O11R2>CONN tarzan/jungle
Connected.

tarzan@O11R2>DROP TABLE t;
Table dropped.

tarzan@O11R2>CREATE TABLE t(r1 NUMBER);
CREATE TABLE t(r1 NUMBER)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

You can also use two new clauses of CREATE TABLE statement to change the default behavior of the DEFERRED_SEGMENT_CREATION parameter.

  • SEGMENT CREATION IMMEDIATE
  • SEGMENT CREATION DEFERRED
dbadmin@O11R2>SHOW PARAMETER DEFERRED
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------
deferred_segment_creation            boolean     TRUE

dbadmin@O11R2>CONN tarzan/jungle
Connected.

tarzan@O11R2>CREATE TABLE t(r1 NUMBER)
             SEGMENT CREATION IMMEDIATE;
CREATE TABLE t(r1 NUMBER)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

tarzan@O11R2>CONN dbadmin
Enter password:
Connected.

dbadmin@O11R2>ALTER SYSTEM SET DEFERRED_SEGMENT_CREATION=FALSE;
System altered.

dbadmin@O11R2>CONN tarzan/jungle
Connected.

tarzan@O11R2>CREATE TABLE t(r1 NUMBER)
CREATE TABLE t(r1 NUMBER)
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

tarzan@O11R2>CREATE TABLE t(r1 NUMBER)
             SEGMENT CREATION DEFERRED;
Table created.

Some important information

Understand Deferred Segment Creation

Restrictions on Deferred Segment Creation

Happy Learning… 🙂

Advertisements

Discussion

2 thoughts on “Deferred Segment Creation

  1. go ahead…it’s really usefull..i regulary..check

    Posted by Alok Kumar | August 23, 2012, 23:31

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: