当前位置:   article > 正文

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

oracle占用空间太大

背景

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

解决办法

1. 先导出再导入方式

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

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

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

导出和导入的数据存储在 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:需要导入到的表空间的名称(第一步创建的表空间名称)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

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;
  
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
alter database datafile 'D:\APP\ADAIVSKENAN\ORADATA\ORCL\FSSCTEST.DBF' resize 2080M;   
  • 1
  1. 查询所有表空间以及每个表空间的大小,已用空间,剩余空间,使用率和空闲率
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;
  • 1
  • 2
  • 3
  • 4
  • 5
  1. 查询排名前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;
  • 1
  • 2
  1. 查询某个具体的表所占空间的大小,把“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;
  • 1
  • 2
  • 3
  • 4
  • 5
  1. 查询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')
  • 1
  1. 库高水位对象统计

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

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

  1. 查看浪费空间的表空间和大小
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' ;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

浪费空间的百分比

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; 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  1. 表统计信息收集 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);
  • 1
  • 2
  • 3
  • 4
  • 5
  1. 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;
  • 1
  • 2
  • 3

查看当前大小

select segment_name,bytes/1024/1024 TSize_MB from dba_segments where segment_name='B0104_BILLPIPE'  and TABLESPACE_NAME='FSSCTEST';
  • 1
  1. 降低表的高水位(shrink or move):不仅可以减少磁盘占用,还是优化效率的重要方式

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

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

  • 1
  • 2
  • 3

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

alter table CORE_METADATA_HIST move;
alter index idx_name rebuild;
  • 1
  • 2
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/weixin_40725706/article/detail/967423
推荐阅读
相关标签
  

闽ICP备14008679号