表空间改名字:(表空间状态必须为online、read write)
alter tablespace oldname to newname;
数据文件改名字:
1.查看现有文件位置
2.offline
3.复制到新的名称
4.alter database rename file '...old' to '...new';
5.online
6.查看dba_data_files 验证
一:不能脱机的表空间
1.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test01.dbf
ONLINE
2.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' offline;
Database altered.
3.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test01.dbf
RECOVER
[oracle@localhost orcl]$ ls
control01.ctl FILE5.log redo01.log redo04.log sysaux01.dbf test01.dbf
control03.ctl FILE6.log redo02.log redo05.log system01.dbf undotbs01.dbf
FILE4.log hclob01.dbf redo03.log rmantbs01.dbf temp01.dbf users01.dbf
[oracle@localhost orcl]$ cp test01.dbf test02.dbf
[oracle@localhost orcl]$ ls
control01.ctl FILE6.log redo03.log sysaux01.dbf test02.dbf
control03.ctl hclob01.dbf redo04.log system01.dbf undotbs01.dbf
FILE4.log redo01.log redo05.log temp01.dbf users01.dbf
FILE5.log redo02.log rmantbs01.dbf test01.dbf
4.
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/test01.dbf' to '/u01/app/oracle/oradata/orcl/test02.dbf';
Database altered.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
RECOVER
5.
SQL> recover datafile '/u01/app/oracle/oradata/orcl/test02.dbf';
Media recovery complete.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
OFFLINE
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' online;
Database altered.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
ONLINE
二、在open状态下且处于archive模式:
将数据文件所在的表空间离线
alter tablespace test offline;
在操作系统下将文件移动到另外一个位置,或重命名(建议使用cp)
host mv /u01/app/oracle/oradata/orcl/test02.dbf /u01/app/oracle/oradata/orcl/test03.dbf
修改控制文件,将数据文件的指针重新指向到另一个位置(执行rename)
alter tablespace test rename datafile '/u01/app/oracle/oradata/orcl/test02.dbf'
to '/u01/app/oracle/oradata/orcl/test03.dbf';
表空间在线
alter tablespace test online
三:
SQL> startup mount;
ORACLE instance started.
[oracle@localhost orcl]$ cp test02.dbf test01.dbf
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
ONLINE
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/test02.dbf' to '/u01/app/oracle/oradata/orcl/test01.dbf';
Database altered.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test01.dbf
ONLINE
SQL> alter database open;
Database altered.
alter tablespace oldname to newname;
数据文件改名字:
1.查看现有文件位置
2.offline
3.复制到新的名称
4.alter database rename file '...old' to '...new';
5.online
6.查看dba_data_files 验证
一:不能脱机的表空间
1.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test01.dbf
ONLINE
2.
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' offline;
Database altered.
3.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test01.dbf
RECOVER
[oracle@localhost orcl]$ ls
control01.ctl FILE5.log redo01.log redo04.log sysaux01.dbf test01.dbf
control03.ctl FILE6.log redo02.log redo05.log system01.dbf undotbs01.dbf
FILE4.log hclob01.dbf redo03.log rmantbs01.dbf temp01.dbf users01.dbf
[oracle@localhost orcl]$ cp test01.dbf test02.dbf
[oracle@localhost orcl]$ ls
control01.ctl FILE6.log redo03.log sysaux01.dbf test02.dbf
control03.ctl hclob01.dbf redo04.log system01.dbf undotbs01.dbf
FILE4.log redo01.log redo05.log temp01.dbf users01.dbf
FILE5.log redo02.log rmantbs01.dbf test01.dbf
4.
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/test01.dbf' to '/u01/app/oracle/oradata/orcl/test02.dbf';
Database altered.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
RECOVER
5.
SQL> recover datafile '/u01/app/oracle/oradata/orcl/test02.dbf';
Media recovery complete.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
OFFLINE
SQL> alter database datafile '/u01/app/oracle/oradata/orcl/test02.dbf' online;
Database altered.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
ONLINE
二、在open状态下且处于archive模式:
将数据文件所在的表空间离线
alter tablespace test offline;
在操作系统下将文件移动到另外一个位置,或重命名(建议使用cp)
host mv /u01/app/oracle/oradata/orcl/test02.dbf /u01/app/oracle/oradata/orcl/test03.dbf
修改控制文件,将数据文件的指针重新指向到另一个位置(执行rename)
alter tablespace test rename datafile '/u01/app/oracle/oradata/orcl/test02.dbf'
to '/u01/app/oracle/oradata/orcl/test03.dbf';
表空间在线
alter tablespace test online
三:
SQL> startup mount;
ORACLE instance started.
[oracle@localhost orcl]$ cp test02.dbf test01.dbf
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test02.dbf
ONLINE
SQL> alter database rename file '/u01/app/oracle/oradata/orcl/test02.dbf' to '/u01/app/oracle/oradata/orcl/test01.dbf';
Database altered.
SQL> select name,status from v$datafile;
/u01/app/oracle/oradata/orcl/test01.dbf
ONLINE
SQL> alter database open;
Database altered.