Tablespace in oracle 9i

Tablespace is a logical storage unit in database. It a bridge between the datafiles and oracle instance.
Hierarchy of tablespace structure.
1. Tablespace can belonging to only database at a time
2. T tablespace can have more than one datafiles

Creating tablespace
Syntax: Create tablespace tbs_name datafile ‘file location/file_name.dbf’ size 100m

Tablespace clauses
Blocksize – set nonstandard block size for the tablespace.
You must set db_cache_size and at least one db_nk_cache_size.
Logging (d) nologging – all tables and indexes within the tablespace have all changes
written to redo.
Default – specify default storage for all objects created in the tablespace creation. (DMT only)
(Initial, next, pctincrease, minextends, maxenteds)
Online offline (d) – tablespace availability
Permanent temporary – tablespace can be used to hold permanent object
or temporary object (sorting)
Autoextend – this enable automatic extension of datafile
Next – this increment of next disk space to be allocated auto when more extents are required.
Maxsize - maximum disk space allowed for auto extension of datafile.
Unlimited – unlimited disk space allowed for auto extension of datafile.
Extent management – how the extent of tablespace are managed (LMT or DMT)
Segment management – track the used and free space in the segment in the tablespace
using free lists or bitmap (LMT only) . Pctfree , Pctused, Freelist, Freelist group
Autoallocate (d) – tablespace is system managed, user cannot specify an extent size
Uniform – tablespace is managed with uniform extents of size 32m or bytes.
By default 1mb in size.
Reuse – reuse the existing file.
Resize – enlarging the tablespace size.

Types of Tablespace
Ø System tablespace
- Created with the database
- Contain data-dictionary and stored program units

Ø Non-system tablespace
- Undo
- Temporary
- User data and easy to manage by dba.

Managing Space in Tablespaces.
When oracle allocates space to a segment (table or index), a group of contiguous free blocks (extent), is added to the segment. Metadata regarding the extent allocation and unallocated extents are either stored in the data dictionary are called DMT or stored within the tablespace header are called LMT

Locally managed tablespace – LMT
A tablespace that manages its own extents maintains 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 group of blocks. When an extent is 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 (like sys.uet$, sys.fet$) in the data dictionary
Create tablespace tsb_lmt datafile ‘file spec ‘size 100m extent management local
uniform size 100k ;

Advantage
1. Avoid recursive space management. It won’t consume or release space in the
data-dictionary table.
2. Avoid contention on data-dictionary table.
3. Extents automatically tracks adjacent free space, by eliminating the need of coalesce
free extents.
4. Do not generate undo info bcos don’t update tables in the data-dictionary.

Autoallocate
Create tablespace tbs_aa datafile ‘filespec’ size 100m extent management local
autoallocate;
UniformSize
Create tablespace tbs_uni datafile ‘filespec’ size 100m extent management local uniform
size 32k;

Segment Space Management – ASSM in LMT

DBA has no longer to manage the segment management. Oracle done a major implementation in oracle 9i to manage the segment space automatically.

Create tablespace tbs_ssm datafile ‘filespec’ size 100m entent management local

segment space management auto

Create tablespace tbs_ssm1 datafile ‘filespec’ size 100m extent management local
segment space management manual (pctused 20 pctfree 20)

Dictionary managed tablespace – DMT

In DMT, to keep track of the free or used status of blocks, oracle uses data dictionary tables. When an extent is allocated or freed for reuse, free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do insert and deletes against these tables. As only one process can acquire the ST enque at a given time, this often lead to contention. These changes generate rollback information because they update tables (like sys.uet$, sys.fet$) in the data dictionary.

Create tablespace tbs_dmt datafile ‘file spec’ size 100m extent management dictionary default storage (initial 1m next 2m pctincrease 0 maxextents 10

Default Storage clause – when DMT is created default storage parameters are specified. These values override any default settings to become the default for the objects created in that tablespace.

Syntax:
STORAGE ( {

INITIAL int [KM] NEXT int [KM] MINEXTENTS int MAXEXTENTS {int UNLIMITED} PCTINCREASEஇந்த் FREELISTS int FREELIST GROUPS int OPTIMAL [intr [KM] NULL] BUFFER_POOL {KEEP RECYCLE DEFAULT}} )

Calculation = initial + ( next * pctincrease / 100)

INITIAL integer [K M] -- default value is 5 x db_block_size
Specify the size of the object's first extent. The space for this extent is allocated when the object is created. Must be specified in a CREATE statement. See INITIAL_EXTENT column in DBA_TABLESPACES

NEXT integer [K M] -- default value is 5 x db_block_size
Specify in bytes the size of the next extent to be allocated to the object. The default value is the size of 5 data blocks. The minimum value is 1 data block. If the value of NEXT is changed using the ALTER statement, the NEXT allocated extent will have the newly specified NEXT size, regardless of the size of the most recently allocated extent and the value of the PCTINCREASE parameter.See NEXT_EXTENT column in DBA_TABLESPACES.

MINEXTENTS integer -- default and minimum value is 1 in the form of the INITIAL extent. Exception is rollback segments which require 2.
Specify the total number of extents to allocate when the object is created. Allows a large amount of space to be allocated when the object is created, even if the space available is not contiguous. The default and minimum value is 1, meaning SMON allocates only the INITIAL extent, except for rollback segments, for which the default and minimum is 2. If MINEXTENTS > 1, then SMON calculates the size of subsequent extents based on the values of INITIAL, NEXT, and PCTINCREASE. The value of MINEXTENTS can be reduced but not increased using the ALTER statement. Reducing the value of MINEXTENTS to a smaller value may be useful before a TRUNCATE ... DROP STORAGE, to ensure a segment will maintain a minimum number of extents after the TRUNCATE. The value of MINEXTENTS cannot be changed for an object residing in a locally managed tablespace. See MIN_EXTENTS column in DBA_TABLESPACES.
MAXEXTENTS {integer UNLIMITED} -- minimum value is 1. default depends on db_block_size. Exception is rollback segments which require 2.
Specify the total number of extents, including the INITIAL, that can be allocated for the object. The minimum value is 1 (except for rollback segments, which always have a minimum value of 2). The default value depends on db_block_size. MAXEXTENTS cannot be changed for an object residing in a locally managed tablespace.
PCTINCREASE integer -- Minimum value is 0. Default value is 50.
Specify the percent by which the third and subsequent extents grow over the preceding extent. The minimum value is 0, meaning all extents after the first are the same size. The default value is 50, meaning that each subsequent extent is 50% larger than the preceding extent. If the value of the PCTINCREASE parameter is changed using the ALTER statement, SMON calculates the size of the next extent using the new value and the size of the most recently allocated extent. Setting PCTINCREASE to 0 will prevent SMON from coalescing extents and keep all extents the same size. Setting of 0 as a way to minimize fragmentation and avoid the possibility of very large temporary segments during processing. PCTINCREASE cannot be specified and therefore is always 0 for rollback segments. See: storage.htm
FREELISTS integer

FREELISTS is ignored for a locally managed tablespace in automatic segment-space management mode. For objects other than tablespaces, specify the number of free lists for each of the free list groups for the table, partition, cluster, or index. The default and minimum value is 1, meaning that each free list group contains one free list. The maximum value of this parameter depends on the data block size. If a value for FREELISTS is too large, an error is returned indicating the maximum value. FREELISTS can be specified in the storage_clause of any statement except when creating or altering a tablespace or rollback segments.

FREELIST GROUPS integer

FREELIST GROUPS is ignored for a locally managed tablespace in automatic segment-space management mode. Specify the number of groups of free lists for the database object being creating. The default and minimum value is 1. The instance number of RAC instances is used to map each instance to one free list group. FREELIST GROUPS can only be specified in CREATE TABLE, CREATE CLUSTER, CREATE MATERIALIZED VIEW, CREATE MATERIALIZED VIEW LOG, and CREATE INDEX.

OPTIMAL [integer [KM] NULL]

Only relevant to rollback segments. Specifies an optimal size in bytes for a rollback segments. Oracle tries to maintain this size for the rollback segments by dynamically deallocating extents when their data is no longer needed for active transactions. Oracle deallocates as many extents as possible without reducing the total size of the rollback segments below the OPTIMAL value. The value of OPTIMAL cannot be less than the space initially allocated by MINEXTENTS, INITIAL, NEXT, and PCTINCREASE. The maximum value depends O/S. Specify NULL for no optimal size for the rollback segments, meaning that extents are never deallocated for rollback segment's. This is the default behavior.

BUFFER_POOL {KEEP RECYCLE DEFAULT}

The BUFFER_POOL clause allows a default buffer pool (cache) to be defined for a schema object. All blocks for the object are stored in the specified cache. Unless overridden by a partition-level definition, partitions inherit the buffer pool value from table or index specifications. BUFFER_POOL clause cannot be specified for cluster tables, tablespaces or rollback segments. It can be specified for clusters.
DEFAULT - Specify to indicate the default buffer pool.
KEEP - Specify to put blocks from the segment into the KEEP buffer pool. Maintaining an appropriately sized KEEP buffer pool retains the schema object in memory to avoid I/O operations. KEEP takes precedence over any NOCACHE clause specified for a table, cluster, materialized view, or materialized view log.
RECYCLE - Specify to put blocks from the segment into the RECYCLE pool. An appropriately sized RECYCLE pool reduces the number of objects whose default pool is the RECYCLE pool from taking up unnecessary cache space.
----------------------------------------------------
Converting DMT to LMT
exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
Converting LMT to DMT
exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2')

Changing the availability of tablespace

Ø Read only
Alter tablespace tbs_name read only;

a. Transaction read only, no further write operation except for the rollback of existing

transaction that previously made either committed or rollback.
b. You can drop item such as tables and indexes from read only tablespace.

Ø Offline, Online [ normal temporary immediate for recover]

Alter tablespace tbs_oo offline;
Alter tablespace tbs_oo online;

Normal (d) – flushes all blocks in all datafile in the tablespace out of sga.
We need to perform media recovery before bringing it back to online. (Recommend).
Temporary – perform checkpoint for all datafile, even if some file could not be written.
Immediate - does not perform checkpoint. Recovery needed.
For recover – for TPITR

a. SYSTEM tablespace cannot bring to offline mode
b. Default temporary tablespace cannot bring to offline until new temp tablespace create.
c. Active undo segment cannot be taken offline
d. Tablespace goes offline or comes back online the event is recorded in the data dictionary
and control file.
e. OI automatically switches a tablespace from online to offline when certain errors are
encountered. Eg: dbwr file in several attempt to write to a datafile.

Ø Move and Rename
Alter tablespace tbs_rn rename datafile ‘filespec’ to ‘filespec’;
Note:
a. Database must be mounted and file must exist
b. Tablespace must be in offline mode.
c. Copy and move file in os level using cp or mv command.
d. Execute the above statement
e. Bring the tablespace online.

Ø Adding datafile to a tablespace

Alter tablespace tbs_name add datafile ‘filespec’ size 100m;

Ø Dropping tablespace
Drop tablespace tbs_name including contents and datafiles cascade constraints;
Ø Resizing datafile
Alter database datafile ‘filespec’ resize 200m;
Ø Enable auto extension of datafile
Alter database datafile ‘filespec’ Autoextend on next 1m maxsize 5;

Important
1. This is possible with in 9.2.0.X, where SYSTEM tablespace is created by DBCA as locally managed by default. With a locally managed SYSTEM tablespace, the rest of the tablespace in such database have to be locally managed as well.
2. Locally managed temporary tablespace cannot be of type "permanent".

Views
dba_data_files, v$datafile, - list datafile information
dba_free_space - space usage in tablespace
dba_tablespaces, v$tablespace - list tablespace information