Creating a Tablespace

You can create additional tablespaces to store user data, so that not all data is stored in the USERS tablespace. The following are some reasons to create additional tablespaces:

  • For certain users, groups of users, or applications, it may be convenient to keep all application data in a separate tablespace or set of tablespaces for backup and recovery or maintenance reasons. For example, suppose you must recover all application data from backup due to a hardware or software failure, and you want to perform an offline recovery. If the application data is kept in a separate tablespace, then you can take just that tablespace offline and recover it, without affecting the operation of other database applications.

  • Some applications, such as those with large partitioned tables, may benefit from distributing data across multiple tablespaces. This approach allows the optimal use of the available storage because frequently accessed data can be placed on high performance disks, and infrequently retrieved data can be placed on less expensive storage.

To create a tablespace:

  1. In Oracle Enterprise Manager Database Express (EM Express), from the Storage menu, select Tablespaces.
  2. To create a new tablespace, click the Create button.

    The Create Tablespace wizard appears, showing the General page.

  3. In the Name field, enter a name for the tablespace.
  4. In the Bigfile section, select Smallfile.

    Note:

    If you select Use bigfile tablespace, then the tablespace can have only one data file. Bigfile tablespaces are used with very large databases that use Oracle Automatic Storage Management or other logical volume managers that support striping, RAID, and dynamically extensible logical volumes.

    EM Express does not support Oracle Automatic Storage Management database instances.

  5. In the Status section, select Online.
  6. To go to the next page in the wizard, click the right arrow button.

    The Add Datafiles page appears.

  7. For the Datafiles field, enter the name for the datafile. If the datafile name includes a number in the suffix (such as df_1), you can click the + button or press the Enter key to create multiple data files with the options you select on the Add Datafiles page.
  8. For the File Size field, enter appropriate values for your data file location and initial size.
  9. Select Auto Extend.
  10. For the Increment field, select the additional space to be added to the file each time it extends.
  11. For the Maximum File Size field, enter the maximum size for this data file.
  12. After adding the data files for the new tablespace, click the right arrow button to go to the next page in the wizard.

    The Space page appears.

    Note:

    On the Add Datafiles page and subsequent pages in the wizard, you can click the right arrow button to go to the next page, or you can click OK to create the tablespace without continuing through the wizard. The default values on on the remaining wizard pages will be used to create the tablespace.

  13. For Block Size, select the block size to use for the tablespace.
  14. For Extent Allocation, select Automatic.
  15. Click the right arrow button to go to the next page in the wizard.

    The Logging page appears.

  16. In the Logging section, select Logging.
  17. Click the right arrow button to go to the final page in the wizard.

    The Segments page appears.

  18. In the Segment Space Management section, select Automatic.
  19. In the Compression section, select None.
  20. Click OK to add the tablespace.

See Also:

"About Tablespaces"