oracle表空间位置移动(包括系统表空间和redo log)

查询表空间使用情况

--查询表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)",
file_name
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,file_name,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME,file_name) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 7 DESC;
select TABLESPACE_NAME,FILE_NAME from dba_data_files;

修改非系统表空间位置

--------------------------------------------------------------------------------
-- 修改非系统表空间位置 系统表空间UNDOTBS1,SYSTEM不能这么移动,但是SYSAUX可以          --
--------------------------------------------------------------------------------
--1 创建迁移目录(注意权限)
sudo mkdir -p /data/tb_oracle
sudo chown -R oracle:oinstall /data/tb_oracle
--2 修改表空间为 Offline 状态
sqlplus
SQL> ALTER TABLESPACE USERS OFFLINE;
 
Tablespace altered.
--3 拷贝数据文件
 !cp /home/oracle/tb_remove/users01.dbf /data01/tb_oracle/users01.dbf
 !sudo chown chown /data01/tb_oracle/users01.dbf
--4 修改 oracle 表空间指向地址
    alter database rename file '/home/oracle/tb_remove/users01.dbf' to '/data/tb_oracle/users01.dbf';
--5 修改表空间为 Online 状态
  alter tablespace USERS online;
-- 6 查看迁移后
  select TABLESPACE_NAME,file_name from sys.dba_data_files;

修改系统表空间位置 SYSTEM和redo log

--------------------------------------------------------------------------------
--  修改系统表空间位置 SYSTEM以及redo log
--------------------------------------------------------------------------------
-- sqlplus "as sysdba"  要系统管理员权限
--1.1 查看表空间信息
select TABLESPACE_NAME,FILE_NAME from dba_data_files;
--1.2 关闭数据库
SQL> shutdown immediate;
--1.3 复制system表空间对应数据文件去新路径(建议使用oracle用户,复制的文件不会有权限问题)
!cp /opt/oracle/oradata/orcl/system01.dbf /data/tb_oracle/system01.dbf
--1.4 给新复制的文件修改为原文件所属用户和用户组
!chown chown oracle.oinstall system01.dbf
--1.5 以mount启动数据库
SQL> startup mount
--1.6 修改system表空间对应数据文件去新路径
SQL> alter database rename file  '/opt/oracle/oradata/orcl/system01.dbf' to '/data/tb_oracle/system01.dbf';
--1.7 启动数据库
SQL> alter database open;
--1.8 确认修改完成
select TABLESPACE_NAME,FILE_NAME from dba_data_files;

修改系统表UNDOTBS1空间位置

--------------------------------------------------------------------------------
--  修改系统表UNDOTBS1空间位置 
--------------------------------------------------------------------------------
--1.以dba用户登录oracle，
sqlplus / as sysdba

--2.查看undo空间；
show parameter undo_tablespace;

--3.查看表空间和文件的对应关系 UNDOTBS1是ONLINE
select file_name, tablespace_name, online_status from dba_data_files where tablespace_name='UNDOTBS1';
--4.查询当前回退表空间状态
select tablespace_name, status from dba_rollback_segs;
--5.undo_tablespace 是一个必须一直存在的表空间，要想删除当前的，我们必须设置一个临时空间供undo_tablespace 使用；
create undo tablespace UNDOTBS2 datafile '/data/tb_oracle/undotbs02.dbf' size 100M;
alter system set undo_tablespace=UNDOTBS2;
--6.重新查询当前回退表空间状态 UNDOTBS1已经变成OFFLINE
select tablespace_name, status from dba_rollback_segs;
--7.删除回退表空间UNDOTBS1
drop tablespace UNDOTBS1 including contents and datafiles;

-- 最好重启下释放空间
shutdown immediate;

startup;

-- 重复以上命令将回退表空间重新设置回来
create undo tablespace UNDOTBS1 datafile '/data/tb_oracle/undotbs1.dbf' size 100M autoextend on maxsize 25G;
alter system set undo_tablespace=UNDOTBS1;
select tablespace_name, status from dba_rollback_segs;
drop tablespace UNDOTBS2 including contents and datafiles;
shutdown immediate;
startup;

做好备份,注意数据安全。

参考
https://www.jianshu.com/p/4b05306edee0
https://www.cnblogs.com/yhq1314/p/15790802.html
https://blog.csdn.net/weixin_30040925/article/details/116317151