Удаление «большого» манданта в SAP R/3 4.0
Давным-давно, в одной очень далекой Галактике… Тьфу, это что-то не то… Несколько лет назад, когда я еще был администратором базы данных Oracle и «сапёром» (администратором SAP R/3) мне понадобилось удалить один из мандантов в системе, в связи с тем, что этот мандант мигрировал на другой сервер и данные уже были не нужны, но место на дисковом массиве занимали. Проблема была ровно одна — данные в манданте занимали почти терабайт из 1.2 Тб общего размера всей БД. Остальная обвязка была следующая: Oracle 8.1.7, дисковый массив на полтора терабайта, и нет свободного места на других массивах.
Поскольку удаляемый мандант большой, то стандартный способ предлагаемый SAP не удачен — он реально пытается удалить данные в таблицах, что гарантирует не только его ОЧЕНЬ медленную работу но и забивание табличного пространства PSAPROLL, где хранятся сегменты отката (сделать его размером 100-200 гигабайт — не лучшая идея в нашем случае).
В итоге, после размышлений подумалось мне, что для данного случая больше подходит метод не удаления большей части данных, а выборки оставшихся данных и пересоздания таблицы со вставкой их обратно (CREATE TABLE NOLOGGING AS SELECT). Самое главное, что с помощью отказа от генерации redo-данных с помощью опции NOLOGGING можно сильно ускорить процесс. Единственное ограничение — данный метод не подходит для таблиц имеющих поля типа LONG, но, к счастью, их немного и они не такие огромные.
Действуем следующим образом:
Все действия выполняются под пользователем SYSTEM. У SYSTEM должны быть полномочия (помимо обычных): ALTER ANY INDEX, ALTER ANY SNAPSHOT, ALTER ANY TABLE, DELETE ANY TABLE, INSERT ANY TABLE, SELECT ANY TABLE.
Надо создать таблицы для хранения списков индексов и таблиц SAP R/3 для того, чтоб каждый раз при запуске не запрашивать их заново:
CREATE TABLE ALL_TABLES_SAPR3_MANDT NOLOGGING AS
SELECT tc.table_name
FROM SYS.ALL_OBJECTS o, SYS.ALL_TAB_COLUMNS tc
WHERE tc.table_name = o.object_name
AND o.OBJECT_TYPE='TABLE'
AND tc.OWNER = 'SAPR3'
AND tc.COLUMN_NAME = 'MANDT'
AND SUBSTR(tc.TABLE_NAME,1,1)<>'/'
CREATE TABLE ALL_INDEXES_SAPR3 NOLOGGING AS
SELECT OBJECT_NAME
FROM SYS.ALL_OBJECTS
WHERE OWNER='SAPR3'
AND OBJECT_TYPE='INDEX'
AND SUBSTR(OBJECT_NAME,1,1)<>'/'
Надо создать процедуру EXECUTE_IMMEDIATE для запуска запросов (Oracle 8.1.7, что поделаешь…):
CREATE OR REPLACE PROCEDURE execute_immediate( p_sql_text VARCHAR2 ) IS
COMPILATION_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT(COMPILATION_ERROR,-24344);
l_cursor INTEGER DEFAULT 0;
rc INTEGER DEFAULT 0;
stmt VARCHAR2(1000);
BEGIN
l_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(l_cursor, p_sql_text, DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(l_cursor);
DBMS_SQL.CLOSE_CURSOR(l_cursor);
EXCEPTION WHEN COMPILATION_ERROR THEN DBMS_SQL.CLOSE_CURSOR(l_cursor);
WHEN OTHERS THEN
BEGIN
DBMS_SQL.CLOSE_CURSOR(l_cursor);
raise_application_error(-20101,sqlerrm || ' when executing ''' || p_sql_text || ''' ');
END;
END;
Создаем функцию UNLIMITED_INDEX:
CREATE OR REPLACE FUNCTION UNLIMITED_INDEX( SCHEMA VARCHAR2 ) RETURN BOOLEAN AS
t_name SYS.ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
CURSOR c_indexes IS SELECT * FROM ALL_INDEXES_SAPR3;
sql_text VARCHAR2(2000);
v_err_code NUMBER;
v_err_msg VARCHAR2(200);
BEGIN
FOR r_index IN c_indexes
LOOP
t_name := r_index.object_name;
DBMS_OUTPUT.PUT_LINE(t_name);
sql_text := 'ALTER INDEX "' || SCHEMA || '"."' || t_name || '" STORAGE ( MAXEXTENTS UNLIMITED)';
/*DBMS_OUTPUT.PUT_LINE(sql_text);*/
EXECUTE_IMMEDIATE(sql_text);
END LOOP;
CLOSE c_indexes;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);
RETURN FALSE;
END;
END;
И функцию DELETE_MANDT:
CREATE OR REPLACE FUNCTION DELETE_MANDT( mandt VARCHAR2 ) RETURN BOOLEAN AS
t_owner CONSTANT SYS.ALL_TAB_COLUMNS.OWNER%TYPE := 'SAPR3';
t_name SYS.ALL_TAB_COLUMNS.TABLE_NAME%TYPE;
CURSOR c_tables IS SELECT * FROM ALL_TABLES_SAPR3_MANDT;
sql_text VARCHAR2(2000);
l_start number;
l_exist number default 1;
l_long number;
v_err_code NUMBER;
v_err_msg VARCHAR2(200);
BEGIN
FOR r_tables IN c_tables
LOOP
l_start := dbms_utility.get_time;
t_name := r_tables.table_name;
DBMS_OUTPUT.PUT_LINE(t_name);
EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || t_owner || '.' || t_name || ' WHERE MANDT=:mandt AND ROWNUM=1'
INTO l_exist
USING mandt;
IF l_exist > 0 THEN
SELECT count(*)
INTO l_long
FROM SYS.ALL_OBJECTS o, SYS.ALL_TAB_COLUMNS tc
WHERE tc.table_name = o.object_name
AND o.object_name = t_name
AND o.OBJECT_TYPE='TABLE'
AND tc.OWNER = t_owner
AND (tc.DATA_TYPE='LONG' OR tc.DATA_TYPE='LONG RAW');
IF l_long = 0 THEN
sql_text := 'CREATE TABLE ' || t_owner || '_' || t_name || ' NOLOGGING AS SELECT * FROM ' || t_owner || '.' || t_name || ' WHERE MANDT<>''' || mandt || '''';
/*DBMS_OUTPUT.PUT_LINE(sql_text);*/
EXECUTE_IMMEDIATE(sql_text);
sql_text := 'TRUNCATE TABLE ' || t_owner || '.' || t_name;
/*DBMS_OUTPUT.PUT_LINE(sql_text);*/
EXECUTE_IMMEDIATE(sql_text);
sql_text := 'ALTER TABLE ' || t_owner || '.' || t_name || ' STORAGE ( MAXEXTENTS UNLIMITED)';
/*DBMS_OUTPUT.PUT_LINE(sql_text);*/
EXECUTE_IMMEDIATE(sql_text);
sql_text := 'INSERT /*+ APPEND */ INTO ' || t_owner || '.' || t_name || ' SELECT * FROM ' || t_owner || '_' || t_name;
/*DBMS_OUTPUT.PUT_LINE(sql_text);*/
EXECUTE_IMMEDIATE(sql_text);
COMMIT;
sql_text := 'DROP TABLE ' || t_owner || '_' || t_name;
/*DBMS_OUTPUT.PUT_LINE(sql_text);*/
EXECUTE_IMMEDIATE(sql_text);
ELSE
sql_text := 'DELETE FROM ' || t_owner || '.' || t_name || ' WHERE MANDT<>''' || mandt || '''';
/*DBMS_OUTPUT.PUT_LINE(sql_text);*/
EXECUTE_IMMEDIATE(sql_text);
END IF;
END IF;
DBMS_OUTPUT.PUT_LINE(round((dbms_utility.get_time-l_start)/100, 2) || ' seconds...');
END LOOP;
CLOSE c_tables;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
BEGIN
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM, 1, 200);
DBMS_OUTPUT.PUT_LINE ('Error code: '||v_err_code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);
RETURN FALSE;
END;
END;
Затем командами типа ALTER ROLLBACK SEGMENT «RBS_0» STORAGE ( MAXEXTENTS UNLIMITED); устанавливаем неограниченного количество экстентов во всех сегментах отката (для того чтобы иметь возможность удалять данные в некоторых таблицах для которых метод не годится — RFBLG все-таки большая таблица, а она должна влезть в сегмент). Также не забываем увеличить само табличное пространство до нужного размера.
Запускаем функцию UNLIMITED_INDEX для установки неограниченного количества экстентов в индексах SAP R/3 (аналогичное действие для таблиц делается в функции удаления манданта).
Ну и потом запускаем функцию DELETE_MANDT с параметром «номер манданта», который надо удалить. Если программа остановится с ошибкой, надо будет её устранить (название ошибки отображается) и запустить функцию заново.
Функция выдает в DBMS_OUTPUT название таблицы и время её обработки.
В итоге у меня получилось удалить почти терабайт данных за несколько часов вместо нескольких дней, и при этом не потребовалось увеличивать емкость массива или переносить данные на другой массив. Понятно, что ситуация удаления большого массива при том что остается небольшой мандант достаточно необычная, но в данном случае всё получилось очень удачно. В случае примерно равных мандантов данный вариант решения уже не подойдет, проще будет воспользоваться стандартной функцией SAP R/3.
- dukeyusupov
- 0
- 363