Очередной раз закончилось место на диске, и в результате поиска свободного места пришла идея уменьшить размер разросшегося 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_NAME | M_ALLOC | M_FREE | M_USED | MAX |
|---|
| 1 | UNDOTBS1 | 335 | 323,69 | 11,31 | 32767,98 |
| 2 | SYSAUX | 360 | 19,38 | 340,63 | 32767,98 |
| 3 | USERS | 5 | 4,56 | 0,44 | 32767,98 |
| 4 | SYSTEM | 680 | 5,56 | 674,44 | 32767,98 |
| 5 | TS_TEST | 32720 | 32607,69 | 112,31 | 32767,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_NAME | FILE_ID | TABLESPACE_NAME | SMALLEST | CURRSIZE | SAVINGS |
|---|
| 1 | D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF | 1 | SYSTEM | 676 | 680 | 4 |
| 2 | D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF | 2 | UNDOTBS1 | 140 | 335 | 195 |
| 3 | D:\ORACLE\ORADATA\TEST\USERS01.DBF | 4 | USERS | 1 | 5 | 4 |
| 4 | D:\ORACLE\ORADATA\TEST\TS_TEST01.DBF | 5 | TS_TESTSTATDEMO | 30119 | 32720 | 2601 |
| 5 | D:\ORACLE\ORADATA\TEST\SYSAUX01.DBF | 3 | SYSAUX | 351 | 360 | 9 |
Получется что уменьшить можно только до 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;
Результат:
| | OWNER | SEGMENT_NAME | SEGMENT_TYPE | TABLESPACE_NAME | FILE_ID | BLOCK_ID |
|---|
| 1 | SYS | C_OBJ# | CLUSTER | SYSTEM | 1 | 86281 |
| 2 | SYS | _SYSSMU7$ | TYPE2 UNDO | UNDOTBS1 | 2 | 17833 |
| 3 | SYS | SYS_IOT_TOP_8797 | INDEX | SYSAUX | 3 | 44905 |
| 4 | SCOTT | SALGRADE | TABLE | USERS | 4 | 49 |
| 5 | TEST | TMP_TEST | TABLE | TS_TEST | 5 | 681 |
Переносим табличку другой 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$%';
Результат:
| OBJECTNAME | OBJECT_TYPE | OWNER | TABLESPACE_NAME | HEADER_BLOCK |
|---|
| 1 | BIN$0IlaH5/6SyGv+h8B8BzJzQ==$0 | TABLE | TEST | TS_TEST | 1010955 |
| 2 | BIN$RX30mLpdRd6nHwalSESbuA==$0 | TABLE | TEST | TS_TEST | 3854995 |
| 3 | BIN$9aT/N+UpQ3+03oi6GC6dYA==$0 | INDEX | TEST | TS_TEST | 3855203 |
| 4 | BIN$QgU0TDpdQVen+1ciGtl63g==$0 | TABLE | TEST | TS_TEST | 1010971 |
Мне не нужны эти объекты, я их удаляю:
purge table test."BIN$0IlaH5/6SyGv+h8B8BzJzQ==$0";
--...
Все. Уменьшаем tablespace и переносим таблички и индексы обратно.
UPD: Decrease HWM (high watermark) of a tablespace
Читать полностью
Мой список блогов