[大数据]11-Oracle表空间管理-迁移数据文件

前言

一、迁移数据文件

二、脚本实验

1.查询系统表空间的数据文件信息

select tablespace_name,
       file_name,
       bytes,
       status
  from dba_data_files
 where tablespace_name = 'SYSTEM';

2.使用dba权限的用户登录数据库服务器，并关闭数据库

conn sys/lianhc-123 as sysdba
shutdown immediate
cp 
/opt/oracle/app/oradata/orcl/system01.dbf /opt/oracle/app/oradata/orcl/tablespace/system01.dbf

startup mount

alter database rename file 
'/opt/oracle/app/oradata/orcl/system01.dbf' 
to '/opt/oracle/app/oradata/orcl/tablespace/system01.dbf';

alter database open;

select tablespace_name,
       file_name,
       status
  from dba_data_files
 where tablespace_name = 'SYSTEM';

3.迁移非系统表空间

alter tablespace MANAGER_TBS1 offline;

host cp 
'/opt/oracle/app/oradata/orcl/managertbs01.dbf' '/opt/oracle/app/oradata/orcl/tablespace/managertbs01.dbf'

alter database rename file 
'/opt/oracle/app/oradata/orcl/managertbs01.dbf' 
to '/opt/oracle/app/oradata/orcl/tablespace/managertbs01.dbf';

alter tablespace MANAGER_TBS1 online;

select tablespace_name,
       file_name,
       status
  from dba_data_files
 where tablespace_name = 'MANAGER_TBS1';