Oracle Table Space management methods segment and extent

• A Permanent tablespace contains persistent schema objects. Objects in permanent tablespaces is stored in data files.

• an undo tablespace are a type of permanent tablespace used by Oracle Database to manage undo data if You is running your database in automatic undo management mode. Oracle strongly recommends that's automatic undo management mode rather than using rollback segments for Undo.

• A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces is stored in temp files.

1, Extent_management_clause

The lets specify how the extents of the tablespace would be extent_management_clause managed.

Note:

After you has specified extent management with this clause, you can change extent management only by migrating the tables Pace.

• autoallocate  specifies that the tablespace is System managed. Users cannot specify an extent size. You cannot specify  autoallocate  for a temporary tablespace.

• UNIFORM  specifies that the tablespace are managed with Uniform extents of  SIZE  bytes. The default  SIZE  is 1 megabyte. All extents of temporary tablespaces was of uniform size, so this keyword was optional for a temporary tablespace. However, must specify , UNIFORM  in order to specify SIZE . You cannot specify , UNIFORM  for an undo tablespace.

If You don't specify AUTOALLOCATE or, then UNIFORM the UNIFORM default was for temporary tablespaces AUTOALLOCATE Types of tablespaces.

If you don't specify extent_management_clause the, then Oracle Database interprets the clause and the to MINIMUM EXTENT DEFAULT storage_clause determine ex Tent management.

The DICTIONARY keyword is deprecated. It is still supported for backward compatibility. However, Oracle recommends that you create locally managed tablespaces. Locally managed tablespaces is much more efficiently managed than dictionary-managed tablespaces. The creation of new Dictionary-managed tablespaces is scheduled for desupport.

2, Logging_clause

Specify the default logging attributes of all tables, indexes, materialized views, materialized view logs, and partitions Within the tablespace. is the LOGGING default. This clause isn't valid for a temporary or undo tablespace.

The Tablespace-level logging attribute can be overridden by logging specifications at the table, index, materialized view, Materialized view log, and partition levels.

See Also:

Logging_clauseFor a full description of this clause

Force LOGGING

Use this clause to put the tablespace into FORCE LOGGING mode.  Oracle Database would log all changes-objects in the tablespace except changes to temporary segments, overriding any NOLOGGINGsetting for individual objects. The database must is open and in READ WRITE mode.

This setting does not exclude the NOLOGGING attribute. You can specify both and FORCE LOGGING NOLOGGING . The is the NOLOGGING default logging mode for objects subsequently created in the Tablespace, but the database Ignor Es this default as long as the tablespace or the database is in FORCE LOGGING mode. If you subsequently take the tablespace out FORCE LOGGING of mode, then the NOLOGGING default is once again enforced.

Note:

FORCE LOGGING mode can has performance effects.You cannot specify FORCE For an LOGGING undo or temporary tablespace.

3, Segment_management_clause

segment_management_clausethe is relevant only for permanent, locally managed tablespaces. It lets specify whether Oracle Database should track the used and free space in the segments in the tablespace using F REE lists or bitmaps. This clause isn't valid for a temporary tablespace.

AUTO Specify if want the database to manage the free space of the segments in the AUTO tablespace using a bitmap. If you specify AUTO , then the database ignores any specification for PCTUSED , FREELIST , and in FREELIST GROUPS subsequent storage Specifications for objects in this tablespace. This setting are called Automatic segment-space Management and is the default.

MANUAL Specify MANUAL if want the database to manage the free space of segments in the tablespace using free lists. Oracle strongly recommends that's don't use the This setting and so you create tablespaces with automatic segment-space m Anagement.

To determine the segment management of an existing tablespace, query the SEGMENT_SPACE_MANAGEMENT column of the DBA_TABLESPACES or USER_TABLESPACES data dictionary View.

Notes:

If you specify AUTOSegment management, then:

• If You set the extent management LOCAL UNIFORM to and then the must ensure that each extent contains at least 5 database blocks.

• If you set extent management to LOCAL AUTOALLOCATE , and if the database block size was 16K or greater, then Oracle manages segment Space by creating extents with a minimum size of 5 blocks rounded up to 64K.

Restrictions on Automatic segment-space Management this clause are subject to the following restrictions:

• You can specify this clause only for a permanent, locally managed tablespace.

• You cannot specify this clause for the SYSTEM tablespace

=======================================================

extent--Minimum Space allocation unit--tablespace management
Block-min I/O Unit--segment Management

Create tablespace James
DataFile '/export/home/oracle/oradata/james.dbf '
Size 100M--Initial file size
Autoextend on-Auto grow-default is off
Next 10M-Each auto-grow size
MaxSize 2048M--Maximum file size
Extent management local--table space uses the local surface space management--by default, Local-system is not specified.
Uniform size 128k--uniform set extent each allocation is uniformly sized to 128k (16 blocks per allocation if db_block_size=8k)
--If the size is not specified, it is 1M, which is 1024/8 blocks
--autoallocate setting extent size is automatically assigned by the system
--no matter how much the system is allocated, the uniform size is 64k (the size of the marker bit in bitmap).
--autoallocate is displayed as Sysrtem in Allocation_type in dba_extents
Segment Space management auto; --The default is auto
There are two types of block management in--segment: MSSM (Manual Segment Space Management),
ASSM (Auto systemt Space Management)
Only Pctfree parameters work when--auto mode
The freelist,pctfree,pctused parameter works when--manual mode.

There are two ways of tablespace management: (Management extent)
1 Data Dictionary Space management dictionary managed tablespace
By managing two major data dictionary tables, uet$ (used EXtends) and fet$ (free EXtends) implement
Eliminated after 9i
Cons: 1 Concurrent access contention
2 generate a lot of redo undo
3 Space debris
2 Local surface space management local managed Tablespace
Bitmap Management
Data file header added to bitmap area
Extent Management Local

Specific space allocation method:
1 autoallocate----Allocation_type=system
2 Uniform----allocation_type=uniform
Allocation_type This value has 3 options:
1, System: Once this value is set, Next_extent will be empty, only the extents value. The value is the default value. The minimum of this option is 64K
2, User: Once this value is set, we are allowed to control the next_extent. There are only two cases where the users: first, the TS is a data dictionary management, and the other is that TS is transferred from data dictionary management to local (with dbms_space_admin.tablespace_migrate_to_local)
3, Uniform: will be marked all the size of the extent will be consistent, the temp table space can only be used in this way; the size of the extent of the above two cases will be inconsistent; The default value in uniform is 1M

There are two ways to manage Segment: (Manage block)
1 MSSM (Manual Segment Space Management)
2 ASSM (Auto systemt Space Management)

1 MSSM (Manual Segment Space Management)
Manage blocks by assigning a free list (freelist) to the segment header in segment
Manage how blocks get in and out of freelist with two parameters Pctfree pctused
The Pctfree value indicates how much of the block space is reserved for updating
The pctused value indicates that the block will rejoin the freelist if it is below this value.

View settings for parameters such as freelist,pctfree,pctused via Dba_tables,dba_indexes

2 ASSM (Auto systemt Space Management)
Manage blocks by assigning bitmaps (bitmap) to the segment header in segment
No longer need freelist
No need to pctused, because there is no need to remove the block from the freelist.
As mentioned earlier in the data file Block1-2 is the data header file, Block3-8 is the extent bitmap.
Block9-10 is a level and level two bitmap of the ASSM block.
ASSM supports up to three levels, but it is generally very difficult to see the use of level three directories

(Segment Space Management: A section space management method in a locally managed tablespace can be specified as:

Automatic: Oracle DB uses bitmaps to manage free space in the segment. Bitmaps describe the state of each block of data in a segment, which is related to the amount of space in a block that can be inserted into a row. When there is more or less free space in the data block, the new state of the block is reflected in the bitmap. By using bitmaps, the Oracle DB You can increase the degree of automation in managing free space. Therefore, this type of space management is called "Automatic segment Space Management (ASSM)".

Manual: This method specifies that you want to use an idle list to manage free space in a segment. A free list is a list of blocks of data that have space to insert rows. Because of this way of managing the space, you need to specify and optimize the pctused for the schema objects created in the table space. Freelists and freelist groups store parameters, so this approach is called "Manual segment Space Management". This method is supported for backwards compatibility and is recommended for use with ASSM.)

View partition and Segment space management methods:

Sql> select Tablespace_name,extent_management,segment_space_management from Dba_tablespaces;
Tablespace_name extent_management segment_space_management
------------------------------ ----------------- ------------------------
SYSTEM LOCAL MANUAL
Sysaux LOCAL AUTO
UNDOTBS1 LOCAL MANUAL
TEMP LOCAL MANUAL
USERS LOCAL AUTO
EXAMPLE LOCAL AUTO
Lxj_temp LOCAL MANUAL
Lxj_data LOCAL AUTO
8 Rows selected

===============

From:

Http://www.cnblogs.com/blsong/archive/2009/11/09/1599076.html

http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_7003.htm#SQLRF01403

Oracle Table Space management methods segment and extent