вторник, 30 марта 2010 г.

Уменьшение размера tablespace в Oracle.

После чистки таблиц в базе, а затем выполнения их дефрагментации, высвободилось достаточно много свободного места в табличном пространстве. В итоге 24 datafile's размером по 32Гб каждый получились заняты процентов по 20-25%. Решено было уменьшить размеры самих datafile's, чтобы освободить место на файловой системе.

Размеры tablespace's можно посмотреть так:

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 (+);


Результат выглядит примерно так:





Здесь видно, что USERS занимает 618Gb, а использует 129Gb. Посмотрим до какого размера можно сжать 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(+);


Результат:





Там, где есть возможность уменьшить размер datafale's - уменьшаем. Т.е. datafile
E:\ORACLE\DATA\DSAUDIT\USERS19.DBF можно уменьшить до 6200Мб.

Пример:

SQL>alter database datafile 'E:\ORACLE\DATA\DSAUDIT\USERS19.DBF' resize 6200M;

Остальные datafile's уменьшить не получается, т.к. в них данные раскиданы по всему пространству. А это значит, что таблички нужно переносить в другой 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;

Результат:





Для переноса таблицы в другой tablespace:

SQL>alter table PINECONE.QUERY_TABLE_TBL move tablespace TEST;

Затем в перенесённой таблице необходимо сделать rebuild индексов.
Посмотрим какие индексы есть в таблице

SQL>SELECT STATUS, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'QUERY_TABLE_TBL';

STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE QTT_OWNER_NAME

И делаем непосредственно ребилд

SQL> ALTER INDEX SYS_C005185 REBUILD;

Проверяем

SQL>SELECT STATUS, INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'QUERY_TABLE_TBL';

STATUS INDEX_NAME
-------- ------------------------------
VALID QTT_OWNER_NAME

Для переноса индексов в другой tablespace:

SQL>alter index PINECONE.SYS_C005180 rebuild tablespace TEST;

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

Определяем объекты, находящиеся в корзине:

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 = 'PINECONE' and
segment_name like 'BIN$%';
И удаляем их, например так:

SQL>purge table PINECONE."BIN$0IlaH5/6SyGv+h8B8BzJzQ==$0";

Уменьшаем tablespace.

SQL>alter database datafile 'F:\ORACLE\DATA\DSAUDIT\USERS23.DBF' resize 10M;
...
...
...
В итоге получилось так:
Теперь переносим таблицы и индексы обратно.

Составлено на основе материалов http://all-oracle.ru, http://ora-20000.blogspot.com/2009/04/tablespace.html 

1 комментарий: