Tablespace in ORACLE Database

A tablespace is a container for segments (tables, indexes, etc). A database consists of one or more tablespaces, each made up of one or more data files. Tables and indexes are created within a particular tablespace.

Oracle has a limit of 64,000 data files per database.

Default tablespaces

When a new database is created, it will have the following tablespaces (as created by the Database Configuration Assistant):

• SYSTEM (the data dictionary)
• SYSAUX (optional database components)
• TEMP (temporary tablespace, see tablespace types below)
• UNDOTBS1 (undo tablespace, see tablespace types below)
• USERS (default users tablespace created)

Tablespace creation

The only mandatory parameter to create tablespace in CREATE TABLESPACE statement is its name.

CREATE TABLESPACE  <tblspc_name>;

Created tablespace will then be:

• Permanent, locally managed and with system allocated extent size.
• Datafile will be created in location provided in the DB_CREATE_FILE_DEST parameter and with size 100 MB. The datafile is autoextensible with no maximum size.
• Name of datafile will be similar to "ora_applicat_zxyykpt000.dbf"

Complete Syntax

 CREATE [TEMPORARY / UNDO] TABLESPACE  <tblspc_name>
 DATAFILE / TEMPFILE       '<datafile01_name and Path where file to create>' SIZE <integer M>[,
                           '<datafile02_name and Path where file to create>' SIZE <integer M>[,
                           '<datafile0N_name and Path where file to create>' SIZE <integer M>[,...]]]
 BLOCKSIZE  <DB_BLOCK_SIZE parameter /2k/4k/8k/16k/32k >
 AUTOEXTEND { [OFF/ON (NEXT <integer K/M >  MAXSIZE<integer K/M >) / UNLIMITED] } 
 LOGGING/NOLOGGING (Logging default) 
 ONLINE/OFFLINE (Online default)
 EXTENT MANAGEMENT { [DICTIONARY] /
                     [LOCAL Default (AUTOALLOCATE / UNIFORM <integer K/M >)] }
 PERMANENT  / TEMPORARY (Permanent default)
 MINIMUM EXTENT
 DEFAULT STORAGE  {    [INITIAL <integer K/M >]
                       [NEXT <integer K/M >]
                       [PCTINCREASE <integer K/M >]
                       [MINEXTENTS <integer>]
                       [MAXEXTENTS <integer> / UNLIMITED]
                       [FREELISTS <integer>]
                       [FREELIST GROUPS <integer>]
                       [OPTIMAL <integer>/NULL]
                       [BUFFER_POOL < DEFAULT/KEEP/RECYCLE >] }
 CHUNK <integer K/M >
 NOCACHE;

• BLOCKSIZE – By Default blocksize define in the parameter DB_BLOCK_SIZE. In Oracle9i, multiple blocksize that is different block size for different tablespaces, can be defined; all datafiles of a same tablespace have the same block size.
• DEFAULT STORAGE :
  • INITIAL – Specifies the size of the object's first extent.3 k minmum for Locally and 2 k minimum Dictionary.
  • NEXT – Specifies the size of the object's sucessive extent.
  • PCTINCREASE – Specifies the ratio of the third or the preceding extent of the object. The default value for PCTINCREASE is 50 % and the minimum value is 0%.
  • MINEXTENTS – The total number of extent allocated to the segment at the time of creation
  • MAXEXTENTS – The maximum number of extent that can be allocated to the segment .
• MININUM EXTENT – The size is specifies in this clause.The extent are multiple of the size specified in this clause .NEXT and INITIAL extent size specified should be multiple of minmum extent.
• PERMANENT / TEMPORARY – Permannent is default, use to store the table,index etc,Temporary is for temporay segments(sorts in Sql) can not store table,index in temporary tablespace.
• LOGGING / NOLOGGING – Logging is default,the DDL operation & direct insert load are recorded in the redo log file.
• ONLINE / OFFLINE - Online is default,tablespace is available as soon as created.

]Tablespace types

Different tablespace types can be created for different purposes:

Permanent tablespaces

Permanent tablespaces are used to store user data and user created objects like tables, indexes and materialized views. Sample create statements:

CREATE TABLESPACE tools DATAFILE '/u01/oradata/orcl/tools/file_1.dbf' SIZE 100M;

CREATE TABLESPACE tools DATAFILE 'C:\ORA\tools01.dbf' SIZE 100M AUTOEXTEND ON MAXSIZE 500M;

Temp tablespaces

Temp or temporary tablespaces are used to store data with short lifespan (transient data), for example: global temporarily tables or sort results.

CREATE TEMPORARY TABLESPACE temp TEMPFILE '/u01/oradata/orcl/temp/file_1.dbf' SIZE 100M;

With a single temp tablespace, the database will only write to one temp file at a time. However, Temporary tablespace groups, an Oracle 10g feature, can be created to allow Oracle to write to multiple temp files simultaneously.

Undo tablespaces

Undo tablespaces are used to store "before image" data that can be used to undo transactions. See ROLLBACK.

CREATE UNDO TABLESPACE undots DATAFILE '/u01/oradata/orcl/undo/file_1.dbf' SIZE 20M;

Assign tablespaces to users

Users cannot create objects in a tablespace (even it's their default tablespace) unless they have a quota on it (or UNLIMITED TABLESPACE privilege). Some examples:

Grant user scott access to use all space in the tools tablespace:

ALTER USER scott QUOTA UNLIMITED ON tools;

Prevent user scott from using space in the system tablespace:

ALTER USER scott QUOTA 0 ON system;

Check free/used space per tablespace

Example query to check free and used space per tablespace:

SELECT /* + RULE */  df.tablespace_name "Tablespace",
       df.bytes / (1024 * 1024) "Size (MB)",
       SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
       Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
       fs.bytes / (1024 * 1024),
       SUM(df.bytes_free) / (1024 * 1024),
       Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
       Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name,bytes_free,bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name,bytes_free,bytes_used) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
 ORDER BY 4 DESC;

Sample output:

Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
UNDOTBS1                               65    17.8125         27         73
EXAMPLE                               100     22.625         23         77
USERS                                   5     1.0625         21         79
TEMP                                   20          2         10         90
SYSAUX                            625.125       54.5          9         91
SYSTEM                                700     9.0625          1         99