how to create a big file tablespace in Oracle

2 years ago
Benjamin Taylor
1 minute

To create a large file tablespace, you can follow these steps:

  1. Make a new storage area
CREATE TABLESPACE bigfile_ts
DATAFILE '/path/to/datafile.dbf' SIZE 10G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

In the above statement, bigfile_ts is the name of the tablespace, /path/to/datafile.dbf is the data file path of the tablespace, and 10G is the initial size of the tablespace.

  1. Change the tablespace.
ALTER TABLESPACE bigfile_ts
AUTOEXTEND ON
NEXT 1G
MAXSIZE 100G;

In the above statement, AUTOEXTEND ON indicates that the tablespace can automatically expand, NEXT 1G indicates that the size of each automatic expansion is 1GB, and MAXSIZE 100G indicates that the maximum size of the tablespace is 100GB.

  1. Modify the tablespace.
ALTER TABLESPACE bigfile_ts
ADD DATAFILE '/path/to/another_datafile.dbf' SIZE 5G;

In the above statement, “/path/to/another_datafile.dbf” is the path to the new data file to be added to the tablespace, with a size of 5G.

Note that creating and modifying tablespaces require appropriate permissions. Additionally, creating a bigfile tablespace requires using the CREATE TABLESPACE statement and specifying the BIGFILE keyword.

bannerAds