Enterprise Service Cloud - Docs

Create and Delete a Tablespace

Definitions

The Managed Oracle Database Service MODS supports smallfile and bigfile tablespaces. The actual size limits are:

Tablespace TypeSize Limit
Smallfile32768MB per Datafile / 1000 Datafiles per CDB
Bigfile32TB per Datafile / 1 Datafile per Tablespace / 1000 Datafiles per CDB

A tablespace is created based on Oracle Managed Files (OMF) under the globally unique immutable ID (GUID) of the specific Pluggable Database. Path definitions are not allowed.

Note: The default tablespace type in a Pluggable Database is set to smallfile. The maximum number of datafiles in a CDB is limited to 1000. A limit increase can be requested, this requires a restart of the underlying Container Database. If the datafile limit is reached, the provisioning of new Pluggable Database is not possible.

For more information about Oracle Managed Files: https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/using-oracle-managed-files.html#GUID-4A3C4616-0D81-4BBA-8EAD-FCAA8AD5C15Aopen in new window

CREATE Smallfile Tablespace

To create a smallfile tablespace, just run the CREATE TABLESPACE command. See the following example with specification of size and autoextend clause (optional). If the AUTOEXTEND clause MAXSIZE is set to UNLIMITED, the datafile size limit is set to 32768MB.

CREATE TABLESPACE app_data_01 DATAFILE SIZE 2G [AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED];

Verification:

SELECT tablespace_name, bytes/1024/1024 as "SIZE MB", round(maxbytes/1024/1024) as "MAXSIZE MB" 
FROM dba_data_files
WHERE tablespace_name='APP_DATA_01';

TABLESPACE_NAME                   SIZE MB MAXSIZE MB
------------------------------ ---------- ----------
APP_DATA_01                          2048      32768

Note: If no sizes are defined, as per default a datafile with these settings is created:

  • Initial Size: 100GB
  • Increment by: 100MB
  • Maxsize: Unlimited
  • Autoextensible: Yes

EXTEND Smallfile Tablespace

To extend an existing smallfile tablespace, run the ALTER TABLESPACE command.

ALTER TABLESPACE app_data_01 ADD DATAFILE SIZE 2G [AUTOEXTEND ON NEXT 256M MAXSIZE UNLIMITED];

CREATE Bigfile Tablespace

To create a bigfile tablespace, run the CREATE BIGFILE TABLESPACE command. See the following example with specification of size and autoextend clause (optional). If the AUTOEXTEND clause MAXSIZE is set to UNLIMITED, the datafile size limit is set to 32TB.

CREATE BIGFILE TABLESPACE app_data_big DATAFILE SIZE 100G [AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED];

Verification:

SELECT tablespace_name, bytes/1024/1024/1024 as "SIZE GB", round(maxbytes/1024/1024/1024/1024) as "MAXSIZE TB" 
FROM dba_data_files
WHERE tablespace_name='APP_DATA_BIG';

TABLESPACE_NAME                   SIZE GB MAXSIZE TB
------------------------------ ---------- ----------
APP_DATA_BIG                          100         32

DROP Tablespace

To drop a tablespace, run the DROP TABLESPACE tsname INCLUDING CONTENTS AND DATAFILES command:

DROP TABLESPACE app_data_01 INCLUDING CONTENTS AND DATAFILES.

Note: This command will drop the tablespace including its datafiles automatically. No CONTENTS and DATAFILE clause results into an ORA-01031.

Last Updated: