首页 » ORACLE » 表空间重命名(rename tablespace name)

表空间重命名(rename tablespace name)

oracle 10g 推出了一个强大的特性,你可以修改已存在表空间的名称了

for examples:

SQL> create tablespace tbsold;

Tablespace created.

SQL> conn anbob/anbob
Connected.
SQL> create table testtbs (id int) tablespace tbsold;

Table created.

SQL> conn / as sysdba;
Connected.

SQL> col table_name for a10
SQL> select table_name,tablespace_name from dba_tables where table_name=’TESTTBS’

TABLE_NAME TABLESPACE_NAME
———- ————————————————————
TESTTBS TBSOLD

SQL> alter tablespace tbsold rename to tbsnew;

Tablespace altered.

SQL> select table_name,tablespace_name from dba_tables where table_name=’TESTTBS’;

TABLE_NAME TABLESPACE_NAME
———- ————————————————————
TESTTBS TBSNEW

总结:

alter tablespace :oldname rename to :newname

However, you must follow the rules when renaming a tablespace:

  1. You must set compatibility level to at least 10.0.1.
  2. You cannot rename the SYSTEM or SYSAUX tablespaces.
  3. You cannot rename an offline tablespace.
  4. You cannot rename a tablespace that contains offline datafiles.
  5. Renaming a tablespace does not changes its tablespace identifier.
  6. Renaming a tablespace does not change the name of its datafiles.

Tablespace rename provides the following benefits:

  • It simplifies the process of tablespace migration within a database.
  • It simplifies the process of transporting a tablespace between two databases.
打赏

目前这篇文章有3条评论(Rss)评论关闭。

  1. Neal Schowalter | #1
    2011-12-21 at 05:16

    Thanks for all of your effort on this site. My aunt take interest in engaging in internet research and it is simple to grasp why. Most of us hear all relating to the powerful manner you provide advantageous steps via your website and as well as encourage response from other ones on the area of interest and our favorite simple princess is now starting to learn a lot of things. Take advantage of the rest of the year. Your doing a brilliant job.

  2. Minna Uhde | #2
    2011-05-25 at 17:16

    Terrific work! This is the type of information that should be shared around the web. Shame on the search engines for not positioning this post higher!

  3. daigoumee | #3
    2011-05-22 at 05:11

    nice post. thanks.