, , ,

Изменение размера 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


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