Problem:

Within an Oracle database, the Enterprise Analyser (EA) tables are owned by a schema, but stored within tablespaces. If there is not enough space to extend the data files for the tablespace, how can I move them to another location?

Resolution:

Although the data file(s) for a tablespace can be as large as is required, it is possible that they become too large for the disks / or partitions that they were originally created on. As you may want to keep all the data files together, you may want to move the data file(s) to another disk.

 The general sequence of events is:

-          Take the workspace off-line

-          Move the data files to the new location, this is done outside of Oracle

-          Alter the tablespace to rename the location of the data files

-          Bring the tablespace back on-line

Some points to note about the above steps:

-          The changes need to be made by a user with the correct DBA privileges.

-          Taking a tablespace off-line means that any objects in that tablespace can no longer be used, but objects in other tablespaces on the same database are still accessible.

-          For EA users, this means that the relevant Workspace is unavailable.

-          This would also effect Enterprise View users, if they are using the Workspace that is being moved.

-          The data files can only be moved between similar media. So data file can be moved from windows drive to another or from one Unix partition to another. They cannot be moved from Windows to Unix. If this is required, then an export/import would be performed to complete the task. 

To help with this, there are some scripts that have been written as examples of moving the data files.

 RENAME_LARGE.sql and RENAME_MED.sql are based on the create_large and create_med tablespaces that can be created when Oracle is installed as part of EA.  (NOTE : In both scripts the data files are move from the D drive to the E drive.)

 Move_TableSpace_Data_File.sql  - A more generic script that will ask for the name of tablespace and the location and name of the new data file. The script can only move 1 data file per tablespace and while the script is waiting for the location and name of the data file, the physical move of the data file can take place.

Test_Move_TSpace_DFile.sql -  Creates a test tablespace, allocates a table to it, populates the table and then queries the table. This can be used to show that the tablespace can be used before and after the move, and that the database objects are unaffected by the move.

0743.Move_TableSpace_Data_File.zip

There are two other related articles:

1. Adding another Data File to Oracle Tablespace used by Enterprise Analyzer Workspace - Has an attached script that can be used to add data files to a table space

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.

Incident # 2616358