Linux下Oracle数据表更换表空间
自己挖坑自己填——某某人如是说。依稀记得两年前冬去春来的时节，某某人给自己挖了一个小小的坑。岁月轮转，两年的光景，洒家来填坑了。
2017年刚刚立春，分公司服务平台准备部署上线，由于当时服务器资源紧张，数据库要和网站、商城共用一台服务器，这台服务器是Linux操作系统。当时平台组也比较忙，于是乎在对Linux一知半解，对Oracle一知半解的情况下硬着头皮在生产环境安装并创建了人生中第一个Linux下的Oracle数据库，也挖了今天要填的坑。
1、 对Linux文件系统不熟，没有关注磁盘分区情况，直接在默认分区下安装了Oracle，并创建了实例，该分区只有50G，当时还剩多少没注意。
2、 在后续使用过程中没有为项目单独创建表空间，直接使用了默认表空间，造成SYSTEM、USERS、SYSAUX表空间越来越大。
3、 后来网站和商城外包开发，他们直接用了我们这个项目的Oracle实例，作为这个实例的一个用户存在，此时我依然没有考虑到空间问题。
4、 后来同事也曾经反映过磁盘空间的问题，因为项目没上量，就直接归咎到网站和商城外包公司那边了，让他们做了清理。
2019年立春还有一个多月，新项目和服务平台共用一个数据库，做数据迁移时终于发现了空间问题。
1、 Oracle所在分区磁盘空间紧张，还剩不到6G。
2、 SYSTEM、USERS、SYSAUX表空间占用率太高，TEMP表空间已满。图上SYSTEM是扩容过的，没扩之前忘记截图了，扩了2G。
新项目上线，随着使用量上升，空间终究是个问题，因此查了查资料，在磁盘分区扩容和表空间转移两个方案中选择了把表空间转移到新的磁盘分区。具体思路及操作步骤如下：
思路：根据第一个图可知还有home分区还很大，可以在这个分区上创建新的表空间文件，然后把现有的数据表的表空间换成新的表空间，这样既可以清理出来root分区磁盘空间，也规范了以后建表使用的表空间文件。实施之前先做了个小实验，在原来表空间上创建了60万记录的表，这个时候USERS表空间变大，然后再把该表迁移到新的表空间，USERS表空间变小了。
一、第一步：使用root用户在新的分区上创建存放Oracle表空间的目录，并为Oracle用户分配目录权限。
第一次从root用户切换到orauser用户时，标识符变成了这个：
，重新登录了一次就好了。
Linux下一些用到的命令如下：
l 创建目录：mkdir 目录名
l 为用户分配目录权限：chown -R 用户名 目录名
chmod -R 755 目录名
注意：分配权限时目录名前加“/”，代表根目录
l 查看当前用户所属用户组：groups
l 查看目录文件命令：ls –lhi
l 文件读写属性：在上图中文件-rw—代表的是文件读写和执行的属性，r表示可读，w表示可写，还有一个上图没有出现的x表示可执行。这个串一共10位，第一位代表类型，如果是d代表是目录，第2到10位分三组分别代表所有者（user）、群组（group）、其他人（other）的权限。rwx-用数字表示的话分别是：r=4，w=2，x=1，-=0，每一组的三个位置上用数字代替相加后就是这一组的权限数字，例如rwxr-xr-x，第一组rwx就是4+2+1=7，第二组和第三者r-x就是4+0+1=5，因此rwxr-xr-x用数字代表就是755，表示只有所有者才有读写执行的权限，组群和其他人只有读和执行的权限。上面介绍为用户分配目录权限的时候“chmod -R 755 目录名”中的755就是这个意思。
二、第二步：新分区上目录名创建好后，开始创建新的数据库表空间。创建之前，先看看表空间使用情况和表空间所在路径。
l 查询表空间所在路径：select * from dba_data_files
l 查看表空间使用情况的SQL：
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS "SUM_BLOCKS",
SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%'
"USED_RATE(%)",
FREE_SPACE || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2)
FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,
SPACE || 'M' "SUM_SPACE(M)",
BLOCKS SUM_BLOCKS,
USED_SPACE || 'M' "USED_SPACE(M)",
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%'
"USED_RATE(%)",
NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2)
USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2)
FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
三、第三步：创建表空间：分别创建数据表空间和索引表空间，数据表空间创建了2个，日志单独放一个表空间，其他业务数据放一个表空间。另外给SYSTEM和TEMP扩展一个表空间文件
l 以sysdba身份启动sqlplus
命令行：sqlplus / as sysdba
l 创建表空间语句：
create tablespace 表空间名称 datafile '表空间文件名（带路径）' size 5120M autoextend ON next 100M maxsize unlimited extent management local;
l SYSTEM表空间增加数据文件：
alter tablespace system add datafile '/home/oradata/system_01.dbf' size 2048M;
l TEMP表空间增加数据文件：
alter tablespace temp add tempfile '/home/oradata/temp_01.dbf' size 2048M;
这里还有两个小坑，SYSTEM和TEMP表空间创建后，不是自动扩展，需要修改，修改后还要改增长步长，默认步长都是1。因此，任何交易类操作做完后一定要看结果，一定要看结果，一定要看结果。
alter database datafile '/home/oradata/system_01.dbf' autoextend on next 10M maxsize 20480M;
alter database tempfile '/home/oradata/temp_01.dbf' autoextend on next 10M maxsize 20480M;
设置原来表空间停止自动扩展
alter database datafile '表空间文件' autoextend off;
alter database tempfile '表空间文件' autoextend off;
l SYSTEM表空间为什么会满？
查询该表空间哪个表最大：
select * from (select SEGMENT_NAME,sum(bytes)/1024/1024 sx from dba_segments
where tablespace_name='SYSTEM' group by segment_name)
where sx>100 order by sx desc;
查看审计表AUD$，审计（Audit)用于监视用户所执行的数据库操作
select count(*) from SYS.AUD$;
查看那种审计比较多：
select action_name,count(*) from dba_audit_trail group by action_name;
对于审计这个表，可以扩展系统表空间的大小，也可以关闭审计功能，并truncate掉这张表。
l USERS表空间为什么会这么大
业务表没有单独创建表空间是一个原因，但其实现在数据记录条数并没有那么大，最多也就是百万级，查看一下USERS表空间哪个表占的比较大：
上网搜了下SYS_LOB0000136691C00006$$，是因为CLOB字段的问题，通常情况下普通表只会新增一个或两个段对象，有CLOB字段的表CLOB列额外新增了两个段对象,类型为LOBSEGMENT和LOBINDEX，LOBINDEX用于指向CLOB段,找出其中的某一部分，所以存储在表中的CLOB存储的是一个地址,或者说是一个指针,实际上表中的CLOB列中存的是一个地址段.然后在lobindex找到所有的地址段.然后在lobSegment中把所有地址段的值都读取了来。所以lobSegment就保存了CLOB列的真正的数据，所以会非常大，并且独立于原始表存在
四、第四步：将新项目的表迁移到新的表空间
l 更换表空间的语句：alter table 表名 move tablespace 表空间名;
l 如果是整个表空间的表都要转到新的表空间则用这个语句：
select 'alter table ' ||table_name || ' move tablespace 目标表空间名称;' from user_all_tables where tablespace_name='源表空间名称'
l 因为这次要把项目用到的表从USERS换到新的表空间，不是所有表，因此可以通过用户来批量修改。
l 根据用户查数据表：
select * from all_tables where owner = '用户名'; 注意大小写。
l 最终转换表空间语句：select 'alter table ' ||table_name || ' move tablespace 目标表空间名称;' from all_tables where upper(owner)=upper('用户名');
五、第五步：使用move后，索引会变的无效，需要重建索引
查找用户下的所有索引，看看索引状态
select index_name,table_name,tablespace_name,index_type,status from dba_indexes where table_owner='用户名' ;--注意大小写
找到索引状态无效的进行重建：
alter index 用户名.索引名称 rebuild tablespace 表空间名;
六、第六步：把CLOB自动转移到相应的表空间（虽然数据表转到新的表空间了，但CLOB字段没有自动转）
ALTER TABLE 用户名.表名 MOVE LOB(CLOB字段名) STORE AS (TABLESPACE 新表空间);
注意新建的表空间一定要足够大。
七、第七步：修改用户默认表空间
ALTER USER 用户名 DEFAULT TABLESPACE 表空间;
八、第八步：收缩USERS表空间数据文件
l 先找到USERS表空间数据文件的路径或者ID，这里用了路径名
select * from dba_data_files;
l 允许表空间进行收缩（对表空间进行融合）
alter tablespace users coalesce;
l 查询表空间中有哪些表
select * from dba_segments where tablespace_name='USERS' and segment_type='TABLE'
l 允许表进行行移动（某些表不能进行truncate，只能delete）
alter table SCOTT.DEPT enable row movement;
alter table SCOTT.EMP enable row movement;
alter table SCOTT.SALGRADE enable row movement;
l 对表进行高水位线回收
alter table SCOTT.DEPT shrink space;
alter table SCOTT.EMP shrink space;
alter table SCOTT.SALGRADE shrink space;
l 收缩表空间数据文件大小
alter database datafile '/ora/app/oradata/fwzs/users01.dbf' resize 2048M;
九、第九步：执行完毕后，确认结果：数据能够正常使用，root分区空间释放。
