Oracle Space Management

Each user is associated with a default tablespace. When a user creates a table, index, or cluster and no tablespace is specified to physically contain the schema object, the user's default tablespace is used if the user has the privilege to create the schema object and a quota in the specified default tablespace.

The size of a tablespace is the size of the data files that constitute the tablespace.

There are two options to increase the size of tablespace:

• Add new file into tablespace.
• Resize existing file

The common practice is to have 4GB datafiles. This limit mostly comes from OS limitations. Another alternative available from Oracle v.10 is Bigfile Tablespaces. This allows an Oracle Database to contain tablespaces made up of single large files rather than numerous smaller ones.

The tablespace must exist before creating a new user.

To create the tablespace
By default, an Oracle-managed datafile for a regular tablespace is 100MB and is autoextensible with an unlimited maximum size. However, if in your DATAFILE clause you override these defaults by specifying a SIZE value (and no AUTOEXTEND clause), then the datafile is not autoextensible.

Recommendation for tablespaces with estimated data size < 4G:

CREATE TABLESPACE tbs_01
  DATAFILE 'tbs_1_file1.dat'
    SIZE 2G
  AUTOEXTEND ON
    NEXT 1G
  MAXSIZE 4G
The value of SIZE is the initial size of the file.
The value of NEXT is the minimum size of the increments added to the file when it extends.
The value of MAXSIZE is the maximum size to which the file can automatically extend.

For tablespaces with estimated size of data of more than 4GB, calculate the number of files = (1.20 * estimated_size(G)/4G) + 1
CREATE TABLESPACE tbs_01
  DATAFILE 'tbs_1_file1.dat' SIZE 4G,
    'tbs_1_file2.dat' SIZE 4G,
    'tbs_1_file3.dat' SIZE 4G;

To create an Oracle User
CREATE USER ora_lyris
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE <tablespace name>