O Reduced contention on data dictionary tables
O No undo generated when space allocation or deallocation occurs
O No coalescing required
n Dictionary-managed tablespaces :
O Default method
O Free extents recorded in data dictionary tables
O Extents are managed in the data dictionary
O Each segment stored in the tablespace can have a different storage clause
O Coalescing required
n Locally managed tablespaces have the following advantages over dictionary-managed tablespaces:
1. Local management 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 undo segment or data dictionary table.
2. Because locally managed tablespaces do not record free space in data dictionary tables, it reduces contention on these tables.
3. Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents.
4. The 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
5. Changes to the extent bitmaps do not generate undo information because they do not update tables in the data dictionary (except for special cases such as tablespace quota information).
n FOR RECOVERY clause freezes the checkpoint information wherever it is, by not updating it. Because a point-in-time recovery is desired, if a backup had to be taken, he would have updated the checkpoint information in datafile headers and control files by using either NORMAL(default) or TEMPORARY option
n Dictionary Managed Tablespace cause recursive space management that slows down the systens, however it provides individual STORAGE clause to satisfy user custom needs. It has to be coalesced to fee space.
n Segments in dictionary managed tablespaces can have a customized storage, this is more flexible than locally managed tablespaces but much less efficient.
n Add more space in an existing TABLESPACE e
ALTER DATABASE DATAFILE ‘/u1/d.dbf’ RESIZE 10M
ALTER TABLESPACE tbs ADD DATAFILE ‘/u1/d2.dbf’ size 10M
n Tablespace can store multiple datafiles on different disks, but don’t provide multiplexing (store same contents in multiple files)
n Resizing a Tablespace
A Tablespace can be resized by :
O Changing the size of a data file
- Automatically using AUTOEXTEND
- Manually using ALTER DATABASE
O Adding a data file using ALTER TABLESPACE
n Enabling Automatic Extension of Data Files:
When a data file is created, the following SQL commands can be used to enable automatic extension of the data file:
- CREATE DATABASE
- CREATE TABLESPACE ... DATAFILE
- ALTER TABLESPACE ... ADD DATAFILE
Query the DBA_DATA_FILES views to determine whether AUTOEXTEND is enabled.
