CREATE TABLESPACE statement
This Oracle tutorial explains how to use the Oracle CREATE TABLESPACE statement with syntax and examples.
Description
The CREATE TABLESPACE statement is used to allocate space in the Oracle database where schema objects are stored.
The CREATE TABLESPACE statement can be used to create the 3 kinds of tablespaces:
- Permanent Tablespace
- Temporary Tablespace
- Undo Tablespace
We will take a look at all 3 kinds of tablespaces.
#1 - PERMANENT TABLESPACE
A permanent tablespace contains persistent schema objects that are stored in data files.
Syntax
The syntax for the CREATE TABLESPACE statement when creating a permanent tablespace is:
CREATE
[ SMALLFILE | BIGFILE ]
TABLESPACE tablespace_name
{ DATAFILE { [ 'filename' | 'ASM_filename' ]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
[ AUTOEXTEND
{ OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
}
]
| [ 'filename | ASM_filename'
| ('filename | ASM_filename'
[, 'filename | ASM_filename' ] )
]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
}
{ MINIMUM EXTENT integer [ K | M | G | T | P | E ]
| BLOCKSIZE integer [ K ]
| { LOGGING | NOLOGGING }
| FORCE LOGGING
| DEFAULT [ { COMPRESS | NOCOMPRESS } ]
storage_clause
| { ONLINE | OFFLINE }
| EXTENT MANAGEMENT
{ LOCAL
[ AUTOALLOCATE
| UNIFORM
[ SIZE integer [ K | M | G | T | P | E ] ]
]
| DICTIONARY
}
| SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
| FLASHBACK { ON | OFF }
[ MINIMUM EXTENT integer [ K | M | G | T | P | E ]
| BLOCKSIZE integer [ K ]
| { LOGGING | NOLOGGING }
| FORCE LOGGING
| DEFAULT [ { COMPRESS | NOCOMPRESS } ]
storage_clause
| { ONLINE | OFFLINE }
| EXTENT MANAGEMENT
{ LOCAL
[ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
| DICTIONARY
}
| SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
| FLASHBACK { ON | OFF }
]
}
- SMALLFILE
- A tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size). This is the most common tablespace size to create.
- BIGFILE
- A tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).
TIP: If you omit the SMALLFILE or BIGFILE option, the Oracle database will use the default tablespace type.
- tablespace_name
- The name of the tablespace to create.
- storage_clause
- The syntax for the storage_clause is:
STORAGE
({ INITIAL integer [ K | M | G | T | P | E ]
| NEXT integer [ K | M | G | T | P | E ]
| MINEXTENTS integer
| MAXEXTENTS { integer | UNLIMITED }
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
}
[ INITIAL integer [ K | M | G | T | P | E ]
| NEXT integer [ K | M | G | T | P | E ]
| MINEXTENTS integer
| MAXEXTENTS { integer | UNLIMITED }
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ integer [ K | M | G | T | P | E ] | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
]
)
Example - PERMANENT TABLESPACE
The following is a CREATE TABLESPACE statement that creates a simple permanent tablespace:
CREATE TABLESPACE tbs_perm_01
DATAFILE 'tbs_perm_01.dat'
SIZE 20M
ONLINE;
This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_01 that has one data file called tbs_perm_01.dat.
The following is a CREATE TABLESPACE statement that creates a permanent tablespace that will extend when more space is required:
CREATE TABLESPACE tbs_perm_02
DATAFILE 'tbs_perm_02.dat'
SIZE 10M
REUSE
AUTOEXTEND ON NEXT 10M MAXSIZE 200M;
This CREATE TABLESPACE statement creates a permanent tablespace called tbs_perm_02 that has one data file called tbs_perm_02.dat. When more space is required, 10M extents will automatically be added until 200MB is reached.
The following is a CREATE TABLESPACE statement that creates a BIGFILE permanent tablespace that will extend when more space is required:
CREATE BIGFILE TABLESPACE tbs_perm_03
DATAFILE 'tbs_perm_03.dat'
SIZE 10M
AUTOEXTEND ON;
This CREATE TABLESPACE statement creates a BIGFILE permanent tablespace called tbs_perm_03 that has one data file called tbs_perm_03.dat.
#2 - TEMPORARY TABLESPACE
A temporary tablespace contains schema objects that are stored in temp files that exist during a session.
Syntax
The syntax for the CREATE TABLESPACE statement when creating a temporary tablespace is:
CREATE
[ SMALLFILE | BIGFILE ]
TEMPORARY TABLESPACE tablespace_name
[ TEMPFILE { [ 'filename' | 'ASM_filename' ]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
[ AUTOEXTEND
{ OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
}
]
| [ 'filename | ASM_filename'
| ('filename | ASM_filename'
[, 'filename | ASM_filename' ] )
]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
}
[ TABLESPACE GROUP { tablespace_group_name | '' } ]
[ EXTENT MANAGEMENT
{ LOCAL
[ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
| DICTIONARY
} ]
- SMALLFILE
- A tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size). This is the most common tablespace size to create.
- BIGFILE
- A tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).
TIP: If you omit the SMALLFILE or BIGFILE option, the Oracle database will use the default tablespace type.
- tablespace_name
- The name of the tablespace to create.
Example - TEMPORARY TABLESPACE
The following is a CREATE TABLESPACE statement that creates a temporary tablespace:
CREATE TEMPORARY TABLESPACE tbs_temp_01
TEMPFILE 'tbs_temp_01.dbf'
SIZE 5M
AUTOEXTEND ON;
This CREATE TABLESPACE statement creates a temporary tablespace called tbs_temp_01 that has one temp file called tbs_temp_01.dbf.
#3 - UNDO TABLESPACE
An undo tablespace is created to manage undo data if the Oracle database is being run in automatic undo management mode.
Syntax
The syntax for the CREATE TABLESPACE statement when creating an undo tablespace is:
CREATE
[ SMALLFILE | BIGFILE ]
UNDO TABLESPACE tablespace_name
[ DATAFILE { [ 'filename' | 'ASM_filename' ]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
[ AUTOEXTEND
{ OFF
| ON [ NEXT integer [ K | M | G | T | P | E ] ]
[ MAXSIZE { UNLIMITED | integer [ K | M | G | T | P | E ] } ]
}
]
| [ 'filename | ASM_filename'
| ('filename | ASM_filename'
[, 'filename | ASM_filename' ] )
]
[ SIZE integer [ K | M | G | T | P | E ] ]
[ REUSE ]
}
[ EXTENT MANAGEMENT
{ LOCAL
[ AUTOALLOCATE | UNIFORM [ SIZE integer [ K | M | G | T | P | E ] ] ]
| DICTIONARY
} ]
[ RETENTION { GUARANTEE | NOGUARANTEE } ]
- SMALLFILE
- A tablespace that contains 1,022 data or temp files (each file can be up to 4 million blocks in size). This is the most common tablespace size to create.
- BIGFILE
- A tablespace that contains only one data or temp file (this file can be up to 4 million blocks in size).
TIP: If you omit the SMALLFILE or BIGFILE option, the Oracle database will use the default tablespace type.
- tablespace_name
- The name of the tablespace to create.
Example - UNDO TABLESPACE
The following is a CREATE TABLESPACE statement that creates an undo tablespace:
CREATE UNDO TABLESPACE tbs_undo_01
DATAFILE 'tbs_undo_01.f'
SIZE 5M
AUTOEXTEND ON
RETENTION GUARANTEE;
This CREATE TABLESPACE statement creates an undo tablespace called tbs_undo_01 that is 5MB in size and has one data file called tbs_undo_01.f.
No comments:
Post a Comment