Удаление «большого» манданта в 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.

Добавить комментарий

Заполните поля или щелкните по значку, чтобы оставить свой комментарий:

Логотип WordPress.com

Для комментария используется ваша учётная запись WordPress.com. Выход /  Изменить )

Google photo

Для комментария используется ваша учётная запись Google. Выход /  Изменить )

Фотография Twitter

Для комментария используется ваша учётная запись Twitter. Выход /  Изменить )

Фотография Facebook

Для комментария используется ваша учётная запись Facebook. Выход /  Изменить )

Connecting to %s