ORA-20000

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