ANBOB™

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.

打赏

微信扫一扫，打赏作者吧～