How to create a tablespace in Oracle Database?

1 year ago
Sophia Anderson
2 minutes

To create a tablespace in Oracle database, you can use the following syntax:

CREATE TABLESPACE tablespace_name
  DATAFILE 'file_name'
  SIZE size
  [AUTOEXTEND ON NEXT size]
  [MAXSIZE size]
  [EXTENT MANAGEMENT {LOCAL | DICTIONARY}]
  [SEGMENT SPACE MANAGEMENT {AUTO | MANUAL}]
  [DEFAULT STORAGE(storage_clause)]
  [TEMPORARY]
  [UNDO];

tablespace_name is the name of the tablespace to be created, file_name is the data file name of the tablespace, and size is the initial size of the tablespace. AUTOEXTEND ON NEXT parameter is used to specify the automatic extension size of the tablespace, MAXSIZE parameter is used to specify the maximum size of the tablespace. EXTENT MANAGEMENT is used to specify the management method of the tablespace, where DICTIONARY means using dictionary management and LOCAL means using local management. SEGMENT SPACE MANAGEMENT is used to specify the management method of segment space, where AUTO means automatic management and MANUAL means manual management. DEFAULT STORAGE is used to specify the default storage attributes of the tablespace. TEMPORARY is used to create a temporary tablespace, and UNDO is used to create an undo tablespace.

For example, here is a sample of creating a tablespace named “my_tablespace”:

CREATE TABLESPACE my_tablespace
  DATAFILE '/u01/app/oracle/oradata/mydb/my_tablespace.dbf'
  SIZE 100M
  AUTOEXTEND ON NEXT 10M
  MAXSIZE 500M
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO
  DEFAULT STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED);

This example creates a tablespace called “my_tablespace” with a data file located at “/u01/app/oracle/oradata/mydb/my_tablespace.dbf”. The initial size is set to 100MB, with automatic growth increment of 10MB and a maximum size of 500MB. It is configured to use local management and automatic segment space management. The default storage properties are initialized at 1MB, with the next incremental size at 1MB, minimum extension of 1 block, and maximum extension unlimited.

Please adjust the grammar and parameters according to your actual needs.

bannerAds