由于各种原因,有时需要修改数据库数据文件的位置,例如磁盘空间不足需要扩容...
首先创建示例环境:
创建表空间:
SQL> CREATE TABLESPACE "DMTEST" DATAFILE '/data1/DAMENG/DMTEST1.DBF' SIZE 128 AUTOEXTEND ON NEXT 100 MAXSIZE 10240, '/data1/DAMENG/DMTEST2.DBF' SIZE 128 AUTOEXTEND ON NEXT 100 MAXSIZE 10240 CACHE = NORMAL;
创建用户,并指定默认表空间
SQL> CREATE USER "DMUSER" IDENTIFIED BY "Dameng123" LIMIT FAILED_LOGIN_ATTEMPS 3, PASSWORD_LOCK_TIME 1, PASSWORD_GRACE_TIME 10 DEFAULT TABLESPACE "DMTEST" DEFAULT INDEX TABLESPACE "DMTEST";
用户授权
SQL> GRANT "PUBLIC","RESOURCE","VTI" TO "DMUSER";
切换为"DMUSER"用户,创建测试表,插入数据
SQL> conn DMUSER/Dameng123;
SQL> select user;
SQL> create table t1 (id int,name varchar2(10));
SQL> insert into t1 values (1,'aa');
SQL> insert into t1 values (2,'bb');
SQL> insert into t1 values (3,'cc');
SQL> commit;
SQL> select * from t1;
实操演示:
现在数据文件存放在根目录下,我们要将它移动到/data1下
修改表空间的数据文件物理位置
方法一(数据库运行时):
1. 将表空间脱机
(1)查看表空间信息
查看表空间类型和状态(STATUS为0时是联机状态,为1时是脱机状态),需用DBA权限查看
SQL> select tablespace_name,status,contents from dba_tablespaces;
查询表空间的路径
SQL> select file_id,file_name,tablespace_name from dba_data_files;
将表空间脱机
需注意几点:
当有事务未提交时,将表空间脱机时会报以下错误
SQL> ALTER TABLESPACE "DMTEST" OFFLINE;
ALTER TABLESPACE "DMTEST" OFFLINE;
[-6407]:锁超时.
已用时间: 00:00:12.020. 执行号:0
确保当前用户有修改表空间的权限,否则会报以下错误
SQL> ALTER TABLESPACE "DMTEST" OFFLINE;
ALTER TABLESPACE "DMTEST" OFFLINE;
第1 行附近出现错误[-5566]:没有修改表空间权限.
已用时间: 0.809(毫秒). 执行号:0
正常执行表空间脱机
2.修改表空间路径
alter tablespace "DMTEST" rename datafile '/DM/dmdbms/data/DAMENG/DMTEST1.DBF' to '/data1/DAMENG/DMTEST1.DBF';
3.将表空间联机
ALTER TABLESPACE "DMTEST" ONLINE;
4.查看服务器上的数据文件位置是否移动
数据文件已经移动到了/data1/DAMENG/目录下
原/DM/dmdbms/data/DAMENG/目录下已经只剩DMTEST2.DBF数据文件
5.查看表数据正常
方法二(停止数据库服务时):
1.停止数据库服务
2.修改数据库控制文件
(1)将数据库转换控制文件为文本文件
(2)修改转换后的txt文件
(3)修改完控制文件里DMTEST2.DBF的路径以后,将控制文件由文本文件转回为控制文件。
(4)拷贝表空间DMTEST2.DBF到修改的新路径下
(5)启动数据库验证查看
验证成功。
附:
注意:临时表空间路径无法在数据库服务启动时修改,无法执行联机脱机操作。在数据库服务停止时,如果要修改临时表空间数据文件的路径,无需修改控制文件,因为在控制文件中并没有保存临时表空间的路径信息。临时表空间的路径信息在dm.ini中修改,在dm.ini中有一个参数为:TEMP_PATH,默认路径为数据库目录下
修改临时表空间路径直接修改dm.ini参数TEMP_PATH,修改完后重启数据库服务即可.
附:第一次操作不建议生产环境直接操作,测试环境演练无误后再整理自己的操作流程进行生产变更!!!