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.