背景
数据库运行了一段时间后,表空间一直增加,但是实际数据量并没那么大。删除数据或者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的值
- 查询可直接收缩表空间数据文件,执行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;
- 查询所有表空间以及每个表空间的大小,已用空间,剩余空间,使用率和空闲率
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;
- 查询排名前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;
- 查询某个具体的表所占空间的大小,把“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;
- 查询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')
- 库高水位对象统计
a. 比较表的行数和表的大小关系。如果行数为0,而表的当前占用大小减去初始化时的大小(INITIAL_EXTENT)后依然很大,那么说明该表有高水位。
b. 行数和块数的比率,即查看一个块可以存储多少行数据。如果一个块存储的行数少于5行甚至更少,那么说明有高水位。注意,这两种方法都不是十分准确,需要再对查询结果进行筛选。需要注意的是,在查询表的高水位时,首先需要分析表,以得到最准确的统计信息。
- 查看浪费空间的表空间和大小
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;
- 表统计信息收集 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);
- 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';
- 降低表的高水位(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;