Applies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.0 to 11.2.0.2 – Release: 11.2 to 11.2
Information in this document applies to any platform.
Purpose
This bulletin provides information about the database version 11.2 new feature called Deferred Segment Creation also referred to as Segment Creation On-Demand.
Scope and Application
Database Administrators and Developers.
11.2 Database New Feature Deferred Segment Creation
The Oracle Database 11.2 includes a new space allocation method. When you create a non-partitioned heap table, the table segment creation is deferred to the first row insert. This functionality is enabled by default with the initialization parameter DEFERRED_SEGMENT_CREATION set to TRUE.
The advantages of this new space allocation method are:
• A significant amount of disk space can be saved for applications that create hundreds or thousands of tables upon installation, many of which might never be populated.
• The application installation time is reduced, because the creation of a table is a data dictionary operation only.
When you insert the first row into the table, the segments are created for the base table, its LOB columns, and its indexes. During segment creation, cursors on the table are invalidated. These operations have a small additional impact on performance.
Note: With this new allocation method, it is essential that you do proper capacity planning so that the database has enough disk space to handle segment creation when tables are populated. For more details, see the Oracle Database Administrator’s Guide.
SQL> connect / as sysdba Connected. SQL> show parameter deferred NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SQL> connect imran/imran Connected. SQL> create table t1(no number); Table created. SQL> select * from user_segments; no rows selected SQL> insert into t1 values(10); 1 row created. SQL> commit; Commit complete. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- T1 SQL> select table_name,segment_created from user_tables; TABLE_NAME SEG ------------------------------ --- T1 YES
• This slide shows you how to verify the segment creation on demand. First you check the parameter DEFERRED_SEGMENT_CREATION . Then a table is created without segments, which you can verify by querying the USER_SEGMENTS data dictionary view. After the insert of a row, you query this view again, to see that the segment now exists.
• You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For non-partitioned tables, indexes and lobs, this column shows YES, if the segment is created. Note that 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.
• Another addition to the data dictionary is the SYS.SEG$ table that stores the storage parameters which you specified during the table or index creation.
You can control segment creation in two ways:
1) With the DEFERRED_SEGMENT_CREATION initialization parameter set to TRUE or FALSE. This parameter can be set in the initialization file. You can also control it via the ALTER SESSION or ALTER SYSTEM commands.
Example:
SQL> connect / as sysdba Connected. SQL> show parameter def NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean TRUE SQL> alter system set deferred_segment_creation=FALSE; System altered. SQL> show parameter def NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ deferred_segment_creation boolean FALSE SQL> connect imran/imran Connected. SQL> create table t2(no number); Table created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- T2 SQL> alter session set deferred_segment_creation=true; Session altered. SQL> create table t3( no number); Table created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- T2 SQL>
2) With the SEGMENT CREATION clause of the CREATE TABLE command:
SEGMENT CREATION DEFERRED: If specified, segment creation is deferred until the first row is inserted into the table. This is the default behavior for the Oracle Database 11gR2.
SEGMENT CREATION IMMEDIATE: If specified, segments are materialized during table creation. This is the default behavior in Oracle databases prior to the Oracle Database 11gR2.
Example
SQL> select * from user_segments; no rows selected SQL> CREATE TABLE SEG_TAB3(C1 number, C2 number) SEGMENT CREATION IMMEDIATE; Table created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- SEG_TAB3 SQL>CREATE TABLE SEG_TAB4(C1 number, C2 number) SEGMENT CREATION DEFERRED; Table created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- SEG_TAB3 SQL>
This clause takes precedence over the DEFERRED_SEGMENT_CREATION parameter. It is possible to force creation of segments for an already created table with the ALTER TABLE …MOVE command. However, it is not possible to directly control the deferred segment creation for dependant objects like indexes. They inherit this characteristic from their parent object, in this case, the table.
Restrictions and Exceptions :-
•To enable deferred segment creation, compatibility must be set to ’11.2.0′ or higher.
•In the Oracle Database 11gR2, deferred segment creation is restricted to non-partitioned tables and non-partitioned indexes. Deferred segment creation is not supported for partitioned indexes, bitmap join indexes, and domain indexes.
• IOTs and other special tables like clustered tables, global temporary tables, session-specific temporary tables, internal tables, typed tables, AQ tables, external tables are not supported. Tables owned by SYS, SYSTEM, PUBLIC, OUTLN, and XDB are also excluded.
• Segment creation on demand is not supported for tables created in dictionary-managed tablespaces and for clustered tables. If you try creating these tables, segments ARE created.
• Also please note: If you create a table with deferred segment creation on a locally managed tablespace, then it has no segments. If at a later time, you migrate the tablespace to dictionary-managed, then any attempt to create segments produces errors. In this case you must drop the table and recreate it.
Notes on Tables Without Segments :-
The following rules apply to a table whose segment has not yet been materialized:
If you create this table with CREATE TABLE … AS subquery, then if the source table has no rows, segment creation of the new table is deferred. If the source table has rows, then segment creation of the new table is not deferred.
If you specify ALTER TABLE … ALLOCATE EXTENT before the segment is materialized, then the segment is materialized and then an extent is allocated. However the ALLOCATE EXTENT clause in a DDL statement on any indexes of the table will return an error.
During an EXCHANGE of a partition or subpartition with a non-partitioned table without a segment, segments are materialized for the non-partitioned table automatically before proceeding with the EXCHANGE.
When you issue an ALTER TABLE … MOVE statement any storage properties you specify override the storage properties specified in the CREATE TABLE statement.
In a DDL statement on the table or its LOB columns or indexes, any specification of DEALLOCATE UNUSED is silently ignored.
ONLINE operations on indexes of a table without a segment will silently be disabled; that is, they will proceed OFFLINE.
Parallel DML operations on tables with no segments are disabled.
Additional Automatic Functionality:-
Additional enhancements in the Oracle Database 11gR2 (unrelated to the deferred segment creation) are implemented to save space: all UNUSABLE indexes and index partitions are created without a segment. This functionality is completely transparent for you. It is enabled by default with the COMPATIBILITY initialization parameter set to 11.2.0.0.
• Creating an index without a segment
CREATE INDEX test_i1 ON seg_test(c) UNUSABLE;
• Removing any allocated space for an index
ALTER INDEX test_i UNUSABLE;
• Creating the segment for an index:
ALTER INDEX test_i REBUILD;
The following slide shows some SQL commands which you might find useful for this new functionality:
• You can create an index without segment with the CREATE INDEX … UNUSABLE clause.
• You can remove any allocated space with the ALTER INDEX … UNUSABLE clause.
• And finally, you can create a segment for an index with the ALTER INDEX … REBUILD
Example
SQL> select * from user_segments ; no rows selected SQL> create table test_table(no number); Table created. SQL> insert into test_table values(10); 1 row created. SQL> commit; Commit complete. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- TEST_TABLE SQL> create index test_index on test_table(no) unusable; Index created. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- TEST_TABLE SQL> alter index test_index rebuild; Index altered. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- TEST_TABLE TEST_INDEX SQL> alter index test_index unusable; Index altered. SQL> select segment_name from user_segments; SEGMENT_NAME -------------------------------------------------------------------------------- TEST_TABLE SQL>
© 2009, www.oracledatabase12g.com. 版权所有.文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.
相关文章 | Related posts:
- Understanding Bootstrap Of Oracle Database
- How to Perform a Health Check on the Database [ID 122669.1]
- TECH: Database Block Checking Features
- 约束条件对于查询优化的作用
- Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g / 11g
- 11g: Scheduler Maintenance Tasks or Autotasks
- Collecting Diagnostic information for Oracle block corruption
- TROUBLESHOOTING: Possible Causes of Poor SQL Performance
- How to Format Corrupted Block Not Part of Any Segment
- THE GAINS AND PAINS OF NOLOGGING OPERATIONS
SEQUENCES NOT STARING WITH ’1′ WHEN USED IN INSERT
Applies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.1 – Release: 11.2
Information in this document applies to any platform.
Goal
Comments
——–
=== ODM Question ===
The sequences created as default ( start with 1 increment by 1) are not staring with ’1′ when used in insert query script, Instead they are starting with 2 or 4.
This behaviour of sequences is seen in version Oracle11gR2, not seen in earlier versions
drop sequence seqtest1;
CREATE TABLE test1(sno NUMBER, name VARCHAR(1000));
CREATE SEQUENCE seqtest1;
INSERT INTO test1 VALUES(SEQTEST1.NEXTVAL,’SAM’);
select * from test1;
SNO
———-
NAME
——————————————————————————
—
2
SAM
+ CREATE SEQUENCE seqtest2;
+ select SEQTEST2.nextval from dual;
NEXTVAL
———-
1
Solution
Comments
——–
=== ODM Answer ===
Bug 9246717 and Bug 9138058 raised and closed as duplicate of Unpublished bug 8493400.
After dev investigating the issue they concluded that is normal behavior given the default setup of 11.2.
By default deferred_segment_creation”=TRUE and during first time insert, the seq.nextval is called twice.
With deferred segment creation during insert, the operands are evaluated, then, when we attempt to insert the row, the segment is created and an ORA-14403 is signaled. Subsequently, the statement is internally retried and the operands are evaluated again.
You can set deferred_segment_creation=FALSE and this will not happen.
But you should not rely on continuity of the values from a sequence.Sequences are not guaranteed to generate all consecutive values starting with the ‘START WITH’ value. There are other conditions like rollback, retries due to cursor invalidation etc that can cause a break in the sequence.
HOW TO DROP A TABLE/INDEX WITH NO SEGMENTS AND NO TABLESPACE WHEN USING ORACLE MANAGED FILES
Applies to:
Oracle Server – Enterprise Edition – Version: 11.2.0.1 – Release: 11.2
Information in this document applies to any platform.
Goal
Drop a table that was created with no segments in a former tablespace when the database is using Oracle Managed Files (OMF)
Solution
Oracle 11.2 introduced a new default feature for table creation called DEFERRED SEGMENT CREATION
This feature allows non SYS users to create tables or indexes without the initial segment (or extents) being created … the segment (and extents) will be created upon the first insert
This is now the default behavior for CREATE TABLE but it can be disabled by specifying SEGMENT CREATION IMMEDIATE
Indexes created on tables with SEGMENT CREATION DEFERRED will also inherit this behavior
If the database is using Oracle Managed Files (OMF) the tablespace into which future segments will be created by tables (or indexes) using DEFERRED SEGMENT CREATION
can be dropped without INCLUDING CONTENTS without error
If a drop of a TABLE with no segments
but defined to have future segments created in a former Oracle Managed File tablespace is attempted … the following error occurs
ORA-00959: tablespace ‘<former tablespace name>’ does not exist
SOLUTION: Drop the table with the PURGE option
DROP TABLE <table name> PURGE;
If a drop of a INDEX with no segments
but defined to have future segments created in a former Oracle Managed File tablespace
and the indexes table is defined to have future segments in an existing tablespace
is attempted … the following error occurs
ORA-00959: tablespace ‘<former tablespace name>’ does not exist
SOLUTION: Drop the table for the associated index
NOTE: This is not a problem if OMF is not used as a ORA-01549: tablespace not empty, use INCLUDING CONTENTS option will occur during the drop of the tablespace
NOTE 2:Bug 9343215 states that this is expected behavior
CASE STUDIES
Create a database that uses Oracle Managed Files (OMF)
CASE STUDY #1 TABLE SEGMENT CREATION DEFERRED
sqlplus / as sysdba
– SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 2 12:46:51 2010
– Copyright (c) 1982, 2009, Oracle. All rights reserved.
– Connected to:
– Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
– With the Partitioning, Automatic Storage Management, OLAP, Data Mining
– and Real Application Testing options
drop user test cascade;
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
– CREATE AN ORACLE MANAGED FILE (OMF) TABLESPACE
create tablespace test;
– Tablespace created.
– CREATE A TABLE IN THE OMF TABLESPACE
create table test_tab (col1 number) tablespace test;
– Table created.
– DEMONSTRATE THAT DEFERRED SEGMENT CREATION SUCCEEDED
SELECT segment_created FROM USER_TABLES WHERE TABLE_NAME = ‘TEST_TAB’;
– SEG
– —
– NO
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM USER_SEGMENTS;
– no rows selected
– DROP THE OMF TABLESPACE
drop tablespace test;
– Tablespace dropped.
– ATTEMPT TO DROP THE TABLE
drop table test_tab;
– drop table test_tab
– *
– ERROR at line 1:
– ORA-00959: tablespace ‘TEST’ does not exist
– ATTEMPT TO INSERT ROWS INTO THE TABLE … THUS FORCING THE CREATION OF THE SEGMENT / EXTENTS
insert into test_tab values (1);
– insert into test_tab values (1)
– *
– ERROR at line 1:
– ORA-00959: tablespace ‘TEST’ does not exist
– IMPLEMENT THE SOLUTION FOR THE DROP
drop table test_tab purge;
– Table dropped.
CASE STUDY #2 INDEX SEGMENT CREATION DEFERRED CASE
sqlplus / as sysdba
– SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 2 12:46:51 2010
– Copyright (c) 1982, 2009, Oracle. All rights reserved.
– Connected to:
– Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
– With the Partitioning, Automatic Storage Management, OLAP, Data Mining
– and Real Application Testing options
drop user test cascade;
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
– CREATE AN ORACLE MANAGED FILE (OMF) TABLESPACE
create tablespace test;
– Tablespace created.
– CREATE A TABLE IN AN OMF TABLESPACE THAT WILL NOT BE DROPPED
create table test_tab (col1 number) tablespace users;
– Table created.
– DEMONSTRATE THAT DEFERRED SEGMENT CREATION SUCCEEDED FOR THE TABLE
SELECT segment_created FROM USER_TABLES WHERE TABLE_NAME = ‘TEST_TAB’;
– SEG
– —
– NO
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM USER_SEGMENTS;
– no rows selected
– CREATE AN INDEX ON THE TABLE AN THE OMF TABLESPACE THAT WILL BE DROPPED
create index test_tab_idx on test_tab(col1) tablespace test;
– Index created.
– DEMONSTRATE THAT DEFERRED SEGMENT CREATION SUCCEEDED FOR THE INDEX
SELECT segment_created FROM USER_INDEXES WHERE INDEX_NAME = ‘TEST_TAB_IDX’;
– SEG
– —
– NO
SELECT SEGMENT_NAME, SEGMENT_TYPE FROM USER_SEGMENTS;
– no rows selected
– DROP THE TABLESPACE THAT THE FUTURE SEGMENT FOR OUR INDEX WOULD BE CREATED IN
drop tablespace test;
– Tablespace dropped.
– ATTEMPT TO INSERT INTO OUR TEST TABLE
insert into test_tab values (1);
– insert into test_tab values (1)
– *
– ERROR at line 1:
– ORA-00959: tablespace ‘TEST’ does not exist
– The test fails because there is no tablespace into which TEST_TAB_IDX can create its segment
– ATTEMPT TO DROP THE INDEX
drop index test_tab_idx;
– drop index test_tab_idx
– *
– ERROR at line 1:
– ORA-00959: tablespace ‘TEST’ does not exist
– DROP THE BASE TABLE FOR THE INDEX
drop table test_tab;
– Table dropped.
– DEMONSTRATE THAT THE INDEX IS DROPPED
SELECT count(*) FROM USER_INDEXES WHERE INDEX_NAME = ‘TEST_TAB_IDX’;
– no rows selected
CASE STUDY #3 NON ORACLE MANAGED FILES (OMF) CASE
sqlplus / as sysdba
– SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 2 12:46:51 2010
– Copyright (c) 1982, 2009, Oracle. All rights reserved.
– Connected to:
– Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
– With the Partitioning, Automatic Storage Management, OLAP, Data Mining
– and Real Application Testing options
drop user test cascade;
create user test identified by test;
grant dba to test;
alter user test default tablespace users;
connect test/test;
– CREATE A NON OMF TABLESPACE
create tablespace test datafile ‘/ora/ora_11.2.0.1/ora11201/test.dbf’ size 100m;
– Tablespace created.
– CREATE A TABLE IN THE NON OMF TABLESPACE
create table test (col1 number) tablespace test;
– Table created.
– ATTEMPT TO DROP THE NON OMF TABLESPACE
drop tablespace test;
– drop tablespace test
– *
– ERROR at line 1:
– ORA-01549: tablespace not empty, use INCLUDING CONTENTS option
– DROP THE NON OMF TABLESPACE
drop tablespace test including contents and datafiles;
– Tablespace dropped.