环境:Oracle Linux Server release 6.4 oracle 11.2.0.1.0
问题描述与分析:经常会有在建立表空间时,在数据文件命名时中间或结尾处多个空格,该如何将这名字里面的空格去掉呢,下面我们实验如何去掉。改名思路:将表空间offline,系统级rename,数据库级rename,表空间online,检查数据。
1. 创建测试表空间
sys@ORCL>
create tablespace aaa datafile '/11g/app/oracle/oradata/ORCL/aaa bbb.dbf' size 10m;
Tablespace created.
2. 将表空间offline
sys@ORCL>
alter tablespace aaa offline;
Tablespace altered.
3. 直接修改数据文件名字报错
sys@ORCL>
alter database rename file '/11g/app/oracle/oradata/ORCL/aaa bbb.dbf' to '/11g/app/oracle/oradata/ORCL/aaabbb.dbf';
alter database rename file '/11g/app/oracle/oradata/ORCL/aaa bbb.dbf' to '/11g/app/oracle/oradata/ORCL/aaabbb.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 8 - new file '/11g/app/oracle/oradata/ORCL/aaabbb.dbf' not
found
ORA-01110: data file 8: '/11g/app/oracle/oradata/ORCL/aaa bbb.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
4. 修改操作系统级数据文件名字
[oracle@test ORCL]$
mv aaa\ bbb.dbf aaabbb.dbf
[oracle@test ORCL]$
ls
aaabbb.dbf
redo01.log SYSAUX01.DBF TEST.DBF wmis01.dbf
control01.ctl redo02.log SYSTEM01.DBF UNDOTBS01.DBF
EXAMPLE01.DBF redo03.log temptbs01.dbf USERS01.DBF
5. 修改数据库级数据文件名字
sys@ORCL>
alter database rename file '/11g/app/oracle/oradata/ORCL/aaa bbb.dbf' to '/11g/app/oracle/oradata/ORCL/aaabbb.dbf';
Database altered.
6. 将数据文件online
sys@ORCL>
alter tablespace aaa online;
Tablespace altered.
sys@ORCL>select name from v$datafile where file#=8;
sys@ORCL>select name from v$datafile where file#=8;
NAME
----------------------------------------------------------------------------------------------------
/11g/app/oracle/oradata/ORCL/
aaabbb.dbf
总结:此次测试主要是在系统级改名字与在数据库级改名字,谁在前谁在后问题。上面我举了错误顺序的例子和报错。生产库改前最好还是弄个备份,DBA的救命稻草,有备无患!
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26148431/viewspace-1666210/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26148431/viewspace-1666210/