Tablespace

Tablespace

From Oracle FAQ

Jump to: navigation , search

<!-- start content -->

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.

<script type="text/javascript"> if (window.showTocToggle) { var tocShowText = &quot;show&quot;; var tocHideText = &quot;hide&quot;; showTocToggle(); } </script>

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 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

 Best practices

• 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 needs to be checkpointed , resulting is unnecessary processing.