Friday, 27 March 2009

Can one rename a tablespace?

From Oracle 10g Release 1, users can rename tablespaces.
Example:
ALTER TABLESPACE ts1 RENAME TO ts2;

However, you must adhere to the following restrictions:
  • COMPATIBILITY must be set to at least 10.0.1
  • Cannot rename SYSTEM or SYSAUX
  • Cannot rename an offline tablespace
  • Cannot rename a tablespace that contains offline datafiles

For older releases, use the following workaround:

  • Export all of the objects from the tablespace
  • Drop the tablespace including contents
  • Recreate the tablespace
  • Import the objects

No comments:

Post a Comment