Moving Data Files:
ALTER TABLESPACE
• The tablespace must be offline.
• The target data files must exist.
ALTER TABLESPACE userdata
RENAME
DATAFILE '/u01/oradata/userdata01.dbf'
TO '/u01/oradata/userdata01.dbf';
1.查看表空间对应的数据文件
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- --------------------
D:\APP\WWANG\ORADATA\MDSP\USERS01.DBF USERS
D:\APP\WWANG\ORADATA\MDSP\UNDOTBS01.DBF UNDOTBS1
D:\APP\WWANG\ORADATA\MDSP\SYSAUX01.DBF SYSAUX
D:\APP\WWANG\ORADATA\MDSP\SYSTEM01.DBF SYSTEM
D:\APP\WWANG\ORADATA\MDSP\IMUSE01.DBF IMUSE01
D:\APP\WWANG\ORADATA\MDSP\JACK.DBF JACK
已选择6行。
2.查看表空间状态
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
IMUSE01 ONLINE
JACK ONLINE
已选择7行。
3.把jack表空间离线并移动jack.dbf到c:\oradata下
Alter tablespace jack offline;
SQL> Alter tablespace jack offline;
表空间已更改。
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
IMUSE01 ONLINE
JACK OFFLINE
已选择7行。
4.用alter tablespace 更新控制文件
SQL> alter tablespace jack rename
2 datafile 'D:\app\wwang\oradata\mdsp\JACK.DBF'
3 to 'C:\oradata\JACK.DBF';
表空间已更改。
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- --------------------
D:\APP\WWANG\ORADATA\MDSP\USERS01.DBF USERS
D:\APP\WWANG\ORADATA\MDSP\UNDOTBS01.DBF UNDOTBS1
D:\APP\WWANG\ORADATA\MDSP\SYSAUX01.DBF SYSAUX
D:\APP\WWANG\ORADATA\MDSP\SYSTEM01.DBF SYSTEM
D:\APP\WWANG\ORADATA\MDSP\IMUSE01.DBF IMUSE01
C:\ORADATA\JACK.DBF JACK
已选择6行。
5.把表空间jack 上线online
Alter tablespace jack online;
SQL> Alter tablespace jack online;
表空间已更改。
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
TABLESPACE_NAME STATUS
-------------------- ------------------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
IMUSE01 ONLINE
JACK ONLINE
已选择7行。
至此用alter tablespace 移动数据文件的位置完成
由于system表空间不能离线所以不能用上述方式移动
用下面的方法可以移动system表空间
Moving Data Files:
ALTER DATABASE
• The database must be mounted.
• The target data file must exist.
ALTER DATABASE RENAME
FILE '/u01/oradata/system01.dbf'
TO '/u03/oradata/system01.dbf';
1.启动数据库到mount状态并移动system01.dbf
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 326414336 bytes
Fixed Size 1374388 bytes
Variable Size 251660108 bytes
Database Buffers 67108864 bytes
Redo Buffers 6270976 bytes
数据库装载完毕。
2.alter database更新控制文件
SQL> alter database rename file
2 'D:\app\wwang\oradata\mdsp\SYSTEM01.dbf'
3 to 'C:\oradata\SYSTEM01.dbf';
数据库已更改。
SQL> alter database open;
数据库已更改。
SQL> select FILE_NAME,TABLESPACE_NAME from dba_data_files;
FILE_NAME TABLESPACE_NAME
---------------------------------------- --------------------
D:\APP\WWANG\ORADATA\MDSP\USERS01.DBF USERS
D:\APP\WWANG\ORADATA\MDSP\UNDOTBS01.DBF UNDOTBS1
D:\APP\WWANG\ORADATA\MDSP\SYSAUX01.DBF SYSAUX
C:\ORADATA\SYSTEM01.DBF SYSTEM
D:\APP\WWANG\ORADATA\MDSP\IMUSE01.DBF IMUSE01
C:\ORADATA\JACK.DBF JACK
已选择6行。