`

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;
 
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
 
 
    此时，datafile的名字没有改过来，与tablespace不一致，所以需要再改一下，这个过程相对来说比较复杂，要以下面的顺序来修改：
 
    1、把相应的tablespace改成read only;
    2、把需要修改的datafile置为offline;
    3、在操作系统中改名
    4、alter database rename file .. to ..;
    5、把相应的datafile置为online;
    6、把相应tablespace改成read write;
 
    具体操作如下：
 
SQL> alter database rename file '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' to '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf';
alter database rename file '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' to '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01121: cannot rename database file 11 - file is in use or recovery
ORA-01110: data file 11: '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf'
 


SQL> alter tablespace wxq_tbs2 read only;
 
Tablespace altered.
 
SQL> alter database datafile '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' offline;
 
Database altered.
 
SQL> host mv /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf /opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf;
 
SQL> host ls -l /opt/oracle/product/10.2.0/oradata/wangxiaoqi/
total 6115528
-rw-r----- 1 oracle oinstall 1073750016 Jul 28 01:35 owb_tbs01.dbf
-rw-r----- 1 oracle oinstall   26222592 Jul 28 01:35 recover_tbs.dbf
-rw-r----- 1 oracle oinstall  209723392 Jul 28 01:35 stream_tbs01.dbf
-rw-r----- 1 oracle oinstall  471867392 Jul 28 16:05 sysaux01.dbf
-rw-r----- 1 oracle oinstall  566239232 Jul 28 16:08 system01.dbf
-rw-r----- 1 oracle oinstall   31465472 Jul 27 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall  513810432 Jul 28 16:10 undotbs01.dbf
-rw-r----- 1 oracle oinstall    5251072 Jul 28 10:14 users01.dbf
-rw-r----- 1 oracle oinstall    1056768 Jul 28 16:08 wxq_tbs2.dbf
 
SQL> alter database rename file '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs.dbf' to '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf';
 
Database altered.
 
SQL> alter database datafile '/opt/oracle/product/10.2.0/oradata/wangxiaoqi/wxq_tbs2.dbf' online;
 
Database altered.
 
SQL> alter tablespace wxq_tbs2 read write;
 
Tablespace altered.
 
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_tbs2.dbf         AVAILABLE
 
8 rows selected.

 
    至此，就全部修改完成了。再强调一下，只有在10g中才能够修改，10g以前是不可以的。
分享到：
| 线程安全与不安全
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle 数据库表空间巡检步骤

    WHERE t.tablespace_name = d.tablespace_name GROUP BY t.tablespace_name; 2：查看物理表空间 SELECT tablespace_name,file_id,file_name, round(bytes / (1024 * 1024), 0) total_space FROM dba_data_files ...

    Oracle维护常用SQL语句汇总

    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 1、查看表空间的名称及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, ...

    oracle空间使用率

    　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME 　ORDER BY 1; 　--查询表空间的free space 　select tablespace_name, 　count(*) as extends, 　round(sum(bytes) / 1024 / 1024, 2) as MB, 　sum(blocks) as ...

    oracle查询表空间大小

    select a.TABLESPACE_NAME "TableSpace Name", round(a.BYTES / 1024 / 1024) "MB Allocated", round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) "MB Used", nvl(round(b.BYTES / 1024 / 1024), 0) "MB Free", ...

    Oracle修改表空间大小的方法

    本文讲述了Oracle修改表空间大小的方法。分享给大家供大家参考，具体如下： 1）查看各表空间分配情况 SQL> select tablespace_name, sum(bytes) / 1024 / 1024 from dba_data_files group by tablespace_name; ...

    最全的oracle常用命令大全.txt

    WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 7、查看数据库库对象 select owner, object_type, status, count(*) count# from all_objects group by owner, object_...

    Oracle RMAN还原时set newname文件名有空格报错的解决方法

    在Oracle 11g之前，RMAN只支持SET NEWNAME FOR DATAFILE，在Oracle 11g中增加了SET NEWNAME FOR TEMPFILE/SET NEWNAME FOR TABLESPACE/SET NEWNAME FOR DATABASE的命令。 优先顺序如下：  1.SET NEWNAME FOR DATA...

    oracle中查看表空间与对应物理文件，用户，表，使用情况

    select b.tablespace_name 表空间, c.owner 用户, c.segment_name 表名, b.file_name 物理文件名, sum(nvl(b.bytes, 0)) / 1024 / 1024 总共大小M, round((sum(nvl(b.bytes, 0)) - sum(nvl(a.bytes, 0))) ...

    2010年oracle命令176页完整版型

    create tablespace tablespace_name logging datafile '/XXX/xxx/datafile_name1.ora' size 4095M; 临时表空间： create temporay tablespae tmp_tablespace_name tempfile '/XXX/xxx/datafile_name1.ora' size 4095...

    ORACLE精品脚本笔记

    找ORACLE字符集 <br>select * from sys.props$ where name='NLS_CHARACTERSET'; <br>15. 监控 MTS <br>select busy/(busy+idle) "shared servers busy" from v$dispatcher; <br>此值大于0.5时，...

    linux下的Oracle数据库安装，卸载和静默安装

    undo_tablespace=undotbs audit_file_dest=$ORACLE_BASE/admin/orcl/adump background_dump_dest=$ORACLE_BASE/admin/orcl/bdump core_dump_dest=$ORACLE_BASE/admin/orcl/cdump user_dump_dest=$ORACLE_BASE/admin/...

    Oracle表空间查看sql使用情况

    select a.tablespace_name tablespace_name ,nvl(ceil((1 - b.free / a.total) * 100), 100) usage_of_tablespace% ,nvl(b.free, 0) left_space(M) ,c.extent_management Extent_management from (select tables

    Oracle常用问题1000问

    select * from user_tables中有个字段TABLESPACE_NAME，（oracle）; select * from dba_segments where …; desc user_tables; select tablespace_name from user_tables where table_name='s_emp'; 55. 怎么...

    oracle 10g问题总结

    oracle 10g问题总结 1. 查询oracle中所有用户信息 select * from dba_users; 2. 只查询用户和密码 select username,password from dba_users; 3. 查询当前用户信息 select * from dba_ustats; 4. 查询...

    Oracle 主要配置文件介绍

    LIST = (SID_DESC = (SID_NAME = PLSExtProc1) (ORACLE_HOME = /u01/app/oracle/product/8.1.7) (PROGRAM = extproc) ) (SID_DESC = (GLOBAL_DBNAME = cams) (ORACLE_HOME = /u01/...

    Oracle常用技术资料合集.zip

    awr_metric_name.txt bind_noused.txt cursor_purge.txt ddl_metadata.txt dml_get fra_get param_get.txt redo_switch.txt segment_size.txt session_kill.txt session_sid.txt session_spid.txt shared_pool_free....

    Oracle练习.txt

    8.怎么可以看到数据库有多少个tablespace? select * from dba_tablespaces; 9.如何显示当前连接用户? SHOW USER 10.如何测试SQL语句执行所用的时间? SQL>set timing on ; 11.怎么把select出来的结果导到一个文本...

    Oracle最常用的语句

    Oracle 最常用的命令 1．登陆系统用户 ：sqlplus 然后输入系统用户名和密码 　登陆别的用户 ： conn 用户名/密码; 2.创建表空间 　create tablespace 空间名 　datafile 'c:\空间名' size 15M --表空间的存放路径,...

    oracle实验报告

    undo tablespace UNDOTBS1 datafile 'E:\app\oracle\oradata\mydb\undotbs1.dbf' size 10m character set ZHS16GBK national character set AL16UTF16 7、创建额外表空间 alter user system temporary ...

    linux系统给oracle数据库增加新的实例.pdf

    cd $ORACLE_HOME/dbs cp initorcl.ora initorcl.ora #复制⼀个原有的ora⽂件 vim initorcl.ora #修改为响应信息 ORACLE_BASE要改为绝对路径 不然会报错 3、创建密码⽂件 orapwd file=$ORACLE_BASE/product/10.2.0/db...

Magicbox
Global site tag (gtag.js) - Google Analytics