, , ,

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

четверг, 16 апреля 2009 г. 4 коммент.

Очередной раз закончилось место на диске, и в результате поиска свободного места пришла идея уменьшить размер разросшегося 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 Читать полностью

,

Размер Clob

среда, 1 апреля 2009 г. 0 коммент.

Понадобилось определить размер clob в байтах. В базе установлена многобайтовая кодировка AL32UTF8. Функция length и dbms_lob.getlength возращают результат в символах, функция lengthb не применима для clob, если используется многобайтовая кодировка (функция возращает ошибку "ORA-22998: CLOB и NCLOB в многобайтовых кодовых таблицах не поддерживается").

Для определения размера пришлось сначала конвертировать clob в blob, а потом уже определить размер blob в байтах.


function clob_to_blob(p_clob in clob
) return blob
is
v_blob blob := null;


v_in pls_integer := 1;
v_out pls_integer := 1;

v_lang pls_integer := dbms_lob.default_lang_ctx;
v_warning pls_integer := dbms_lob.no_warning;
begin
if (p_clob is not null) then
dbms_lob.createtemporary(v_blob, true, dbms_lob.session);

dbms_lob.convertToBlob( dest_lob => v_blob
,src_clob => p_clob
,amount => dbms_lob.getlength(p_clob)
,dest_offset => v_in
,src_offset => v_out
,blob_csid => dbms_lob.default_csid
,lang_context => v_lang
,warning => v_warning
);
end if;
return v_blob;
end clob_to_blob;

function get_clob_size_in_bytes(p_clob in clob
) return number
is
v_blob blob;
v_result number := null;
begin
v_blob := clob_to_blob(p_clob => p_clob);
if (v_blob is not null) then
v_result := dbms_lob.getlength(v_blob);

if (dbms_lob.istemporary(lob_loc => v_blob) = 1) then
dbms_lob.freetemporary(v_blob);
end if;
end if;
return v_result;
end get_clob_size_in_bytes;

Подсказка взята отсюда How to find the size of clob cloumn when the character set is AL32UTF8??

UPD: Size of CLOB or BLOB in Oracle DB


Читать полностью