As Oracle has evolved, the options for creating tablespaces have 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 when you create the tablespace:
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:
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!
There are several "create tablespace" options that determine how big the tablespace will be:
Autoextend on: Creating a tablespace with autoextend on alleviates the potential problem of a database outage if the tablespace reaches maximum extents. You create a tablespace using autoextend with the autoextend on option. Using "create tablespace . . . autoextend on" will push the point of failure to the OS filesystem, which is good because you never want you database to hang because a tablespace cannot grow. With autoextend on, you only need to monitor the free space in the filesystem directory.
Maxsize: This is the maximum size that the datafile can grow. We recommend creating a tablespace with maxsize unlimited to prevent a lock-up.
Size: This is the size of the underlying data file for the tablespace.
Blocksize: Specifying a larger blocksize will not result in a larger tablespace. See here on creating tablespaces with non-default blocksizes.
Uniform size: This specifies the size that the data file will grow when it needs to extend.
By default, an Oracle tablespace 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: