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 NOLOGGING
setting 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_clause
the 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
AUTO
Segment 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