Deferred Segment Creation as of 11.2.0.2

One year ago I wrote a post entitled Deferred Segment Creation. If you read the comments related to it you can see that several people were concerned by the fact that it was not possible to easily get rid of segments associated to empty tables. That was with version 11.2.0.1. Now we have version 11.2.0.2 and this specific problem has been fixed.

As of 11.2.0.2 DBMS_SPACE_ADMIN, and not DBMS_SPACE as written in the New Feature Guide, provides the following procedures:

  • MATERIALIZE_DEFERRED_SEGMENTS
  • DROP_EMPTY_SEGMENTS

With them it is possible to materialize/drop the segments of the empty tables and their associated objects. Depending on the specified parameters, they can process all database segments, all segments owned by a specific schema, all segments associated to a specific table, or all segments associated to a specific partition.

Let’s have a look to an example:

  • Create a partitioned table with a primary key and a LOB column
SQL> CREATE TABLE t (
  2    id NUMBER,
  3    c CLOB,
  4    CONSTRAINT t_pk PRIMARY KEY (id) USING INDEX LOCAL
  5  )
  6  SEGMENT CREATION DEFERRED
  7  PARTITION BY HASH(id) PARTITIONS 4;
  • Show that no segment is available
SQL> SELECT segment_name, segment_type, bytes, extents
  2  FROM user_segments
  3  WHERE segment_name IN ('T','T_PK')
  4  OR segment_name IN (SELECT segment_name
  5                      FROM user_lobs
  6                      WHERE table_name = 'T')
  7  ORDER BY 1,2;

no rows selected
  • Materialize the segments
SQL> BEGIN
  2    dbms_space_admin.materialize_deferred_segments(
  3      schema_name => 'CHA',
  4      table_name  => 'T'
  5    );
  6  END;
  7  /
  • Show that the segments are now available
SQL> SELECT segment_name, segment_type, bytes, extents
  2  FROM user_segments
  3  WHERE segment_name IN ('T','T_PK')
  4  OR segment_name IN (SELECT segment_name
  5                      FROM user_lobs
  6                      WHERE table_name = 'T')
  7  ORDER BY 1,2;

SEGMENT_NAME                   SEGMENT_TYPE              BYTES    EXTENTS
------------------------------ -------------------- ---------- ----------
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
SYS_LOB0000103611C00002$$      LOB PARTITION           8388608          1
T                              TABLE PARTITION         8388608          1
T                              TABLE PARTITION         8388608          1
T                              TABLE PARTITION         8388608          1
T                              TABLE PARTITION         8388608          1
T_PK                           INDEX PARTITION           65536          1
T_PK                           INDEX PARTITION           65536          1
T_PK                           INDEX PARTITION           65536          1
T_PK                           INDEX PARTITION           65536          1
  • Get rid of the segments (this is possible because the table is empty)
SQL> BEGIN
  2    dbms_space_admin.drop_empty_segments(
  3      schema_name => 'CHA',
  4      table_name  => 'T'
  5    );
  6  END;
  7  /
  • Show that no segment is available
SQL> SELECT segment_name, segment_type, bytes, extents
  2  FROM user_segments
  3  WHERE segment_name IN ('T','T_PK')
  4  OR segment_name IN (SELECT segment_name
  5                      FROM user_lobs
  6                      WHERE table_name = 'T')
  7  ORDER BY 1,2;

no rows selected

The attentive reader might have noticed two additional new features available since 11.2.0.2. The first one is that deferred segment creation is also supported for partitioned table. The second one is that the initial extents associated to partitioned tables and partitioned LOBs (but not to partitioned indexes) have a new default size of 8MB. Note that this new default is only used for segments created in an EXTENT MANAGEMENT LOCAL AUTOALLOCATE tablespace.

7 Comments

  1. October 5, 2010    

    Yes, that’s rather nice, adding to that the TRUNCATE within DROP ALL STORAGE option to deallocate *ALL* the segments to get a segment free table.

    Nicolas.

  2. October 5, 2010    

    Hi Nicolas

    You are right, I forgot to mention the enhanced TRUNCATE syntax. Thank you for pointing it out.

    Cheers,
    Chris

  3. bpolarsk bpolarsk
    October 8, 2010    

    Does this ‘dbms_space_admin.drop_empty_segment’ works for partitions flagged missing after a restore skip tablespaces ? Up to now, we were forced to exchange these with tables created specially for that.

    • October 11, 2010    

      Hi

      I didn’t test it and, therefore, I cannot be sure at all… But, honestly, I would be surprised that it works in such a situation.

      Cheers,
      Chris

  4. Smee Smee
    December 10, 2010    

    Hi,
    does this (deferred_segment_creation)parameter can define in the response file for silent install?

    Cheers.

    • December 12, 2010    

      Hi Smee

      I nener did that… But as for any other parameter you should be able to specify it through the DBCA parameter named initParams.

      Cheers,
      Chris

  5. Smee Smee
    December 13, 2010    

    Hi Christian,

    tnx for your quick response. I tried to put this parameter into the response file, but this is an advanced parameter and I failed. I try something else.

    Thanks once more.

    Best regards,
    Smee

No Pings Yet

  1. October 2010 Blogroll Report | AskDba.org Weblog on December 28, 2011 at 07:22
  2. Deferred Segement Creation PARALLEL | Spot On Oracle on August 7, 2014 at 06:53

Leave a Reply

Your email address will not be published. Required fields are marked *


Time limit is exhausted. Please reload CAPTCHA.