目录
linux的oracle更改表空间路径
1、查看当前表空间路径
psql登录sys用户
select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#;
2、复制原有的表空间路径
cp -r /u01/app/oracle/oradata/IFRSDB /u01/app/oracle/oradata/IFRSSIT
cp -r /home/oracle/u01/oradata/ifrsdb /home/oracle/u01/oradata/ifrssit
3、查询表空间状态
sqlplus / as sysdba
select tablespace_name,status from dba_tablespaces;
4、修改表空间
查询表空间：
select 'alter tablespace '||t1.NAME||' offline;' as alertUser,'alter database rename file '''||t2.NAME||''' to '''||replace(t2.NAME,'ifrsdb','ifrssit')||''';' as alertSql
from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#
and t2.name like '/u01/app/oracle/oradata/IFRSDB/%';
4.1、修改表空间为Offline
sqlplus / as sysdba
alter tablespace users offline;
执行：除了SYSTEM、UNDOTBS1不能之间更改
alter tablespace BPL_SPACE offline;
4.2、修改表空间指向地址
alter database rename file '原路径USERS01.DBF' to '文件新路径USERS01.DBF';
根据sys查询更改相应的路径
select 'alter database rename file '''||t2.NAME||''' to '''||replace(t2.NAME,'IFRSDB','IFRSSIT')||''';' as alertSql
from v$tablespace t1,v$datafile t2 where t1.ts# = t2.ts#
and t2.name like '/u01/app/oracle/oradata/IFRSDB/%';
查询如下更改：
alter database rename file '/home/oracle/u01/oradata/ifrsdb/BPL_SPACE.DBF' to '/home/oracle/u01/oradata/ifrssit/BPL_SPACE.DBF';
5、修改表空间为Online
alter tablespace BPL_SPACE online ;