Скрипт определения размера таблиц в oracle и очистка данных

Часта ситуация когда в Oracle размер таблиц достигает больших величин и мы оказываемся в положении когда прикладное приложение просто не может записать что-либо в базу данных. Пришлось столкнуться с такой проблемой в Oracle XE, когда размер уперся в лицензионное ограничение этой бесплатной версии oracle.

Все операции будем производить через консоль линукс-сервера, на котором установлена наша СУБД.

Для начала логинимся под root и переключаемся на пользователя под которым работает демон оракла, у меня это пользователь oracle.

#su oracle

1	#su oracle

Затем устанавливаем переменные окружения для запуска утилиты sqlplus

$export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

1	$export ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

Здесь следует обратить внимание:при установке переменной ORACLE_HOME в конце не должен стоять слэш! Иначе мы потом получим сообщение о том , что не можем присоединиться к инстансу оракла!
И устанавливаем инстанс к которому будем подключаться, у меня это иснтанс XE

$export ORACLE_SID=XE

1	$export ORACLE_SID=XE

Далее запускаем консольного клиента Oracle с правами sysdba

$/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/sqlplus / as sysdba

1	$/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/sqlplus / as sysdba

Далее все запросы запускаем в sqlplus.

Для скрипта определения размера таблиц oracle нужно будет указать владельца таблиц, поэтому сначала вытащим из базы всех возможных владельцев таблиц:

SELECT distinct owner FROM dba_tables;

1	SELECT distinct owner FROM dba_tables;

и следом выполняем сам мега-скрипт:

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND   s.owner = i.owner
AND   s.segment_type = 'INDEX'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

COLUMN TABLE_NAME FORMAT A32

COLUMN OBJECT_NAME FORMAT A32

COLUMN OWNER FORMAT A10

SELECT

owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg

FROM

(SELECT segment_name table_name, owner, bytes

FROM dba_segments

WHERE segment_type = 'TABLE'

UNION ALL

SELECT i.table_name, i.owner, s.bytes

FROM dba_indexes i, dba_segments s

WHERE s.segment_name = i.index_name

AND s.owner = i.owner

AND s.segment_type = 'INDEX'

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.segment_name

AND s.owner = l.owner

AND s.segment_type = 'LOBSEGMENT'

UNION ALL

SELECT l.table_name, l.owner, s.bytes

FROM dba_lobs l, dba_segments s

WHERE s.segment_name = l.index_name

AND s.owner = l.owner

AND s.segment_type = 'LOBINDEX')

WHERE owner in UPPER('&owner')

GROUP BY table_name, owner

HAVING SUM(bytes)/1024/1024 > 10 /* Ignore really small tables */

ORDER BY SUM(bytes) desc

;

(не забываем нажать ентер :))
У нас спрашивается для какого владельца посчитать размеры таблиц — указываем, жамкаем ентер и наконец-то мы видим сколько места занимает каждая таблица!

У меня оказалось, что прикладное приложение записывает логи ошибок в базу данных и за несколько лет эта таблица разрослась до безумных размеров. И удалить ее за один запрос не получилось, так как не хватало места для undo table space. Поэтому долго и нудно пришлось чистить по частям:

delete from shit_app.errorlogs WHERE ROWNUM < 200000;

1	delete from shit_app.errorlogs WHERE ROWNUM < 200000;

Но и после того как все данные были очищены таблица продолжает занимать все-тоже место! Здесь нам поможет сжатие таблицы. Для начала мы должны разрешить для таблицы перемещение строк:

alter table shit_app.errorlogs enable row movement;

1	alter table shit_app.errorlogs enable row movement;

И после этого сжимаем базу таблицу запросом:

alter table shit_app.errorlogs shrink space;

1	alter table shit_app.errorlogs shrink space;

И теперь наступило полное счастье ) Пользователи могут работать дальше!