本地表空间管理优点vs数据字典表空间管理(转载)

最新推荐文章于 2021-04-13 16:26:40 发布

lovehewenyu

最新推荐文章于 2021-04-13 16:26:40 发布

阅读量493

点赞数

分类专栏： oracle 基础知识

oracle 基础知识专栏收录该内容

27 篇文章 0 订阅

订阅专栏

本地表空间管理优点vs数据字典表空间管理

Advantages of Using Locally Managed vs Dictionary Managed Tablespaces

PURPOSE
-------

This article describes the locally managed tablespaces, and some of the
advantages of using them over dictionary managed tablespaces.

SCOPE & APPLICATION
-------------------

It is intended for both analysts and customers.

LOCALLY MANAGED TABLESPACES
---------------------------

A Locally Managed Tablespace is a tablespace that manages its own extents
maintaining a bitmap in each datafile to keep track of the free or used status
of blocks in that datafile. Each bit in the bitmap corresponds to a block or
a group of blocks. When the extents are allocated or freed for reuse, Oracle
changes the bitmap values to show the new status of the blocks. These changes
do not generate rollback information because they do not update tables in the
data dictionary (except for tablespace quota information), unlike the default
method of Dictionary - Managed Tablespaces.

** Reduced recursive space management
** Reduced contention on data dictionary tables
** No rollback generated
** No coalescing required

SPACE MANAGEMENT IN TABLESPACES
-------------------------------

** Free extents recorded in bitmap (so some part of the tablespace is set aside
for bitmap)
** Each bit corresponds to a block or group of blocks
** Bit value indicates free or unused
** Common views used are DBA_EXTENTS and DBA_FREE_SPACE

Syntax
------

The LOCAL option of the EXTENT MANAGEMENT clause specifies that a tablespaces
is to be locally managed.

Extent_management_clause:
[EXTENT MANAGEMENT
{DICTIONARY | LOCAL
{AUTOALLOCATE | UNIFORM [SIZE integer [K|M] }}]

where:

DICTIONARY specifies that the tablespace is managed using dictionary
tables (this is the default)

LOCAL specifies that tablespace is locally managed with a bitmap

AUTOALLOCATE specifies that the tablespace is system managed (users cannot
specify an extent size)

UNIFORM         specifies that the tablespace is managed with uniform extents
                of SIZE bytes (use K or M to specify the extent size in
                kilobytes or megabytes. The default size is one megabyte.
                If you specify, LOCAL, you cannot specify DEFAULT
                storage_clause, MINIMUM EXTENT or TEMPORARY.

Prior to Oracle 9.2, you may use the EXTENT MANAGEMENT clause when creating
permanent and temporary tablespaces other than SYSTEM.
In Oracle 9.2, all tablespaces, including the SYSTEM tablespace, can be locally
managed. When you specify the EXTENT MANAGEMENT LOCAL clause in the CREATE
DATABASE statement, you cause Oracle to create a locally managed SYSTEM tablespace
wherein Oracle determines extent sizes. The COMPATIBLE initialization parameter
must be set to 9.2 or higher for this statement to be successful. A locally
managed SYSTEM tablespace is created AUTOALLOCATE by default. It is not possible
to create a locally managed SYSTEM tablespace and specify UNIFORM extent size.

The new mandatory SYSAUX tablespace from Oracle 10g onwards must be locally managed
with SEGMENT SPACE MANAGEMENT AUTO.

The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and
DEFAULT STORAGE are not valid for locally managed tablespaces
in the CREATE TABLESPACE syntax. As far as object creation in LMT is concerned, objects' storage parameters
will define the number of LMT's extents used for the initial object creation.

It is not possible to alter the method of space management later.

Starting with Oracle 8.1.6, you may migrate dictionary managed tablespaces to
locally managed tablespaces, where in 8.1.5 this is not possible.
You can migrate your existing tablespace to locally managed by using the
DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure.
Additionally, the DBMS_SPACE_ADMIN package provides maintenance procedures for
locally managed tablespaces.

Starting in 10g, Oracle lets you create bigfile tablespaces.
A Bigfile tablespace is a tablespace containing a single very large data file.
Bigfile tablespaces are supported only for locally managed tablespaces.
See Note 262472.1 for more info on Bigfile tablespaces.

In this statement, we assume that the database block is 2K:

CREATE TABLESPACE tbs_1
DATAFILE 'file_tbs1.dbf' SIZE 10M
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 128K

This statement creates a locally managed tablespace in which every extent
is 128K and each bit in the bit map describes 64 blocks. In essence, if we
assume a default database block size of 2K, and that each bit in the map
represents one extent (128K), then each bit maps (128k UNIFORM SIZE / 2k
ORACLE BLOCK SIZE) 64 oracle blocks.

Oracle strongly recommends that you create tablespaces that are locally managed rather than dictionary managed.
The creation of new dictionary-managed tablespaces is scheduled for desupport.

*****************************************************************************************
***ADVANTAGES IN USING LOCALLY MANAGED TABLESPACES OVER DICTIONARY MANAGED TABLESPACES***
*****************************************************************************************

- Because locally managed tablespaces do not record free space in data
dictionary, it reduces contention on these tables.

- Local management of extents automatically tracks adjacent free space,
eliminating the need to coalesce free extents.

- Avoids recursive space management operations, which can occur in
dictionary-managed tablespaces if consuming or releasing space in an
extent results in another operation that consumes or releases space in
a rollback segment or data dictionary table.

- Sizes of extents that are managed locally can be determined automatically
by the system. Alternatively, all extents can have the same size in a
locally managed tablespace.

- Changes to the extent bitmaps do not generate rollback information
because they do not update tables in the data dictionary (except for
special cases such as tablespace quota information).

- Reduced fragmentation