Oracle表空间-移动

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:\ORACEL\BASE\ORADATA\ORCL\USERS01.DBF                                                   4 USERS                           332922880      40640 AVAILABLE            4 YES            3435972198    4194302          160  331874304       40512 ONLINE
C:\ORACEL\BASE\ORADATA\ORCL\UNDOTBS01.DBF                                                 3 UNDOTBS1                        104857600      12800 AVAILABLE            3 YES            3435972198    4194302          640  103809024       12672 ONLINE
C:\ORACEL\BASE\ORADATA\ORCL\SYSAUX01.DBF                                                  2 SYSAUX                          566231040      69120 AVAILABLE            2 YES            3435972198    4194302         1280  565182464       68992 ONLINE
C:\ORACEL\BASE\ORADATA\ORCL\SYSTEM01.DBF                                                  1 SYSTEM                          713031680      87040 AVAILABLE            1 YES            3435972198    4194302         1280  711983104       86912 SYSTEM
C:\ORACEL\BASE\ORADATA\ORCL\EXAMPLE01.DBF                                                 5 EXAMPLE                         104857600      12800 AVAILABLE            5 YES            3435972198    4194302           80  103809024       12672 ONLINE
C:\ORCL1122\DISK5\LIANXI01.DBF                                                            6 LIANXI                           52428800       6400 AVAILABLE            6 NO                      0          0            0   52355072        6391 ONLINE
C:\ORCL1122\DISK5\LIANXI02.DBF                                                            7 LIANXI                           52428800       6400 AVAILABLE            7 NO                      0          0            0   52355072        6391 ONLINE
C:\ORCL1122\DISK5\LIANXI_UNDO.DBF                                                         8 LIANXI_UNDO                      20971520       2560 AVAILABLE            8 NO                      0          0            0   19922944        2432 ONLINE
C:\ORCL1122\DISK5\LIANXI_INDEX.DBF                                                        9 LIANXI_INDEX                     26214400       3200 AVAILABLE            9 YES            3435972198    4194302          256   25165824        3072 ONLINE
C:\ORCL1122\DISK5\LIANXI_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:\ORACEL\BASE\ORADATA\ORCL\USERS01.DBF   C:\orcl1122\disk6;
        1 file(s) copied

4、执行移动命令
SQL> alter tablespace users rename
  2  datafile 'C:\ORACEL\BASE\ORADATA\ORCL\USERS01.DBF'
  3  to 'C:\orcl1122\disk6\USERS01.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:\ORCL1122\DISK6\USERS01.DBF                                                             4 USERS                           332922880      40640 AVAILABLE            4 YES            3435972198    4194302          160  331874304       40512 ONLINE
C:\ORACEL\BASE\ORADATA\ORCL\UNDOTBS01.DBF                                                 3 UNDOTBS1                        104857600      12800 AVAILABLE            3 YES            3435972198    4194302          640  103809024       12672 ONLINE
C:\ORACEL\BASE\ORADATA\ORCL\SYSAUX01.DBF                                                  2 SYSAUX                          566231040      69120 AVAILABLE            2 YES            3435972198    4194302         1280  565182464       68992 ONLINE
C:\ORACEL\BASE\ORADATA\ORCL\SYSTEM01.DBF                                                  1 SYSTEM                          713031680      87040 AVAILABLE            1 YES            3435972198    4194302         1280  711983104       86912 SYSTEM
C:\ORACEL\BASE\ORADATA\ORCL\EXAMPLE01.DBF                                                 5 EXAMPLE                         104857600      12800 AVAILABLE            5 YES            3435972198    4194302           80  103809024       12672 ONLINE
C:\ORCL1122\DISK5\LIANXI01.DBF                                                            6 LIANXI                           52428800       6400 AVAILABLE            6 NO                      0          0            0   52355072        6391 ONLINE
C:\ORCL1122\DISK5\LIANXI02.DBF                                                            7 LIANXI                           52428800       6400 AVAILABLE            7 NO                      0          0            0   52355072        6391 ONLINE
C:\ORCL1122\DISK5\LIANXI_UNDO.DBF                                                         8 LIANXI_UNDO                      20971520       2560 AVAILABLE            8 NO                      0          0            0   19922944        2432 ONLINE
C:\ORCL1122\DISK5\LIANXI_INDEX.DBF                                                        9 LIANXI_INDEX                     26214400       3200 AVAILABLE            9 YES            3435972198    4194302          256   25165824        3072 ONLINE
C:\ORCL1122\DISK5\LIANXI_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:\dddddd\ddd.dbf'  'd:\sss'
4、移动命令
 alter database rename file 'c:\ddddd\ddd.dbf' to 'd:\sss\ddd.dbf'
5、数据库open
alter database open；
6、查询  
 位置已更改