Освобождение места (реорганизация файлов табличных пространств) Oracle

https://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm
https://oracle-base.com/articles/misc/reclaiming-unused-space

Размер табличных пространств Oracle

Другой запрос для вычисления размера табличных пространств Oracle:
http://www.dba-oracle.com/t_tablespace_script.htm

set lines 132
set pages 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)" format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)" format 9,999,990.00
column "FREE (MB)" format 9,999,990.00
column "% USED" format 990.00

select
a.tablespace_name,
a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
(nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
(select
tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from
dba_data_files
group by
tablespace_name ) a,
(select
tablespace_name,
sum(bytes) tot_used
from
dba_segments
group by
tablespace_name ) b
where
a.tablespace_name = b.tablespace_name (+)
and
a.tablespace_name not in
(select distinct
tablespace_name
from
dba_temp_files)
and
a.tablespace_name not like 'UNDO%'
order by 1;

Перемещение файлов табличных пространств

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '/opt/oracle/oradata/ORACL11';
CREATE OR REPLACE DIRECTORY DEST_DIR AS '/mnt/hdd2tb2/oradata/ORACL11';

ALTER TABLESPACE USERS OFFLINE NORMAL;
-- БЛОК ПОВТОРЕНИЙ - НАЧАЛО --
/* Этот блок нужно повторить для каждого перемещаемого файла */
BEGIN
DBMS_FILE_TRANSFER.COPY_FILE(
source_directory_object => 'SOURCE_DIR',
source_file_name => 'users08.dbf',
destination_directory_object => 'DEST_DIR',
destination_file_name => 'users08.dbf');
END;
/

alter tablespace USERS rename datafile '/opt/oracle/oradata/ORACL11/users08.dbf' to '/mnt/hdd2tb2/oradata/ORACL11/users08.dbf';
-- БЛОК ПОВТОРЕНИЙ - КОНЕЦ --

ALTER TABLESPACE USERS ONLINE; /* Поднимать ТП лучше после перемещения каждого файла для проверки целостности */

/* Послеу успешного включения ТП можно удалить файлы из SOURCE_DIR */

Переименование и перемещение файлов данных табличных пространств Oracle 11g

Проверить рекомендации:

1) http://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm#ADMIN11430
2) http://docs.oracle.com/cd/E16655_01/server.121/e17636/dfiles.htm#ADMIN012
3) https://community.oracle.com/thread/920272?tstart=0

P.S. Текущая версия СУБД: Oracle 11.2.0.4

Oracle и дохлый винчестер

Началось все с сообщений о поврежденных файлах табличного пространства USERS (пример сообщения для одного из таких файлов):
26.12.13 17:24:20.523,ORB-12117208(57),ERROR,TransactJob,
com.bssys.db.jdbc.DBSQLException: ORA-01115: ошибка ввода/вывода при чтении блока из файла (блок # )
ORA-01110: файл данных 25: '/mnt/hdd2tb/oracle/ORACLE11/users20.dbf'
ORA-27072: Ошибка ввода/вывода файла
Additional information: 3
Additional information: 3632835
Additional information: 8192

Таких сообщений становилось все больше и больше.
В чем моя ошибка?
Во-первых, в том, что я не включил режим "ARCHIVELOG", поэтому восстановить файлы в RMAN не удалось.
Во-вторых, в том, что я его включил уже после ошибки: это привело к аварийной остановке Oracle, т.к. быстро растущие файлы FAST-RECOVERY сожрали все оставшееся свободное место на HDD с системой.
Ладно, довольно-таки быстро сообразил режим отключить и удалить файлы. Место свободное появилось.

После этого настала очередь борьбы с проблемой: Что делать, если умер HDD с частью табличных пространств и Oracle из-за этого не запускается, сообщая при startup'е:
ORA-01157: cannot identify/lock data file 15 - see DBWR trace file
ORA-01110: data file 15: '/mnt/hdd2tb/oracle/ORACLE11/USERS2/users2_07.dbf'
ORA-27037: unable to obtain file status
?

Помогло отключение недостающих DATA-файлов:
alter database datafile '/mnt/hdd2tb/oracle/ORACLE11/users10.dbf' offline drop;
После этого Oracle запустился.
Далее дело техники: либо удалить схемы, которые лежали на недостающих DATA-файлах (drop user cascade;), либо пересоздать табличное пространство
- drop tablespace including contents;
- см. описание "create tablespace".

----после 3-х часов безуспешных попыток удалить датафайлы, чтобы освободить место на диске---
В итоге, на Oracle 11g система не позволяет удалить датафайлы из USERS, сообщая:
SQL> alter tablespace USERS drop datafile '/opt/oracle/oradata/ORACLE11/users25.dbf';
alter tablespace USERS drop datafile '/opt/oracle/oradata/ORACLE11/users25.dbf'
*
ERROR at line 1:
ORA-01662: tablespace 'USERS' is non-empty and cannot be made temporary

Плюнул. Пересоздал БД Oracle.

Oracle: Temporary tablespace

Temporary tablespace shrink/clean/recreate:
http://stackoverflow.com/questions/1824572/how-to-shrink-temp-tablespace-in-oracle

Oracle - free space in tablespace

Проверка свободного места в табличном пространстве
http://www.orafaq.com/wiki/Tablespace#Check_free.2Fused_space_per_tablespace

Увеличение tablespace в Oracle

На примере tablespace USERS, с автоматическим увеличением файла и начальным размером 1 МБ:
alter tablespace USERS add datafile '<путь к dbf>' size 1 M autoextend on;

Дефрагментация табличного пространства в Oracle

Необходимо автоматизировать дефрагментацию табличного пространства.