Tablespaces

Tablespaces

A tablespace is a logical storage unit within an Oracle database. It is logical because a tablespace is not visible in the file system of the machine on which the database resides. A tablespace, in turn, consists of at least one datafile which, in turn, are physically located in the file system of the server. Btw, a datafile belongs to exactly one tablespace.

Each table, index and so on that is stored in an Oracle database belongs to a tablespace. The tablespace builds the bridge between the Oracle database and the filesystem in which the table's or index' data is stored.

There are three types of tablespaces in Oracle:
A.Permanent tablespaces
B.Undo tablespaces
C.Temporary tablespaces

Creating a tablespace
A tablespace is created with the create tablespace sql command.

----Permanent tablespace
create tablespace ts_something logging datafile '/dbf1/ts_sth.dbf' size 32m
autoextend on next 32m maxsize 2048m extent management local;

create tablespace data datafile '/home/oracle/databases/ora10/data.dbf'size 10Mautoextend on maxsize 200M extent management local uniform size 64K;

----Temporary tablespace
create temporary tablespace temp_mtr tempfile '/dbf1/mtr_temp01.dbf' size 32m
autoextend on next 32m maxsize 2048m extent management local;
--- Undo tablespace
create undotablespace ts_undo datafile '/dbf/undo.dbf' size 100M;

Dropping a tablespace
Dropping a tablespace is a structural change to the database that requires a backup.
The extent size of the objects in the tablespace are subject to the tablespace's minimum extent size.

Quotas on tablespaces
Users can have (space) quotas on tablespaces. This is a means to limit how much space a user uses on a tablespace. This quota can be set using alter user quota.

Tablespace groups
This is a feature that is available with Oracle 10g.

Assigning a tablespace to a tablespace group:

SQL>alter tablespace ts_user tablespace group ts_grp_user;


Renaming Tablespaces
This is a feature that is available with Oracle 10g and can be useful in transportable tablespace scenarios.

SQL>alter tablespace ts_user rename to ts_user_01;

The system and sysaux tablespace cannot be renamed, though. But that will not be much of a problem, However, read only tablespaces can be renamed.
After renaming a tablespace, the controlfiles should immediatly be backed up.

The system tablespace

Every Oracle database contains a tablespace named SYSTEM that Oracle creates automatically when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.
A small database might need only the SYSTEM tablespace; however, it is recommended that you create at least one additional tablespace to store user data separate from data dictionary information. This allows you more flexibility in various database administration operations and can reduce contention among dictionary objects and schema objects for the same datafiles.

Note: The SYSTEM tablespace must always be kept online

The sysaux tablespace
The sysaux tablespace is new with Oracle 10g . It is used to store database components that were stored in the system tablespace in prior releases of the database. Also, the tablespaces that were needed for RMAN's recovery catalog, for Ultra Search, for Data Mining, for XDP and for OLAP are going to sysaux with 10g. Additionally, it is the place where automatic workload repository stores its information.

occupants
An occupant is a set of (application-) tables within the sysaux tablespace. The occupants can be viewed with the v$sysaux_occupants

Temporary tablespace
A temporary tablespace is a tablespace that can only be used for sort segments. No permanent objects can reside in a temporary tablespace. Sort segments are used when a segment is shared by multiple sort operations. One sort segment exists in every instance that performs a sort operation in a given tablespace.
Temporary tablespaces provide performance improvements when you have multiple sorts that are too large to fit into memory. The sort segment of a given temporary tablespace is created at the time of the first sort operation. The sort segment grows by allocating extents until the segment size is equal to or greater than the total storage demands of all of the active sorts running on that instance

Transportable tablespaces
Transportable tablespaces can quickly be moved across different platforms (with Oracle 10g)
This allows databases to be migrated at almost the speed of a file transfer.
For example, data providers can deploy their data to custumers that run Oracle on different platforms more easily. Also, usually, data marts are on different platforms as the data warehouse. With transportable tablespaces, the data can be 'copied' from the data warehouse to the data mart more efficiently.

Transportable tablespaces require the export transportable tablespaces option.

RMAN> convert tablespace ts_data, ts_idx to platform 'Microsoft Windows NT' format '/temp/%U';

Bigfile tablespaces
This is a feature of Oracle 10g. A bigfile tablespace contains only one datafile (or tempfile) which can be as big as 2^32 (=4GB) blocks
create bigfile tablespace beeeg_ts data file '/o1/dat/beeeg.dbf' size 2T
Bigfile tablespaces are supported only for locally managed tablespaces with automatic segment-space management (which is the default setting since Oracle 9i). There are two exceptions: locally managed undo and temporary tablespaces can be bigfile tablespaces, even though their segments are manually managed.
The system and sysauxtablespace cannot be created as bigfile tablespace.
Bigfile tablespaces should be used with automatic storage managemet, or other logical volume managers that support dynamically extensible logical volumes, striping and RAID.

Smallfile tablespaces
A smallfile tablespace is a traditional tablespace that can contain up to 1022 datafiles.

Default tablespaces
An Oracle database can be configured with a default tablespace and a default temporary tablespace. These are used for users who are not configured with default tablespaces or default temporary tablespaces .
Displaying tablespaces
The dba_tablespaces view displays all tablespaces along with the respective relevant information.
Space management
Oracle maintains extents for a tablespace. There are two different methods for Oracle to keep track of free and used (occupied) extents:
Dictionary managed tablespace
Locally managed tablespace
A 'bitmap' is stored within the tablespace. Each bit within this bitmap determines if a corresponding extent in the tablespace is free or used. The extent sizes are either uniform or autoallocate.

resulting in the following benefits:

1.Improved concurrency and speed of space operations, because space allocations and deallocations predominantly modify locally managed resources (bitmaps stored in header files) rather than requiring centrally managed resources such as enqueues

2.Improved performance, because recursive operations that are sometimes required during dictionary-managed space allocation are eliminated
Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.

3.Simplified space allocation--when the AUTOALLOCATE clause is specified, appropriate extent size is automatically selected

4.Reduced user reliance on the data dictionary because necessary information is stored in file headers and bitmap blocks

EXTENT MANAGEMENT LOCAL AUTOALLOCATE
EXTENT MANAGEMENT LOCAL UNIFORM

Segment Space Management in Locally Managed Tablespaces

A.MANUAL

Specifying MANUAL tells Oracle that you want to use free lists for managing free space within segments. Free lists are lists of data blocks that have space available for inserting rows.

MANUAL is the default.

B. AUTO

This keyword tells Oracle that you want to use bitmaps to manage the free space within segments. A bitmap, in this case, is a map that describes the status of each data block within a segment with respect to the amount of space in the block available for inserting rows. As more or less space becomes available in a data block, its new state is reflected in the bitmap. Bitmaps allow Oracle to manage free space more automatically, and thus, this form of space management is called automatic segment-space management.

CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Dictionary managed tablespaces
For dictionary-managed tablespaces, Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated, or freed for reuse. Extents are allocated according to the following storage parameters initial next pctincrease .The information about used and free extents is stored in the dictionary.

CREATE TABLESPACE tbsb DATAFILE '/u02/oracle/data/tbsa01.dbf' SIZE 50M EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

State of tablespaces
A tablespace is either online (ready to be accessed) or offline (not accessible).
A tablespace becomes offline either
when the DBA explicitely makes the tablespace offline, or
when an error occures while Oracle accesses a datafile within the tablespace.
Offline tablespaces cannot be transported to other databases.

Read Only Tablespaces

The primary purpose of read-only tablespaces is to eliminate the need to perform backup and recovery of large, static portions of a database. Oracle never updates the files of a read-only tablespace, and therefore the files can reside on read-only media, such as CD ROMs or WORM drives.

Whenever you create a new tablespace, it is always created as read-write. The READ ONLY option of the ALTER TABLESPACE command allows you to change the tablespace to read-only, making all of its associated datafiles read-only as well. You can then use the READ WRITE option to make a read-only tablespace writeable again.
Read-only tablespaces cannot be modified. Therefore, they do not need repeated backup. Also, should you need to recover your database, you do not need to recover any read-only tablespaces, because they could not have been modified.
You can drop items, such as tables and indexes, from a read-only tablespace, just as you can drop items from an offline tablespace. However, you cannot create or alter objects in a read-only tablespace.

Locally managed tablespaces

Locally managed tablespaces have some part of the tablespace set aside for 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. The default SIZE is 1 megabyte. NOTE: If you do not specify either AUTOALLOCATE or UNIFORM with the LOCAL parameter, then AUTOALLOCATE is the default. Locally Managed Tablespaces---------------------------
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 newstatus 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 (except for special cases such as tablespace quota information).
Local management of extents automatically tracks adjacent free space, eliminating the need to coalesce free extents. 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. A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system.When you create the tablespace, the UNIFORM or AUTOALLOCATE (system managed) option specifies the type of allocation.
For system managed extents, Oracle determines the optimal size of extents, witha minimum extent size of 64K. This is the default for permanent tablespaces.
For uniform extents, you can specify an extent size or use the default size,which is 1 MB. Temporary tablespaces that manage their extents locally can onlyuse this type of allocation.
In locally managed tablespaces, Oracle uses object's storage parameters INITIAL,NEXT, PCTINCREASE, and MINEXTENTS in conjunction with the size of extents specified for the tablespace to determine the object's first extent. For example, in a uniform locally managed tablespace with 2M extents, if you specify (in CREATE TABLE, etc. )INITIAL value of 1M, NEXT 2M, and MINEXTENTS 4, then Oracle initially creates four 2M extents - to provide for 1M+2M+2M+2M=7M of initial size of the object.
One cannot create a locally managed SYSTEM tablespace in 8i.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 tablespaces in such database have to be locally managed as well.
Locally managed temporary tablespaces can not be of type "permanent".
Examples (1)------------
REM AUTOALLOCATE specifies that the tablespace is system managed. REM Users cannot specify an extent size.
SQL> CREATE TABLESPACE local_uniform DATAFILE 2 'local_u.dbf' SIZE 1M reuse 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; Tablespace created.
SQL> CREATE TABLESPACE local_auto DATAFILE 2 'local_auto.dbf' SIZE 1M reuse 3 EXTENT MANAGEMENT LOCAL AUTOALLOCATE; Tablespace created.
SQL> select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, 2 MIN_EXTLEN, EXTENT_MANAGEMENT, ALLOCATION_TYPE, PLUGGED_IN 3 from dba_tablespaces 4 where tablespace_name like 'LOCAL%';
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MIN_EXTLEN EXTENT_MAN ALLOCATION PLUGG --------------- -------------- ------------ ----------- ---------------- ----- ---------- ---------- ----- LOCAL_AUTO 65,536 1 2,147,483,645 65,536 LOCAL SYSTEM NO LOCAL_UNIFORM 131,072 131,072 1 2,147,483,645 131,072 LOCAL UNIFORM NO
Now let us create 2 tables in the local tablespaces:
SQL> CREATE TABLE local_table1 ( 2 DEPTNO NUMBER(3) NOT NULL, 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13)) 5 storage (initial 10k next 10k) 6 tablespace local_uniform; Table created.
SQL> CREATE TABLE local_table2 ( 2 DEPTNO NUMBER(3) NOT NULL, 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13)) 5 storage (initial 10k next 10k) 6 tablespace local_auto; Table created.
Look at the INITIAL and NEXT extents in the USER_TABLES:
SQL> select table_name, tablespace_name, INITIAL_EXTENT, NEXT_EXTENT 2 from user_tables where table_name like 'LOCAL%';
TABLE_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT ------------------------------ --------------- -------------- ------------ LOCAL_TABLE1 LOCAL_UNIFORM 10,240 131,072 LOCAL_TABLE2 LOCAL_AUTO 10,240
Check the actual size of each allocated extent:
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS 2 from user_extents where segment_name like 'LOCAL%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS --------------- --------------- --------------- --------- ------------ ------ LOCAL_TABLE1 TABLE LOCAL_UNIFORM 0 131,072 64 LOCAL_TABLE2 TABLE LOCAL_AUTO 0 65,536 32
Let us allocate another extent for each table:
SQL> alter table local_table1 allocate extent; Table altered.
SQL> alter table local_table2 allocate extent; Table altered.
SQL> select SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID, BYTES, BLOCKS 2 from user_extents where segment_name like 'LOCAL%';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS --------------- --------------- --------------- --------- ------------ ------ LOCAL_TABLE1 TABLE LOCAL_UNIFORM 0 131,072 64 LOCAL_TABLE1 TABLE LOCAL_UNIFORM 1 131,072 64 LOCAL_TABLE2 TABLE LOCAL_AUTO 0 65,536 32 LOCAL_TABLE2 TABLE LOCAL_AUTO 1 65,536 32
The db_block_size in this example is 2048 (2K).
Thus, the extent sizes for tables created in tablespaces LOCAL_UNIFORM andLOCAL_AUTO are 128K (131072 bytes) and 64K (65536 bytes), correspondingly.
In the next Example, note the effect of object's INITIAL, NEXT, PCTINCREASE, and MINEXTENTS storage parameters when creating tables.
Example (2)-----------
CREATE TABLESPACE lmt_1 DATAFILE 'lmt_1.dbf'size 20m EXTENT MANAGEMENT LOCAL uniform size 2m;
Tablespace created.
create table lmt_test1 (a number)STORAGE (initial 1m next 2m minextents 4) tablespace lmt_1;
Table created.
select EXTENT_ID, BYTES from dba_extents where SEGMENT_NAME='LMT_TEST1';
EXTENT_ID BYTES---------- ---------- 0 2097152 1 2097152 2 2097152 3 2097152
create table lmt_test2 (a number)STORAGE (INITIAL 1m next 2m minextents 4 pctincrease 50) tablespace lmt_1;
Table created.
select EXTENT_ID, BYTES from dba_extents where SEGMENT_NAME='LMT_TEST2';
EXTENT_ID BYTES---------- ---------- 0 2097152 1 2097152 2 2097152 3 2097152 4 2097152 5 2097152
create table lmt_test3 (a number)STORAGE (INITIAL 1m next 2m minextents 4 pctincrease 90) tablespace lmt_1;
Table created.
select EXTENT_ID, BYTES from dba_extents where SEGMENT_NAME='LMT_TEST3';
EXTENT_ID BYTES---------- ---------- 0 2097152 1 2097152 2 2097152 3 2097152 4 2097152 5 2097152 6 2097152 7 2097152
As we could see, tables' storage parameters are taken in consideration wheninitially calculating the size of each table (in terms of number of allocated ts extents). After that, each allocated extent will be the tablespace's extent size, or multiple extents - if table extent is allocated via"alter table allocate extent (size XXX)".
Advantages of Locally Managed Tablespaces:------------------------------------------
1. Better space management due to: - uniform extent sizes; - reduced data dictionary access.
2. Reduced tablespace fragmentation.
3. Better management of temporary space.