Skip to content

Create Tablespace Examples

  • by

Creating a normal and permanent tablespace is pretty regular practice for a DBA whenever application needs it, comparing to creating a temporary tablespace.

In this post, let me show you some quick and easy examples that create a permanent tablespace.

Single DataFile

It contains only one datafile when creating the tablespace.

SQL> create tablespace erptbs datafile '/u01/app/oracle/oradata/ORCL/erptbs_01.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

Multiple DataFiles

We repeated the file specification delimiting by commas when creating the tablespace.

SQL> create tablespace erptbs datafile '/u01/app/oracle/oradata/ORCL/erptbs_01.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u01/app/oracle/oradata/ORCL/erptbs_02.dbf' size 10m autoextend on next 10m maxsize unlimited, '/u01/app/oracle/oradata/ORCL/erptbs_03.dbf' size 10m autoextend on next 10m maxsize unlimited;

Tablespace created.

In this case, we initially use 3 datafiles for the tablespace creation.

Reuse DataFile

If the file is existing in the file system physically, you can use REUSE to take over the file.

SQL> create tablespace erptbs datafile '/u01/app/oracle/oradata/ORCL/erptbs_01.dbf' size 10m reuse autoextend on next 10m maxsize unlimited;

Tablespace created.

To better manage a tablespace, you may want to know how big a tablespace can grow.

Leave a Reply

Your email address will not be published.