IN ORACLE MILIEU …

Oracle 9i:

SQL> CREATE TABLESPACE test1 DATAFILE ‘/u01/app/orsbox/proddata/test1.dbf’ size 500M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

For Renaming the tablespace, we don’t have a direct command in 9i. For that we need to follow the below steps.

1) Export all of the objects from the tablespace

[orsbox@ocvmrh2124 9.2.0]$ exp system/manager FILE=test1.dmp LOG=test1.log TABLESPACES=test1

Export: Release 9.2.0.3.0 – Production on Thu Jul 12 01:02:37 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 – Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export selected tablespaces …
For tablespace TEST1 …
. exporting cluster definitions
. exporting table definitions
. exporting referential integrity constraints
. exporting triggers
Export terminated successfully without warnings.

2) Drop the tablespace including contents

SQL> drop tablespace test1 including contents;

Tablespace dropped.

3) Recreate the tablespace

SQL> CREATE TABLESPACE test2 DATAFILE ‘/u01/app/orsbox/proddata/test1.dbf’ size 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

4) Import the objects

[orsbox@ocvmrh2124 9.2.0]$ imp system/manager FILE=test1.dmp LOG=test1.log FULL=Y TABLESPACES=test2

Import: Release 9.2.0.3.0 – Production on Thu Jul 12 01:27:47 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.3.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 – Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
. importing SYSTEM’s objects into SYSTEM
Import terminated successfully without warnings.
[orsbox@ocvmrh2124 9.2.0]$

Oracle Database 10g:

In Oracle database 10g we can easily rename a tablespace. Renaming of tablespace has been simplified to an extent of a single ALTER TABLESPACE command. Lets see how renaming of tablespace can be done in 10g.

SQL> CREATE TABLESPACE test1 DATAFILE ‘/slot/ems1177/oracle/db/apps_st/data/test1.dbf’ size 500M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> alter tablespace test1 rename to test2;

Tablespace altered.