, , ,

Изменение размера tablespace'а (resize tablespace)

четверг, 16 апреля 2009 г. Оставить комментарий

Очередной раз закончилось место на диске, и в результате поиска свободного места пришла идея уменьшить размер разросшегося tablespace'а.

Смотрим размер tablespace'ов:


select a.tablespace_name ,
round(a.bytes_alloc / 1024 / 1024, 2) m_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) m_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) m_used,
round(maxbytes/1048576,2) Max
from ( select f.tablespace_name,
sum(f.bytes) bytes_alloc,
sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
from dba_data_files f
group by tablespace_name) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+)

Результат:
TABLESPACE_NAMEM_ALLOCM_FREEM_USEDMAX
1UNDOTBS1335323,6911,3132767,98
2SYSAUX36019,38340,6332767,98
3USERS54,560,4432767,98
4SYSTEM6805,56674,4432767,98
5TS_TEST3272032607,69112,3132767,98

Оказалось, что TS_TEST лежит в одном файле и занимает 32720Mb, а использует 112,31Mb. Поэтому следущим желанием было уменьшить размер, хотя бы до 1000MB.

ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\FKHD\TS_STATDEMO01.DBF' RESIZE 1000M;

Видим ошибку:

ORA-03297: file contains used data beyond requested RESIZE value

Получается что данные разбросаны по всему tablespace, и сжать его не получается.
Посмотрим до какого размера можно сжать tablespace.

select dba_data_files.file_name,
dba_data_files.file_id,
dba_data_files.tablespace_name,
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) smallest,
ceil(blocks * db_block_size / 1024 / 1024) currsize,
ceil(blocks * db_block_size / 1024 / 1024) -
ceil((nvl(hwm, 1) * db_block_size) / 1024 / 1024) savings
from dba_data_files,
(select file_id,
max(block_id + blocks - 1) hwm
from dba_extents
group by file_id) b,
(select value db_block_size from v$parameter where name = 'db_block_size') c
where dba_data_files.file_id = b.file_id(+);

Результат:
 FILE_NAMEFILE_IDTABLESPACE_NAMESMALLESTCURRSIZESAVINGS
1D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF1SYSTEM6766804
2D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF2UNDOTBS1140335195
3D:\ORACLE\ORADATA\TEST\USERS01.DBF4USERS154
4D:\ORACLE\ORADATA\TEST\TS_TEST01.DBF5TS_TESTSTATDEMO30119327202601
5D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF3SYSAUX3513609

Получется что уменьшить можно только до 30119Mb.
А это значит, что таблички нужно переносить в другой tablespace. Можно перенести все сразу таблички, уменьшить размер, и вернуть таблички на место. А можно найти объекты, которые раскиданы по tablespace, и переместить только их.

select dba_extents.owner,
dba_extents.segment_name,
dba_extents.segment_type,
dba_extents.tablespace_name,
dba_extents.file_id,
dba_extents.block_id
from dba_extents,
(select file_id,
max(block_id) max_block_id
from dba_extents
group by file_id) b
where dba_extents.file_id = b.file_id and
dba_extents.block_id = b.max_block_id;

Результат:
 OWNERSEGMENT_NAMESEGMENT_TYPETABLESPACE_NAMEFILE_IDBLOCK_ID
1SYSC_OBJ#CLUSTERSYSTEM186281
2SYS_SYSSMU7$TYPE2 UNDOUNDOTBS1217833
3SYSSYS_IOT_TOP_8797INDEXSYSAUX344905
4SCOTTSALGRADETABLEUSERS449
5TESTTMP_TESTTABLETS_TEST5681

Переносим табличку другой tablespace.

-- Переносим табличку
alter table test.tmp_test move tablespace ts_test2;
-- Переносим индекс
alter index test.tmp_index rebuild tablespace ts_test2;

Также определяем и переносим другие таблики или индексы,и пробуем снова уменьшить tablespace.
Опять таже ошибка, такое ощущение, что что-то забыли проверить. И действительно. Забыли про RECYCLEBIN.
Так как объекты переименовывыются, при попадании в корзину, то поищем эти объекты. Все они начинаются с "BIN$"

select decode(partition_name, null,
segment_name,
segment_name || ':' || partition_name) objectname,
segment_type object_type,
owner,
tablespace_name,
header_block
from dba_segments
where tablespace_name = 'TS_TEST' and
segment_name like 'BIN$%';

Результат:
OBJECTNAMEOBJECT_TYPEOWNERTABLESPACE_NAMEHEADER_BLOCK
1BIN$0IlaH5/6SyGv+h8B8BzJzQ==$0TABLETESTTS_TEST1010955
2BIN$RX30mLpdRd6nHwalSESbuA==$0TABLETESTTS_TEST3854995
3BIN$9aT/N+UpQ3+03oi6GC6dYA==$0INDEXTESTTS_TEST3855203
4BIN$QgU0TDpdQVen+1ciGtl63g==$0TABLETESTTS_TEST1010971

Мне не нужны эти объекты, я их удаляю:

purge table test."BIN$0IlaH5/6SyGv+h8B8BzJzQ==$0";
--...

Все. Уменьшаем tablespace и переносим таблички и индексы обратно.
UPD: Decrease HWM (high watermark) of a tablespace

4 коммент. »

  • Анонимно пишет:  

    Дай бог вам здоровья...
    Совсем с ног сбился ища решение, как tablespace с blob/clob уменьшить

    Спасибо...

  • Артём Круглов пишет:  

    Супер. Спасибо большое.

  • crower пишет:  

    Спасибо! .. просто и доступно давно искал ))

  • Анонимно пишет:  

    Наконец-то четкая инструкция! Просто супер.
    А то от посылов курить доку и общих слов move/rebuild уже устал.

  • Оставьте Ваш комментарий