mysql ibdata缩小

    

 最近导一个空库到数据库后，发现占用的表空间非常大，执行表收缩（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.找出需要移动表空间的表并将这些表移到刚创建的表空间

--收缩表

SELECT DISTINCT 'alter table ' || segment_name ||
                ' 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 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 '||owner||',' || segment_name || ' enable row movement; '  FROM dba_extents WHERE tablespace_name = 'gcomm_bk'   AND segment_type = 'TABLE';
释放存贮--针对delte
SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' shrink space cascade; '  FROM dba_extents WHERE tablespace_name = 'gcomm_bk'   AND segment_type = 'TABLE';
移动表到新的表空间
SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' move tablespace GCOMM2; '  FROM dba_extents WHERE tablespace_name = 'gcomm_bk'   AND segment_type = 'TABLE';
重建索引到新的表空间
SELECT DISTINCT 'alter INDEX  '||owner||',' || segment_name ||' REBUILD TABLESPACE GCOMM2; '  FROM dba_extents WHERE tablespace_name = '"gcomm_bk"'  AND segment_type = 'INDEX';
移动大对象到新的表空间
select 'alter table '||owner||','||table_name||' move lob('||colmn_name||') store as (tablespace GCOMM2)' from dba_lobs  
where segment_name in(select segment_name
                         from dba_extents
                         where tablespace_name ='gcomm_bk' and  segment_type like 'LOB%')

二、征对原因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多。

 最近导一个空库到数据库后，发现占用的表空间非常大，执行表收缩（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.找出需要移动表空间的表并将这些表移到刚创建的表空间

--收缩表

SELECT DISTINCT 'alter table ' || segment_name ||

                ' 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 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 '||owner||',' || segment_name || ' enable row movement; '  FROM dba_extents WHERE tablespace_name = 'gcomm_bk'   AND segment_type = 'TABLE';

释放存贮--针对delte

SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' shrink space cascade; '  FROM dba_extents WHERE tablespace_name = 'gcomm_bk'   AND segment_type = 'TABLE';

移动表到新的表空间

SELECT DISTINCT 'alter table '||owner||',' || segment_name || ' move tablespace GCOMM2; '  FROM dba_extents WHERE tablespace_name = 'gcomm_bk'   AND segment_type = 'TABLE';

重建索引到新的表空间

SELECT DISTINCT 'alter INDEX  '||owner||',' || segment_name ||' REBUILD TABLESPACE GCOMM2; '  FROM dba_extents WHERE tablespace_name = '"gcomm_bk"'  AND segment_type = 'INDEX';

移动大对象到新的表空间

select 'alter table '||owner||','||table_name||' move lob('||colmn_name||') store as (tablespace GCOMM2)' from dba_lobs  

where segment_name in(select segment_name

                         from dba_extents

                         where tablespace_name ='gcomm_bk' and  segment_type like 'LOB%')

二、征对原因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多。