Oracle create tablespace & alter tablespace syntax Oracle Database Tips by Donald Burleson

As Oracle has evolved, the options for creating and altering tablespaces has grown more complex, with more create tablespace options:

You create tablespaces with the create tablespace command. Before you create the tablespace you should decide:

1. How big you wish the tablespace to be.
2. Where you want to put the datafile or datafiles that will be associated with that tablespace.
3. What you want to call the tablespace and the datafiles.

We recommend that you include the following in the datafile name:

1. The name of the database
2. The name of the tablespace
3. A number that makes the datafile unique

So, for a tablespace called USERS assigned to a database called BOOKTST we would have a datafile called booktst_users_01.dbf. Here is an example of creating such a tablespace using the create tablespace command:

create tablespace
   users
datafile  
  ?/ora01/oracle/oradata/booktst_users_01.dbf?
size
   50m;

Note that we used 50m to indicate that we wanted to create the tablespace 50 Megabytes in size. You can also use the K symbol for Kilobytes, and the G symbol for Gigabytes. If you want to try to use the P symbol for a 50 Petabyte tablespace, we want to talk to you!

By default, Oracle tablespaces will not grow if it runs out of space. If you use up all your space, you are just out of luck unless you use the autoextend keyword to indicate that the tablespace may grow, on it's own, dynamically. Here is an example of creating a tablespace that is set to autoextend:

create tablespace
   users
datafile
   ?/ora01/oracle/oradata/booktst_users_01.dbf?
size
   50m
autoextend on
next 10m
maxsize 100m;

In this example, the tablespace will auto extend in increments of 10 megabytes until it reaches a maximum size of 100 megabytes. We recommend you use auto extend on all tablespaces for any production database.  Also see: Oracle tablespace uniform size extents
 

Oracle has many option for altering a tablespace but several create tablespace options (uniform size) cannot be added after the tablespace has been created: