Home > oracle, SQL*Plus > Rename a tablespace/datafile in Oracle 9i/10g

Rename a tablespace/datafile in Oracle 9i/10g

To rename a tablespace in Oracle 10g is very easy:

SQL> alter tablespace test1 rename to test2;

The rename only works in Oracle 10, and updates the dictionary entries for user quotas (user_ts_quotas). There is no simple way to rename a tablespace in Oracle 9, you have to use exp/imp, so, you cannot issue this online.

If you want to delete a tablespace, it’s better to first unassign all quotas on that tablespace, because if you later create a tablespace with the same name, the old quotas are still active:

SQL> alter user <username> quota 0 on <tablespace_name>;
SQL> drop tablespace <tablespace_name> including contents and datafiles;

If you want to rename also its datafile(s) you have to do it via SQL*Plus and linux console:

SQL> ALTER TABLESPACE test2 OFFLINE;
linux> mv 'oldfile.dbf' 'newfile.dbf'
SQL>  ALTER TABLESPACE test2 RENAME datafile '/path_to_old_datafile/oldfile.dbf' TO '/path_to_new_datafile/newfile.dbf'

SQL>  ALTER TABLESPACE test2 ONLINE;
Advertisements
Categories: oracle, SQL*Plus Tags: , , ,
  1. February 15, 2014 at 20:23

    Good post ! I addressed this subject in this post showing how do to it with rman to reduce downtime.
    http://www.dba-scripts.com/2014/01/27/oracle-relocate-a-datafile/

    Like

    • February 17, 2014 at 14:10

      Thanks Cyrille for your comment. Good luck with your blog!

      Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: