Sunday, November 14, 2010

Deferred segment creation and dbms_space_admin.materialize_deferred_segments


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> show parameter compatible

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
compatible string 11.2.0
SQL> show parameter deferred

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
deferred_segment_creation boolean TRUE




SQL> create table t1 (id number);

Table created.

SQL> create table t2 (id number);

Table created.

SQL> create index t1_pk on t1(id);

Index created.
SQL> create table t3 (doctype clob) lob(doctype) store as securefile;

Table created.

SQL> select table_name,segment_created from user_tables;

TABLE_NAME SEG
------------------------------ ---
T1 NO
T2 NO
T3 NO

SQL> select segment_name from user_segments;

no rows selected





SQL> begin
2 dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
3 end;
4 /
dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'DBMS_SPACE_ADMIN' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

SQL> conn / as sysdba
Connected.
SQL> begin
2 dbms_space_admin.materialize_deferred_segments(schema_name=>'DONGHUA');
3 end;
4 /

PL/SQL procedure successfully completed.




SQL> conn donghua/donghua
Connected.
SQL> select table_name,segment_created from user_tables;

TABLE_NAME SEG
------------------------------ ---
T1 YES
T2 YES
T3 YES

SQL> select segment_name from user_segments;

SEGMENT_NAME
--------------------------------------------------------------------------------
T1
T2
T3
T1_PK
SYS_IL0000021560C00001$$
SYS_LOB0000021560C00001$$

6 rows selected.

1 comment:

  1. I couldn't understand the purpose of MATERIALIZE_DEFERRED_SEGMENTS procedure in the Oracle documentation. Your example just makes it for me.
    Thanks a lot.

    ReplyDelete