Oracle批改Tablespace的Name
日期：2014-05-16 浏览次数：20380 次
Oracle修改Tablespace的Name
Oracle修改TableSpace的Name
?
??? 在Oracle10g以前，tablespace的name是不可以随意修改的，只能drop掉之后重建，但是在10g之后，oracle加入了修改的功能，除了system和sysaux两个表空间外，其他的表空间都可以改名。今天专门来记录一下这个特性：
?
??? 为TableSpace改名的举例如下：
?
SQL> create tablespace wxq_tbs datafile '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' size 1M;
?
Tablespace created.
SQL> alter tablespace wxq_tbs rename to wxq_tbs2;
?
Tablespace altered.
SQL> select tablespace_name,status from dba_tablespaces;
?
TABLESPACE_NAME????????????????????????????????????????????? STATUS
------------------------ -------------
------------------------ -------------
SYSTEM?????????????????????????????????????????????????????? ONLINE
UNDOTBS1???????????????????????????????????????????????????? ONLINE
SYSAUX?????????????????????????????????????????????????????? ONLINE
TEMP???????????????????????????????????????????????????????? ONLINE
USERS??????????????????????????????????????????????????????? ONLINE
OWB_TBS????????????????????????????????????????????????????? ONLINE
RECOVERY_TBS???????????????????????????????????????????????? ONLINE
STREAM_TBS?????????????????????????????????????????????????? ONLINE
WXQ_TBS2???????????????????????????????????????????????????? ONLINE
SQL> select tablespace_name,file_name,status from dba_data_files;
UNDOTBS1???????????????????????????????????????????????????? ONLINE
SYSAUX?????????????????????????????????????????????????????? ONLINE
TEMP???????????????????????????????????????????????????????? ONLINE
USERS??????????????????????????????????????????????????????? ONLINE
OWB_TBS????????????????????????????????????????????????????? ONLINE
RECOVERY_TBS???????????????????????????????????????????????? ONLINE
STREAM_TBS?????????????????????????????????????????????????? ONLINE
WXQ_TBS2???????????????????????????????????????????????????? ONLINE
SQL> select tablespace_name,file_name,status from dba_data_files;
?
TABLESPACE_N FILE_NAME?????????????????????????????????????????????????????? ? STATUS
------------ ------------------------------ -------------
USERS??????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/users01.dbf????????? AVAILABLE
SYSAUX?????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/sysaux01.dbf???????? AVAILABLE
UNDOTBS1???? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/undotbs01.dbf??????? AVAILABLE
SYSTEM?????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/system01.dbf???????? AVAILABLE
OWB_TBS????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/owb_tbs01.dbf??????? AVAILABLE
RECOVERY_TBS /opt/oracle/product/10.2.0/oradata/wangxiaoqi/recover_tbs.dbf????? AVAILABLE
STREAM_TBS?? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/stream_tbs01.dbf???? AVAILABLE
WXQ_TBS2???? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf????????? AVAILABLE
------------ ------------------------------ -------------
USERS??????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/users01.dbf????????? AVAILABLE
SYSAUX?????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/sysaux01.dbf???????? AVAILABLE
UNDOTBS1???? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/undotbs01.dbf??????? AVAILABLE
SYSTEM?????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/system01.dbf???????? AVAILABLE
OWB_TBS????? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/owb_tbs01.dbf??????? AVAILABLE
RECOVERY_TBS /opt/oracle/product/10.2.0/oradata/wangxiaoqi/recover_tbs.dbf????? AVAILABLE
STREAM_TBS?? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/stream_tbs01.dbf???? AVAILABLE
WXQ_TBS2???? /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf????????? AVAILABLE
?
?
??? 此时，datafile的名字没有改过来，与tablespace不一致，所以需要再改一下，这个过程相对来说比较复杂，要以下面的顺序来修改：
?
??? 1、把相应的tablespace改成read only;
???
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
