Hey, did you catch that redirect? The Toad World URL is now community.toadworld.com. Don't worry -- you'll still find all the same great content here (and more on the way). We're just in the process of giving our Toad World site some well-deserved love. Stay tuned for some more updates coming this way.
Meanwhile, enjoy community.toadworld.com.
Locally managed tablespaces are an Oracle
feature that was introduced in Oracle version 8i. There are several
benefits associated with locally managed tablespaces, as well as a few
restrictions. In this topic we will cover locally managed tablespaces in
Tablespaces can be created using one of two
types of space management. The traditional tablespace is created as a
dictionary-managed tablespace. When dictionary managed tablespaces are
used, the allocation and deallocation of space for the tablesapce is managed via the data dictionary in the
SYSTEM tablespace. One data dictionary table is SYS.UET$ to track used extents. The other data dictionary table is SYS.FET$ to track free extents.
There are negative implications to the use of
dictionary-managed tablespaces. Since they use the data dictionary tables,
which are contained in SYSTEM tablespace, there is a redo and UNDO
generation associated with space allocations and deallocations within the tablespace. Contention for the two data dictionary tables can also lead to poor performance. Other
performance problems can be caused by the taking of shared pool latches as
these dictionary tables are updated.
Locally managed tablespaces were introduced
in Oracle8i to eliminate the space management performance overhead
associated with dictionary-managed tablespaces, and to provide some
additional functionality not available with dictionary tablespaces.
Instead of managing space in the SYSTEM tablespace, locally managed
tablespaces manage all space allocations and deallocations within the tablespace datafiles
themselves, using bitmaps. The bits in the maps determine if a section of space is used (turned on) or free (turned off). Locally managed tablespace bitmaps eliminate
the need for recursive SQL to be executed against the data dictionary, and
reduces the redo and UNDO components associated with accessing the data
dictionary. As previously noted, locally managed tablespaces were first
introduced in Oracle8i, and have become the default space management
method used in Oracle9i and Oracle 10g.
Beginning with Oracle 8i tablespaces can be
transported between different databases. This has significant benefits
when moving large amounts of data between different databases. For
example, if you have a large amount of OLTP data that you wish to move
into your data warehouse, you can store that data in a transportable
tablespace. You can then transport a copy of that tablespace to another
database, and plug it in. Tablespaces do not have to be locally managed to be transported to another database.
Oracle9i provides even more benefits when
using locally managed tablespaces. This is because Oracle9i allows you to
create a locally managed tablespace with a block size that is different
than the default block size of the database. This allows you to have
multiple tablespaces of varying block sizes in your database. Further,
this feature allows you to transport tablespaces across databases of
varying block sizes. This removes a restriction in Oracle8i that requires
that transported tablespaces be of the same block size.
Oracle 10g removed one more restriction to transportable tablespaces. Starting with Oracle 10g, the source and destination platforms do not need to be the same. Prior to Oracle 10g, you could only transport a tablespace from one platform to the same platform, i.e. Windows to Windows, or Solaris to Solaris, or Linux to Linux. Oracle 10g lets you transport tablespaces across platforms, for example from Windows to Solaris, or from Solaris to Linux. In order to transport tablespaces from one platform to a different platform, both the source and the destination databases must have COMPATIBLE set to at least 10.0.0.
Aside from the performance issues previously mentioned, there are a few other advantages of locally managed tablespaces. One of the benefits that eases administration is how the Oracle database allocates space in a locally managed tablespace. The DBA has the option of letting the database automatically determine the extent size or have all extents in the tablespace have one uniform size. The auto-allocation method of extents frees the DBA from having to worry about extent sizes. The uniform method of extents ensures that each and every extent in the tablespace is the same size. Dictionary managed tablespaces do not enforce uniform extent sizes in a tablespace.
Another benefit of locally managed tablespaces is that free space does not have to be coalesced. In dictionary managed tablespaces, it is the SMON process that occasionally wakes up and coalesces adjacent chunks of free space. This overhead operation can cause some performance issues. Locally managed tablespace chunks of free space are either one uniform size, or in the case of the autoallocate method, integer multiples of the smallest chunk of free space. Any free space will always be large enough to be used; therefore it does not have to be coalesced into larger chunks of free space. This brings one other benefit to locally managed tablespaces; the tablespace will not become fragmented so as to make small, unusable chunks of free space. The DBA will not have to perform any reorganization task on these tablespaces due to free space fragmentation.
Another benefit of locally managed tablespaces is that standby databases can be opened in READ ONLY mode. This is made possible because locally managed temporary tablespaces do not generate any undo or redo. If the local temporary tablespace is dictionary managed, then undo and redo is generated and the standby database cannot be opened for reporting.
Oracle 10g introduced the concept of a bigfile tablespace. Bigfile tablespaces are helpful in very large databases (VLDB). Instead of a tablespace consisting of multiple small datafiles, the bigfile tablespace is created with a very large (up to 4G blocks) datafile. The bigfile tablespace can only be locally managed with auto segment space management. If the block size of the tablespace is 2K, then the bigfile tablespace can be as large as 8TB. If the block size of the tablespace is 32K, then the bigfile tablespace can be as large as 128TB. This can significantly increase the maximum size of the overall Oracle database.
To create a locally managed tablespace on
an existing database use the local option of the extent management clause
of the create tablespace command. Here is an example of the creation of a
locally managed tablespace:
CREATE TABLESPACE large_tables
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;
When a locally managed tablespace is
created, you can configure two different extent allocation methods. These
two methods are autoallocate or uniform. In the previous example we
demonstrated the use of the autoallocate option. When the autoallocate
method is selected, Oracle will select the initial and next extent sizes
for all objects in the tablespace. Autoallocate is the default space
management method for a locally managed tablespace. Many of today's DBAs are letting Oracle manage the extent sizes in their tablespaces. No longer does the DBA need to worry about determining appropriate values for the initial and next extents for their database segments. Originally, the best practices of the Oracle DBA said to create locally managed tablespaces with uniform extent sizes. As the Oracle community has researched, tested, and grown accustomed to autoallocate, the best practices of today's Oracle DBA is to use the autoallocate option for most locally managed tablespaces.
When you specify autoallocate, Oracle will allocate extent sizes of 64KB, 1MB, or 8MB. The first sixteen extents will all be 64KB in size. When the seventeenth extent is allocated, Oracle decides that it had the wrong extent size and allocates a larger, 1MB extent. After sixteen 1MB extents have been allocated, future extents will be 8MB in size. If you create an object and specify an initial extent of 1MB, then Oracle skips the 64KB extents and proceeds directly to a 1MB extent. If you create an object and specify an initial extent of 2MB, then Oracle allocates two 1MB extents for you.
When the uniform extent management method
is selected, you must specify an associated extent size. When objects are
created in the tablespace, they will all have extents that are sized based
on this value. The default extent size for a uniform extent managed
tablespace is 1M. Here is an example of creating a locally managed tablespace using
the uniform space management option:
CREATE TABLESPACE large_tables
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 500K;
If using uniform extents, it is generally a best practice to create
the extent size as an integer multiple of the blocksize for that tablespace. If using automatically allocated extents, the extent sizes are integer multiples of any valid block size.. Further, when sizing
datafiles to be used in locally managed tablespace, follow this formula to
minimize wasted space used by the bit map in each datafile used to track
Size = 64K + (tablespace block_size * N)
Size = 64K + (tablespace block_size * N)
Also, in Oracle8i and Oracle9i Release 1,
the SYSTEM tablespace could not be created as a locally managed tablespace.
Beginning with Oracle9i Release 2, the SYSTEM tablespace could be created as a locally
managed tablespace. In fact, if you use the Oracle database creation
assistant to create a database in Oracle9iR2 or higher, it will create the SYSTEM
tablespace as a locally managed tablespace by default. If the SYSTEM
tablespace is a locally managed tablespace, then all of the tablespaces in
the database must be locally managed. Dictionary managed tablespaces are
not supported when the SYSTEM tablespace is locally managed.
Oracle offers a new method of managing
space within the blocks of a tablespace in the form of Automatic Segment
Space Management (ASSM), a new feature in Oracle9i. ASSM is enabled via
the segment space management clause of the create tablespace command. The
segment space management clause allows you to select from the default
space management behavior, or to use automatic segment space management
through the use of the manual or auto parameters. Let’s look at a
summary of these two space allocation methods:
If you choose to use ASSM, there are some
restrictions that you should be aware of. These restrictions include:
Due to the architecture and the sharing of data between instances in a Real Application Cluster environment, ASSM should
be considered in the strategy to obtain optimal performance.
The dbms_space_admin package contains
several procedures for managing locally managed tablespaces including
functionality to convert a dictionary-managed tablespace into locally
managed tablespaces. Be cautious when converting
your dictionary-managed tablespace, following the correct procedures
outlined in the Oracle documentation. Many DBAs prefer to move database segments to a locally managed tablespace than to convert a dictionary managed tablespace to locally managed. Indexes can be moved with the alter index rebuild statement. Tables can be moved with the alter table move command. You should always backup your
database before converting any dictionary-managed tablespace to a locally
Table 1: Description of the
procedures in the package from Oracle 10g
To summarize, there are several advantages
to using locally managed tablespaces, including: