最近导一个空库到数据库后,发现占用的表空间非常大,执行表收缩(SHRINK SPACE CASCADE)后,发现实际占用的空间不到1%。
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 5000M;
提示:ORA-03297: file contains used data beyond requested RESIZE value
原因1:压缩表空间大小,最小必须是该表空间目前最大块段的大小
原因2:表的初始大小分配的太大
一、征对原因1,可以采用移动表空间的方法来减小block_id
--找出目前GCOMM2表空间最大的block_id
SELECT MAX(block_id)
FROM dba_extents
WHERE tablespace_name = 'GCOMM2';
MAX(BLOCK_ID)
-------------
994816
--计算目前最大块的段所占用的空间(该数据库的block大小是8092)
SQL> show parameter db_block_size ;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SELECT 994816*8192/1024/1024 FROM dual;
994816*8192/1024/1024
---------------------
7772
因此目前该表空间只能收缩到7772M左右
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 7772M;
--通过移动移动表空间来收缩表空间大小步骤
1.创建表空间用于将较大block_id的表移到此表空间
CREATE TABLESPACE "gcomm_bk" DATAFILE 'D:\ora_tablespace\gcomm_bk.dbf' SIZE 2048M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
2.找出需要移动表空间的表并将这些表移到刚创建的表空间
--收缩表
' SHRINK SPACE CASCADE; '
FROM dba_extents
WHERE tablespace_name = 'GCOMM2'
AND segment_type = 'TABLE'
AND file_id = 11
AND block_id > 30336;
SELECT DISTINCT 'alter table ' || segment_name ||
' move tablespace "gcomm_bk"; '
FROM dba_extents
WHERE tablespace_name = 'GCOMM2'
AND file_id = 11
AND block_id > 30336/*872704*/
AND segment_type = 'TABLE';
移动表空间过程中出现了这个错误:ORA-01658: 无法为表空间 gcomm_bk 中的段创建 INITIAL 区
原因:表空间不够。
解决:将该表空间设为自动扩展或者直接扩大该表空间
注:如果是smallfile 表空间无法设为自动扩展,则必须手动将这些表空间先加大
ALTER DATABASE DATAFILE 'D:\ora_tablespace\gcomm_bk.dbf' RESIZE 7000M;
3.重建该空间的索引
SELECT DISTINCT 'alter INDEX ' || segment_name ||
' REBUILD TABLESPACE "gcomm_bk"; '
FROM dba_extents
WHERE tablespace_name = 'GCOMM2'
AND file_id = 11
AND block_id > /*30336*/872704
AND segment_type = 'INDEX';
4.再次查看此时gcomm2表空间最大块的段
SELECT MAX(block_id)
FROM dba_extents
WHERE tablespace_name = 'GCOMM2';
SELECT 30336*8192/1024/1024 FROM dual;
5.收缩表空间(执行成功)
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 500M;
6.再将表移回来
SELECT DISTINCT 'alter table ' || segment_name ||
' move tablespace GCOMM2; '
FROM dba_extents
WHERE tablespace_name = 'gcomm_bk'
AND segment_type = 'TABLE';
7.重建索引
' REBUILD TABLESPACE GCOMM2; '
FROM dba_extents
WHERE tablespace_name = '"gcomm_bk"'
AND file_id = 11
AND block_id > 30336
AND segment_type = 'INDEX';
二、征对原因2可通过减小表或索引的初始化大小来收缩表空间
--征对块大的表 减小其初始分配大小
SELECT DISTINCT 'alter table ' || segment_name ||
' MOVE STORAGE(INITIAL 1M) ;'
FROM dba_extents
WHERE tablespace_name = 'GCOMM2'
AND segment_type = 'TABLE'
AND file_id = 11
AND block_id > 30336;
--征对块大的索引减小其初始分配大小
SELECT DISTINCT 'alter index ' || segment_name ||
' REBUILD tablespace GCOMM2 storage(initial 64K next 1M pctincrease 0);'
FROM dba_extents
WHERE tablespace_name = 'GCOMM2'
AND segment_type = 'INDEX'
AND file_id = 11
AND block_id > 30336;
然后再收缩表空间
ALTER DATABASE DATAFILE 'D:\ora_tablespace\GCOMM2.dbf' RESIZE 1000M;
执行成功,原先为7G多。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24558279/viewspace-1220754/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24558279/viewspace-1220754/