Oracle表空间-移动
发布时间：2020-12-12 14:26:46 所属栏目：百科 来源：网络整理
导读：1、 alter tablespace 表空间名 rename datafile 'eeee','eeee'.... to 'ssss','sssss'.... 该语句只适用于上面没有活动的还原数据或临时段的非系统表空间中的数据。表空间要脱机状态 2、alter databases 数据库名 rename file 'ssss'.. to 'ddd'... 该语言
|
1、 alter tablespace 表空间名 rename datafile 'eeee','eeee'.... to 'ssss','sssss'.... 该语句只适用于上面没有活动的还原数据或临时段的非系统表空间中的数据。表空间要脱机状态 2、alter databases 数据库名 rename file 'ssss'.. to 'ddd'... 该语言适用于系统表空间和不能置为脱机的表空间中的数据文件。 移动非系统表空间 users表空间 1、查看表空间： SQL> select * from dba_data_files; FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS -------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- ------------- C:ORACELBASEORADATAORCLUSERS01.DBF 4 USERS 332922880 40640 AVAILABLE 4 YES 3435972198 4194302 160 331874304 40512 ONLINE C:ORACELBASEORADATAORCLUNDOTBS01.DBF 3 UNDOTBS1 104857600 12800 AVAILABLE 3 YES 3435972198 4194302 640 103809024 12672 ONLINE C:ORACELBASEORADATAORCLSYSAUX01.DBF 2 SYSAUX 566231040 69120 AVAILABLE 2 YES 3435972198 4194302 1280 565182464 68992 ONLINE C:ORACELBASEORADATAORCLSYSTEM01.DBF 1 SYSTEM 713031680 87040 AVAILABLE 1 YES 3435972198 4194302 1280 711983104 86912 SYSTEM C:ORACELBASEORADATAORCLEXAMPLE01.DBF 5 EXAMPLE 104857600 12800 AVAILABLE 5 YES 3435972198 4194302 80 103809024 12672 ONLINE C:ORCL1122DISK5LIANXI01.DBF 6 LIANXI 52428800 6400 AVAILABLE 6 NO 0 0 0 52355072 6391 ONLINE C:ORCL1122DISK5LIANXI02.DBF 7 LIANXI 52428800 6400 AVAILABLE 7 NO 0 0 0 52355072 6391 ONLINE C:ORCL1122DISK5LIANXI_UNDO.DBF 8 LIANXI_UNDO 20971520 2560 AVAILABLE 8 NO 0 0 0 19922944 2432 ONLINE C:ORCL1122DISK5LIANXI_INDEX.DBF 9 LIANXI_INDEX 26214400 3200 AVAILABLE 9 YES 3435972198 4194302 256 25165824 3072 ONLINE C:ORCL1122DISK5LIANXI_INDEX02.DBF 10 LIANXI_INDEX 15728640 1920 AVAILABLE 10 NO 0 0 0 14680064 1792 ONLINE 10 rows selected 2、脱机 SQL> alter tablespace users offline; Tablespace altered SQL> select TABLESPACE_NAME,status from dba_tablespaces where tablespace_name='USERS'; TABLESPACE_NAME STATUS ------------------------------ --------- USERS OFFLINE 3、复制数据文件到其他位置： SQL> host copy C:ORACELBASEORADATAORCLUSERS01.DBF C:orcl1122disk6; 1 file(s) copied 4、执行移动命令 SQL> alter tablespace users rename 2 datafile 'C:ORACELBASEORADATAORCLUSERS01.DBF' 3 to 'C:orcl1122disk6USERS01.DBF'; Tablespace altered 5、联机 SQL> alter tablespace users online; Tablespace altered 6、查询 users 位置已更改 SQL> select * from dba_data_files; FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUTOEXTENSIBLE MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS -------------------------------------------------------------------------------- ---------- ------------------------------ ---------- ---------- --------- ------------ -------------- ---------- ---------- ------------ ---------- ----------- ------------- C:ORCL1122DISK6USERS01.DBF 4 USERS 332922880 40640 AVAILABLE 4 YES 3435972198 4194302 160 331874304 40512 ONLINE C:ORACELBASEORADATAORCLUNDOTBS01.DBF 3 UNDOTBS1 104857600 12800 AVAILABLE 3 YES 3435972198 4194302 640 103809024 12672 ONLINE C:ORACELBASEORADATAORCLSYSAUX01.DBF 2 SYSAUX 566231040 69120 AVAILABLE 2 YES 3435972198 4194302 1280 565182464 68992 ONLINE C:ORACELBASEORADATAORCLSYSTEM01.DBF 1 SYSTEM 713031680 87040 AVAILABLE 1 YES 3435972198 4194302 1280 711983104 86912 SYSTEM C:ORACELBASEORADATAORCLEXAMPLE01.DBF 5 EXAMPLE 104857600 12800 AVAILABLE 5 YES 3435972198 4194302 80 103809024 12672 ONLINE C:ORCL1122DISK5LIANXI01.DBF 6 LIANXI 52428800 6400 AVAILABLE 6 NO 0 0 0 52355072 6391 ONLINE C:ORCL1122DISK5LIANXI02.DBF 7 LIANXI 52428800 6400 AVAILABLE 7 NO 0 0 0 52355072 6391 ONLINE C:ORCL1122DISK5LIANXI_UNDO.DBF 8 LIANXI_UNDO 20971520 2560 AVAILABLE 8 NO 0 0 0 19922944 2432 ONLINE C:ORCL1122DISK5LIANXI_INDEX.DBF 9 LIANXI_INDEX 26214400 3200 AVAILABLE 9 YES 3435972198 4194302 256 25165824 3072 ONLINE C:ORCL1122DISK5LIANXI_INDEX02.DBF 10 LIANXI_INDEX 15728640 1920 AVAILABLE 10 NO 0 0 0 14680064 1792 ONLINE 10 rows selected SQL> 移动系统表空间： 1、关闭数据库 shutdown immediate; 2、启动例程 startup mount; 3、复制数据文件到其他位置 host copy 'c:ddddddddd.dbf' 'd:sss' 4、移动命令 alter database rename file 'c:dddddddd.dbf' to 'd:sssddd.dbf' 5、数据库open alter database open； 6、查询 位置已更改 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |