Problem:

Within in an Oracle database, the Enterprise Analyser tables are owned by a schema, but stored within tablespaces.  Monitoring has shown my large tablespace is about to run out of room, and I would like to add a new data file, rather than just extend the data file(s) that are already allocated to the tablespace.

How can this be done?

Resolution:

When the Oracle tablespaces are created various options can be specified, including the data files that are used to hold the tablespace.

When the tablespace is created or altered, a maximum size can be given to that data file.

It may not be possible to increase the size of the data file(s) and a new data file may have to be allocated to the tablespace.

This new data file does not need to be on the same disk or partition as the other data files.

The attached script is aimed at Windows systems, but can be used on UNIX system, as it asks for the name of the data file, but does not ensure that it is a windows name.

5732.WorkspaceExtend.sql

There are two other related articles:

1. Moving Oracle Tablespace - Has an attached script that can be used to move a data file to another disk.

2. Space Left in Oracle Tablespace used by Enterprise Analyzer Workspace - Has an attached script that can be used to calculate remaining physical space in an Oracle Tablespace.