解决Oracle占用磁盘太大的问题

背景

数据库运行了一段时间后，表空间一直增加，但是实际数据量并没那么大。删除数据或者truncate表并不会降低表空间。这是因为表空间不足时会自动扩容，但是不会自动收缩。

解决办法

1. 先导出再导入方式

直接导出数据库再创建个小一些的表空间导入，研发环境最简单、有效。也可以结合方法2中的相关sql，将占用空间大的表查询出来，删除无用数据后再导出。

如下：
表空间、默认的实例名称、用户密码均为fssctest

D:\app\adaivskenan\product\11.2.0\dbhome_1\BIN\expdp  fssctest/fssctest@orcl dumpfile=202107.dmp

导出和导入的数据存储在 D:\app\adaivskenan\admin\orcl\dpdump

# 导入方式一
D:\app\adaivskenan\product\11.2.0\dbhome_1\BIN\impdp fssctest/fssctest dumpfile=202107.dmp
# 导入方式二
impdp fssctest/fssctest dumpfile=XXX.dmp directory=dpdata1 remap_schema=fssctest:fssctest remap_tablespace=fssctest:newfssctest

# directory 需要用命令在oracle中创建一个路径
# fssctest：原先dmp文件的表空间名称，如果不知道原先的表空间名称是什么，先随便写个，到时候导入的时候会提示表空间不存在，把提示不存在的表空间名称写上去就可以。
# newfssctest：需要导入到的表空间的名称（第一步创建的表空间名称）

2. Resize方式

主动收缩表空间占磁盘的大小只能用resize收缩，如果收缩报错ORA-03297，说明Resize的尺寸过小，需要适当调大reisze的值

1. 查询可直接收缩表空间数据文件,执行resize命令

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
          ceil(HWM * a.block_size)/1024/1024 ResizeTo,
          (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
          'alter database datafile '''||a.name||''' resize '||
          ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
   from v$datafile a,
        (select file_id,max(block_id+blocks-1) HWM
          from dba_extents
          group by file_id) b
  where a.file# = b.file_id(+)
   and (a.bytes - HWM *block_size)>0;
  

alter database datafile 'D:\APP\ADAIVSKENAN\ORADATA\ORCL\FSSCTEST.DBF' resize 2080M;   

2. 查询所有表空间以及每个表空间的大小，已用空间，剩余空间，使用率和空闲率

select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

3. 查询排名前10的占用表和空间

SELECT * FROM (SELECT SEGMENT_NAME, SUM(BYTES) / 1024 / 1024 MB FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 
upper('fssctest') GROUP BY SEGMENT_NAME ORDER BY 2 DESC) WHERE ROWNUM < 10;

4. 查询某个具体的表所占空间的大小，把“TABLE_NAME”换成具体要查询的表的名称：

select t.segment_name, t.segment_type, sum(t.bytes / 1024 / 1024) "占用空间(M)"
from dba_segments t
where t.segment_type='TABLE'
and t.segment_name='TABLE_NAME'
group by OWNER, t.segment_name, t.segment_type;

5. 查询blob存储的SYS_LOB相关表（表格式为：SYS_LOB(10 digit object_id)C(5 digit col#)，取中间10位查询）

a. 我们建一个表的时候，oracle对在对应的表空间在给我们一个segent中来存放数据，并且会因为数据量的增大再做扩展。但是当我们所建立的表中含有lob型的数据时，oracle会为每个lob字段生成一个独立的segment用来存放数据，同时也建立了独立的index segment .oracle对它们是单独管理的。

b. 普通表只会新增一个或两个段对象.类型为TABLE和INDEX，数据就存放在表段中.索引就放在索引段中。但是LOB列则额外新增了两个段对象,类型为LOBSEGMENT和LOBINDEX，LOBINDEX用于指向LOB段,找出其中的某一部分，所以存储在表中的LOB存储的是一个地址,或者说是一个指针,实际上表中的lob列中存的是一个地址段.然后在lobindex找到所有的地址段.然后在lobSegment中把所有地址段的值都读取了来。所以lobSegment就保存了LOG列的真正的数据，所以会非常大，并且独立于原始表存在。

select object_name,status from dba_objects where object_id in('0000149596','0000152152','0000149860','0000151756')

6. 库高水位对象统计

a. 比较表的行数和表的大小关系。如果行数为0，而表的当前占用大小减去初始化时的大小（INITIAL_EXTENT）后依然很大，那么说明该表有高水位。

b. 行数和块数的比率，即查看一个块可以存储多少行数据。如果一个块存储的行数少于5行甚至更少，那么说明有高水位。注意，这两种方法都不是十分准确，需要再对查询结果进行筛选。需要注意的是，在查询表的高水位时，首先需要分析表，以得到最准确的统计信息。

7. 查看浪费空间的表空间和大小

SELECT D.OWNER,
       ROUND(D.NUM_ROWS / D.BLOCKS, 2),
       D.NUM_ROWS,
       D.BLOCKS,
       D.TABLE_NAME,
 ROUND((d.BLOCKS*8-D.INITIAL_EXTENT/1024)/1024)  t_size_MB
  FROM DBA_TABLES D
 WHERE D.BLOCKS > 10
   AND ROUND(D.NUM_ROWS / D.BLOCKS, 2) < 5
 AND d.OWNER='FSSCTEST' ;

浪费空间的百分比

SELECT OWNER,
       SEGMENT_NAME TABLE_NAME,
       SEGMENT_TYPE,
       GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
                      GREATEST(NVL(HWM, 1), 1)),
                      2),
                0) WASTE_PER
  FROM (SELECT A.OWNER OWNER,
               A.SEGMENT_NAME,
               A.SEGMENT_TYPE,
               B.LAST_ANALYZED,
               A.BYTES,
               B.NUM_ROWS,
               A.BLOCKS BLOCKS,
               B.EMPTY_BLOCKS EMPTY_BLOCKS,
               A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
               DECODE(ROUND((B.AVG_ROW_LEN * NUM_ROWS *
                            (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
                            0),
                      0,
                      1,
                      ROUND((B.AVG_ROW_LEN * NUM_ROWS *
                            (1 + (PCT_FREE / 100))) / C.BLOCKSIZE,
                            0)) + 2 AVG_USED_BLOCKS,
               ROUND(100 *
                     (NVL(B.CHAIN_CNT, 0) / GREATEST(NVL(B.NUM_ROWS, 1), 1)),
                     2) CHAIN_PER,
               B.TABLESPACE_NAME O_TABLESPACE_NAME
          FROM SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B, SYS.TS$ C
         WHERE A.OWNER = B.OWNER
           AND SEGMENT_NAME = TABLE_NAME
           AND SEGMENT_TYPE = 'TABLE'
           AND B.TABLESPACE_NAME = C.NAME)
 WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0) /
                      GREATEST(NVL(HWM, 1), 1)),
                      2),
                0) > 50
   AND OWNER NOT LIKE '%SYS%'
   AND BLOCKS > 100
 ORDER BY WASTE_PER DESC; 

8. 表统计信息收集 sqlplus执行

降低表对空间浪费之前必须进行ANALYZE操作，然后执行结果的收集分析，否则HWM分析准确性不高

ANALYZE TABLE  B0204_QUERYCUSTSET ESTIMATE STATISTICS;

ANALYZE TABLE  B0204_QUERYCUSTSET  COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL INDEXED COLUMNS;

execute dbms_stats.gather_table_stats(ownname => 'fssctest', tabname => 'B0204_QUERYCUSTSET' , estimate_percent => null ,method_opt => 'for all indexed columns' ,cascade => true);

9. HWM分析可以释放多少BLOCKS

select t.TABLE_NAME,t.NUM_ROWS,t.BLOCKS,t.empty_blocks,t.LAST_ANALYZED from dba_tables t where table_name in ('B0204_QUERYCUSTSET') and t.TABLESPACE_NAME='FSSCTEST';

SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) USED_BLOCK FROM  B0204_QUERYCUSTSET;

查看当前大小

select segment_name,bytes/1024/1024 TSize_MB from dba_segments where segment_name='B0104_BILLPIPE'  and TABLESPACE_NAME='FSSCTEST';

10. 降低表的高水位(shrink or move)：不仅可以减少磁盘占用，还是优化效率的重要方式

a. 降低方法shrink(需要先允许移动)

alter table B0204_QUERYCUSTSET enable row movement;
alter table B0204_QUERYCUSTSET shrink space;

b. 降低方法 move(需要进行索引重建)

alter table CORE_METADATA_HIST move;
alter index idx_name rebuild;