Tablespace

From Oracle FAQ
Jump to: navigation, search

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[edit]

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[edit]

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 SIZE <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[edit]

Different tablespace types can be created for different purposes:

Permanent tablespaces[edit]

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[edit]

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[edit]

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[edit]

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[edit]

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

Best practices[edit]

  • Do not create objects in the SYSTEM tablespace. The system tablespace is reserved for the data dictionary.
  • Don't create tablespaces with hundreds of small datafiles. These files need to be checkpointed, resulting in unnecessary processing.

Also see[edit]

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #