个人感觉DB2更改文件名的方法有点麻烦,虽然步骤也不是很多,可能是习惯于ORACLE的方法造成的。
在ORACLE中更改数据文件的路径是比较简单的事情,尤其在12C之后,如下一个简单的命令就可以更改文件路径:
ALTER DATABASE MOVE DATAFILE 4 TO '/data/orcl/user01.dbf'
即便在12C之前移动文件也算相对简单:
将相关的文件离线--ALTER DATABASE DATAFILE 4 OFFLINE
在操作系统级别更改文件位置或名称 --mv /data1/orcl/user01.dbf /data/orcl/user01.dbf
更新控制文件指针 --alter database rename datafile 4 to '/data/orcl/user01.dbf'
将文件在线--ALTER DATABASE DATAFILE 4 ONLINE
在DB2中移动文件或者改名需要借助于备份,这有点蛋疼,对于从ORACLE转到DB2的数据库工程师来说,可能不太适应。
下面简单看一下这个操作步骤:
下面是将表空间TEST的文件修改文件路径并且命名。
[yansp@db2server ~]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUX 9.7.0
SQL authorization ID = YANSP
Local database alias = TESTDB
[yansp@db2server ~]$ db2 list tablespace containers for 7
Tablespace Containers for Tablespace 7
Container ID = 0
Name = /dbauto/manual/test1.dbf
Type = File
Container ID = 1
Name = /dbauto/manual/test2.dbf
Type = File
表空间TEST有2个容器test1.dbf和test2.dbf,下面我们将其移动到上层目录,并且修改文件名。
[yansp@db2server ~]$ db2 restore database testdb tablespace \(test\) from '/tmp' taken at 20130219003935 redirect
SQL1277W A redirected restore operation is being performed. Table space
configuration can now be viewed and table spaces that do not use automatic
storage can have their containers reconfigured.
DB20000I The RESTORE DATABASE command completed successfully.
[yansp@db2server ~]$ db2 "set tablespace containers for 7 using (file '/dbauto/test01.dbf' 2000,file '/dbauto/test02.dbf' 2000)"
DB20000I The SET TABLESPACE CONTAINERS command completed successfully.
[yansp@db2server ~]$ db2 restore database testdb continue
DB20000I The RESTORE DATABASE command completed successfully.
[yansp@db2server ~]$ db2 rollforward database testdb to end of logs and stop
Rollforward Status
Input database alias = testdb
Number of nodes have returned status = 1
Node number = 0
Rollforward status = not pending
Next log file to be read =
Log files processed = -
Last committed transaction = 1970-01-01-00.00.00.000000 UTC
DB20000I The ROLLFORWARD command completed successfully.
[yansp@db2server ~]$ db2 list tablespace containers for 7
SQL1024N A database connection does not exist. SQLSTATE=08003
[yansp@db2server ~]$ db2 connect to testdb
Database Connection Information
Database server = DB2/LINUX 9.7.0
SQL authorization ID = YANSP
Local database alias = TESTDB
[yansp@db2server ~]$ db2 list tablespace containers for 7
Tablespace Containers for Tablespace 7
Container ID = 0
Name = /dbauto/test01.dbf
Type = File
Container ID = 1
Name = /dbauto/test02.dbf
Type = File
阅读(5367) | 评论(0) | 转发(0) |