oracle表空间Managing Tablespace & Data Files(三)
ng data file（移动数据文件）
1、Alter Tablespace
前提
①、Tablespace must be offline
②、Targer data file must exist
步骤
①、alter tablespace shanxi offline;
②、离线状态下dba_data_files 中bytes是空的
③、将shanxi3.dbf更改到上一级
alter tablespace shanxi rename datafile '/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi3.dbf'
to '/u01/app/oracle/product/10.2.0/oradata/shanxi3.dbf';
④、在通过dba_data_files查询发现路径变化了
2、Alter Database
前提：
①、database must be mounted
②、targer data file must exist;
步骤
①、关闭数据库
shutdown immediate;
②、移动数据文件或拷贝
mv shanxi3.dbf oamis/
③、打开数据库到mount状态
startup mount;
④、执行下面命令
alter database rename file '/u01/app/oracle/product/10.2.0/oradata/shanxi3.dbf' to
'/u01/app/oracle/product/10.2.0/oradata/oamis/shanxi3.dbf';
⑤、修改数据库为打开状态
alter database open;
⑥、查询dba_data_files文件看路径
十五、Dropping Tablespace
You cannot drop a tablespace if it:
-Is the System tablespace
-Has active segment
including contents drops the segments
including contexts and datafile deletes data files
cascade constraints drops all referential integrity constraints
命令：
drop tablespace userdata including contents and datafiles
www.2cto.com
删除表空间shanxi及文件
drop tablespace shanxi including contents and datafiles;
十六、Get Tablespace information
1、Tablespace information
--dba_tablespaces
--v$tablespace
2、Data file information
dba_data_files
v$datafile
3、Temp file information
dba_temp_files
v$tempfile
作者 liyangfd