Партиции в oracle что это

Oracle: партицирование таблиц, как управлять секциями

Lorem ipsum dolor

Партицирование таблиц Oracle по диапазон у значений основывается на каком-либо столбце табличных данных, который содержит уникальные сведения. Создание отдельных табличных секций происходит по такому принципу:

CREATE TABLE MY.NEWTABLE ( ISN NEWNUMBERS. UPDATED NEWDATES) TABLESPACES HSTNEWDATA

PARTITION BY RANGE (ISN)

(PARTITION PARTISAN_01 VALUE LESS THAN (1500),

PARTITION PARTISAN_02 VALUE LESS THAN (2500),

PARTITION PARTISAN_03 VALUE LESS THAN (3500),

PARTITION PARTISAN_MAXIMUM VALUE LESS THAN (MAXVALUES)

) ENABLE ROW MOVEMENT;

Партицирование таблиц Oracle по спискам значений

Такой метод партицирования удобен, когда присутствует возможность определить список элементов конкретного столбца, чтобы по ним разбить табличное представление на отдельные области. Вот как это происходит на практике:

CREATE TABLE MY.NEWTABLE (ISN NEWNUMBER,UPDATED NEWDATES, L

PARTID AS (TO_NEWNUMBERS(TO_CHAR(UPDATEDS, ’ ’)))

) PARTITION BY LIST(PARTID)

( PARTITION TABLEPART_3 VALUES (3),

PARTITION TABLEPART_4 VALUES (4),

PARTITION TABLEPART_14 VALUES (14));

Партицирование по хеш-значению

Первые два способа партицирования наиболее популярны и часто используются. Все способы, которы е будут описаны ниже , применяются в специфич еских случаях, в том числе и разбивка на табличные секции по хеш-значению. Данный способ основывается на хеш-функциях, поэтому считается наиболее точным.

Вот как этот способ выглядит на практике:

CREATE TABLE MY.NEWTABLE (TASKSISN NEWNUMBERS, OBJECTISN NEWNUMBERS, K PARAMETRS NEWNUMBERS,

CONSTRAINT NEWPK_LISTIN PRIMARY NEWKEY(TASKSISN,OBJECTISN,OBJECTROWID,K PARAMETRS)

) MYORGANIZATION INDEX INCLUDING PARAMETRS OVERFLOW PARTITION BY HASH (TASKSISN) PARTITIONS 24

Составное партицирование

При таком методе внутри одной секции образу е тся несколько связанных подсекций. А вообще, такой метод понимает смешанное применение нескольк их других методов, описанных чуть выше , н апример , по списку значений и хеш-значениям и др. Причем сочетания способов мо гут быть различным и .

Вот как выглядит составное партицирование таблиц Oracle, где одновременно используются первые два способа, описанные сегодня в статье:

CREATE TABLE MYTABLE.NEWPAY_ORD_RECORDING ( ISN NEWNUMBERS, K

NEWPAY_NEWDATA NEWDATES, NEWPAYER_NEWNAMES VARCHAR3(255). K NEWSTATUS NEWNUMBERS ) TABLESPACE HSTNEWDATA

PARTITION BY RANGE (NEWPAY_NEWDATA)

INTERVAL (NEWINTERVAL ‘7’ DAYS)

SUBPARTITION BY LIST (NEWSTATUS)

SUBPARTITION NEWTEMPLATE (

SUBPARTITION NEWSTATUSK) VALUE0 (0) K TABLESPACE TRNEWDATA1,

SUBPARTITION NEWSTATUS_1 VALUE1 (1) K TABLESPACE TRNEWDATA2,

SUBPARTITION NEWSTATUSK VALUE2 (2) K TABLESPACE TRNEWDATA3 )

(PARTITION PK015KK1 VALUE LESS K

THAN(TO_NEWDATE(‘02.02.2022′,’DD.MM.YYYY’)))

ENABLE ROW MOVEMENT;

Заключение

Сегодня мы лишь поверхностно коснулись темы «Партиционирование таблиц Oracle» и привели простейшие практические примеры, чтобы вы могли ознакомит ь ся с тем , как оно выглядит. В следующих статьях мы подробнее остановимся на каждом отдельном методе, потому что по каждому из ни есть что рассказать.

Мы будем очень благодарны

если под понравившемся материалом Вы нажмёте одну из кнопок социальных сетей и поделитесь с друзьями.

Cекционирование по циклу таблиц c LOB-столбцами.

От версии к версии Oracle расширяются возможности секционирования таблиц. Например, в Oracle 11g появилась возможность автоматического создания новой секции, расширен перечень комбинаций секций и подсекций, использование в качестве ключа секционирования виртуального, а не только реального столбца и др. Последняя новая возможность была использована нами при секционировании по циклу.

Cекционирование по циклу

Под секционированием таблиц по циклу понимаются секционирование, выполненное в соответствии с двумя правилами. Первое правило – таблица должна содержать фиксированное количество секций, равное числу дней в месяце (31 секция) или в году (366 секций), либо числу месяцев (12 секций). Второе правило — данные в одну и ту же секцию попадают с определенной периодичностью (цикличностью). Периодичность зависит от метода секционирования по циклу и может быть равна 31, 366 или 12. Например, данные попадают в секцию определенного дня года, независимо от номера года.

Идея секционирования по циклу возникла из следующей практической задачи. В одну из таблиц с именем DOCIMAGE записывается большой поток данных, основной объем которого составляют BLOB’ы, помещаемые в столбец OLEOBJECT таблицы DOCIMAGE. Вероятность обращения к этим данных со временем быстро снижается, однако, сохраняется требование их непрерывной доступности, т.е. в любой момент времени эти BLOB’ы могут быть востребованы. Для уменьшения стоимости хранения большого объема данных было решено переместить в архив основную по объему часть данных (а, именно, столбцы с BLOB-данными) и расположить их на более дешевом и более медленном устройстве хранения EMC Centera.

Для этого было проведено секционирование таблицы DOCIMAGE (с секциями на каждый день года) по столбцу PARTID, в котором номер текущего дня формируется из столбца CREATED соответствующим триггером. При создании таблицы было задано, что сама таблица размещается в одном табличном пространстве, а ее BLOB-данные в другом табличном пространстве IMAGETBS. Секции ежедневно заполняются, при этом раз в сутки запускается процесс, который очищает BLOB-столбцы более ранних секций (двух месячной давности и не имеющих запрета на очистку) со Shrink-сжатием табличного пространства этих секций. Для первой секции команда Shrink, например, имеет вид:

ALTER TABLE DOCIMAGE MODIFY PARTITION DOCIMAGE_1 LOB(OLEOBJECT) (SHRINK SPACE);

В случае, если пользовательский запрос запрашивает BLOB-данные, которых нет в оперативной таблице DOCIMAGE, система извлекает данные из архива с устройства EMC Centera и помещает их обратно в соответствующую секцию таблицы DOCIMAGE. После этого данные обрабатываются и остаются в оперативной таблице DOCIMAGE, пока не попадут в очередной цикл очистки. Таким образом, в оперативной таблице DOCIMAGE содержатся свежие данные за последние два месяца плюс более старые данные, которые недавно были извлечены из архива. Все остальные BLOB-данные располагается на более медленном архивном устройстве. Данная секционированная таблица успешно функционирует 1,5 года и показала эффективность принятого решения.

Преимущества данной реализации:

цикличность буфера позволяет ограничить количество секций с возможностью сохранения данных за неограниченный период времени. Из-за постоянного удаления устаревших данных в архив размеры секций остается приемлемыми;
наличие секций по дням позволяет получить приемлемое время сжатия табличного пространства по Shrink. Эти важно, так как необходимость сохранения в секциях отдельных неудаляемых BLOB’ов не позволяет применить операцию быстрой Truncate-очистки секции;
по сравнению с нециклическим секционированием проще настраиваются циклически исполняемые процессы по обслуживанию таблицы (архивирование, очистка и т.д.).

Команда создания таблицы DOCIMAGE с размещением BLOB’ов в другом табличном пространстве приведена ниже:

CREATE TABLE ADM.DOCIMAGE ( ISN NUMBER NOT NULL, CREATED DATE, … OLEOBJEC BLOB, PARTID NUMBER ) TABLESPACE ADMDATA LOB (OLEOBJECT) STORE AS ( TABLESPACE IMAGETBS ENABLE STORAGE IN ROW CACHE STORAGE ( BUFFER_POOL DEFAULT ) ) PARTITION BY RANGE (PARTID) ( PARTITION DOCIMAGE_1 VALUES LESS THAN (2), . PARTITION DOCIMAGE_366 VALUES LESS THAN (367) ) ENABLE ROW MOVEMENT;

LOB (OLEOBJECT) STORE AS ( TABLESPACE IMAGETBS ENABLE STORAGE IN ROW CACHE )

позволяет не только обеспечивать ввод данных LOB-столбца OLEOBJECT в другое табличное пространство IMAGETBS (сама таблица находиться в табличном пространстве ADMDATA), но и задавать режимы ввода LOB данных. Так, при наличии фразы ENABLE STORAGE IN ROW маленькие LOB’ы размером менее 4000 байт будут храниться в самой таблице, а при размере LOB’ов более 4000 байт они будут размещаться в LOB-сегменте (при замене фразы ENABLE STORAGE IN ROW на DISABLE STORAGE IN ROW все LOB’ы будут храниться в LOB-сегменте). Фраза CACHE обеспечивает кэширование данных LOB.

При создании таблицы с LOB-столбцом для него формируются LOB-сегмент и LOB-индексный сегмент, даже если потом они окажутся пустыми вследствие того, что все LOB’ы останутся в строках таблицы.

Создаваемые LOB-сегменты можно увидеть по запросу:

SELECT OWNER, TABLE_NAME, COLUMN_NAME, SEGMENT_NAME SEGMENT_LOB, TABLESPACE_NAME, INDEX_NAME, IN_ROW, CHUNK, PCTVERSION, CACHE, PARTITIONED FROM DBA_LOBS WHERE OWNER='ADM' AND TABLE_NAME='DOCIMAGE';

TABLE_NAME COLUMN_NAME SEGMENT_LOB INDEX_NAME ---------- ----------- ------------------------- -------------------- DOCIMAGEOLE OBJECT SYS_LOB0000488329C00006$$ SYS_IL0000488329C00006$$

В случае секционирования таблицы с LOB-столбцами каждой секции таблицы будет соответствовать две новых секции: секция LOB-сегмента и LOB-индексный сегмент. Результаты секционирования таблицы с LOB-столбцами можно увидеть запросом:

SELECT D.TABLESPACE_NAME ,L.TABLE_OWNER,L.TABLE_NAME, L.COLUMN_NAME ,L.PARTITION_POSITION POS,L.PARTITION_NAME, L.LOB_PARTITION_NAME,LOB_INDPART_NAME, L.SEGMENT_CREATED,LOB_NAME,INDEX_NAME FROM ALL_LOB_PARTITIONS L, ALL_LOBS D WHERE L.TABLE_OWNER=D.OWNER AND L.TABLE_NAME=D.TABLE_NAME AND L.COLUMN_NAME=D.COLUMN_NAME AND L.TABLE_OWNER='ADM' AND L.TABLE_NAME='DOCIMAGE' ORDER BY L.PARTITION_POSITION;

В дальнейшем подход секционирования по циклу был развит и реализован в ряде других таблиц, в которых данные хранятся ограниченное время (несколько дней или месяцев). Такие таблицы были секционированы по циклу с быстрой Truncate-очисткой устаревших секций.

Опыт показал, что эффективно секционируются по циклу (помимо указанной выше таблицы типа DOCIMAGE) таблицы трех видов:

секционированные таблицы с числом секций, равным числу дней в году с ежедневной очисткой устаревшей секции;
секционированные таблицы с числом секций, равным числу месяцев в году (12 секций) с ежемесячной очисткой устаревшей секции с данными за месяц;
секционированные таблицы с числом секций равным силу дней в месяце (31 секция) с ежедневной очисткой устаревшей секции с данными за день.

В этих таблицах преимуществом секционирования по циклу является то, что не надо создавать новые и уничтожать старые секции, а главное преимущество в том, что очистка секции по truncate происходит быстро и, что еще более существенно, очитка при Truncate идет с освобождением табличного пространства для ввода новых данных.

Секционирование по циклу таблицы PAGES с секциями по месяцам в течение года.

Идея секционирования по циклу была реализована в таблице PAGES с BLOB-столбцом PAGE_DATA. В этих BLOB-элементах содержится отсканированные документы по страхованию. Объем данных, вводимых ежемесячно в таблицу PAGES, более 500 Гб, и он продолжает расти. В силу этого, хранение информации в течение нескольких месяцев потребовало бы терабайты пространства. В связи с этим, было принято решение разбить таблицу PAGES на 12 месяцев и хранить данные только за текущий и предыдущий месяц (за 2 месяца), а более старые секции очищать по Truncate с освобождением табличного пространства для ввода новых данных. В результате объем хранимых данных стал более ограничен и предсказуем, а очистка по Truncate (вместо обычного Delete, требующего использования команды Shrink для освобождения табличного пространства) происходит быстро с минимальной загрузкой процессора. Секционирование по циклу таблицы PAGES проведено по методу RANGE, где ключем секционирования выступил вновь созданный в таблице столбец PARTID, принимающий значения от 1 до 12 (по числу месяцев). Столбец PARTID заполняется триггером при вводе новой строки в таблицу. В триггере выполняется функция to_number(to_char(CREATED,’MM’)), которая обрабатывает столбец CREATED таблицы (дата создания записи), выделяя значение месяца.

Структура таблицы PAGES в схеме ADM приведена ниже.

CREATE TABLE ADM.PAGES ( ID NUMBER, PAGE_DATA BLOB, CREATED DATE, … PARTID NUMBER ) TABLESPACE ADMDATA PARTITION BY RANGE (PARTID) ( PARTITION PAGES_1 VALUES LESS THAN (2) , PARTITION PAGES_2 VALUES LESS THAN (3) , PARTITION PAGES_3 VALUES LESS THAN (4) , PARTITION PAGES_4 VALUES LESS THAN (5) , PARTITION PAGES_5 VALUES LESS THAN (6) , PARTITION PAGES_6 VALUES LESS THAN (7) , PARTITION PAGES_7 VALUES LESS THAN (8) , PARTITION PAGES_8 VALUES LESS THAN (9) , PARTITION PAGES_9 VALUES LESS THAN (10), PARTITION PAGES_10 VALUES LESS THAN (11), PARTITION PAGES_11 VALUES LESS THAN (12), PARTITION PAGES_12 VALUES LESS THAN (13) ) ENABLE ROW MOVEMENT; --возможность перемещения данных между секциями

Результаты секционирования могут быть просмотрены запросом:

Select * From ALL_TAB_PARTITIONS where table_owner='ADM' and table_name ='PAGES';

Вид триггера, заполняющего столбец PARTID, приведен ниже:

CREATE OR REPLACE TRIGGER ADM.PAGES_BIU BEFORE INSERT ON PAGES REFERENCING FOR EACH ROW BEGIN :NEW.PARTID:=to_number(to_char(:NEW.Created,'MM')); END;

Очистка по Truncate, например, второй секции выполняется по команде:

ALTER TABLE ADM.PAGES TRUNCATE PARTITION(PAGES_2) UPDATE GLOBAL INDEXES;

где фраза UPDATE GLOBAL INDEXES позволяет обеспечить после Truncate доступность глобальных индексов таблицы.

Контроль над освобождением табличного пространства (в байтах) после выполнения Truncate можно осуществлять по запросу:

SELECT TABLESPACE_NAME, SUM(BYTES) FREE_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME ORDER BY 1;

Секционирование может быть не только по методу секционирования RANGE, но и по LIST. В этом случае командные строки секционирования таблицы примут вид

PARTITION BY LIST (PARTID) ( PARTITION PAGES_1 VALUES (1) , … PARTITION PAGES_12 VALUES (12) )

Приведенный выше вариант был разработан для версии Oracle 10g. С переходом на Oracle 11g другие таблицы, например, таблица SESS_LOCK (будет рассмотрена ниже) была секционирована с использованием виртуального столбца вместо реального столбца PARTID по методу секционирования LIST. Для таблицы PAGES виртуальный столбец имел бы вид:

PARTID NUMBER generated always as ( to_number(to_char(CREATED ,'MM')) ) virtual

где строки generated always as и virtual — типовые команды для виртуального столбца. Строка to_number(to_char(CREATED ,’MM’)) отражает функцию автоматического заполнения виртуального столбца PARTID данными из столбца Created в процессе ввода данных в таблицу.

Преимуществом такого решения является то, что триггер записи данных в столбец PARTID создавать не надо, столбец автоматически будет заполняться числами от 1 до 12 из столбца CREATED.

Команда создания таблицы с виртуальным столбцом выглядит следующим образом:

CREATE TABLE ADM.PAGES (ID NUMBER, PAGE_DATA BLOB, CREATED DATE, PARTID NUMBER generated always as ( to_number(to_char(CREATED ,'MM')) ) virtual ) TABLESPACE ADMDATA ( PARTITION PAGES_1 VALUES LESS THAN (2) , … PARTITION PAGES_12 VALUES LESS THAN (13) )

Замечание. Нужно отметить, что преимуществом циклического секционирования является также наличие фиксированного названия секций, что позволяет использовать это при просмотре и анализа секций. Например, для указанной выше таблицы, ниже приведенный запрос позволяет следить за числом записей в таблице по секциям и процессом очистки секций.

select 'pages_1' part, count(*) num_rows from ADM.PAGES partition(PAGES_1) union all select 'pages _2', count(*) from ADM.PAGES partition(PAGES_2) … union all select 'pages _12', count(*) from ADM.PAGES partition(PAGES_12)

Секционирование по циклу таблицы SESS_LOCK с секциями по дням внутри месяца.

В таблицу SESS_LOCK в течение дня вводиться информация о блокировках между сессиями. В таблице имеется столбец CLOB. Данная информация храниться менее месяца (за 14 дней). Секционирование по циклу было организовано с 31-й секцией (максимальное число дней в месяце). Ежедневно запускает JOB, который вызывает процедуру очистки старой секции, отстоящей на 15 дней от текущей, а также очищает секцию, следующей после текущей, подготавливая ее для ввода данных на следующий день.

Таблица работает в Oracle 11g, поэтому используется виртуальный столбец PARTDD взамен реального столбца (что позволило обойтись без триггера для заполнения столбца данными).

Виртуальный столбец автоматически заполняется значением из столбца Monitortime, содержащего текущую дату ввода строки. Таблица была секционирована по методу секционирования LIST. Команда создание таблицы выглядит следующим образом:

CREATE TABLE ADM.SESS_LOCK (SID NUMBER, SERIAL# NUMBER, WHO_SID NUMBER, WHO_SERIAL# NUMBER, SQL_ID VARCHAR2(13), SQL_FULLTEXT CLOB, … EVENT VARCHAR2(64), MONITORTIME DATE, PARTDD NUMBER generated always as (to_number(to_char(MONITORTIME,'DD')) ) virtual ) TABLESPACE ADMDATA PARTITION BY LIST (PARTDD) ( PARTITION SESS_LOCK_01 VALUES(1) COMPRESS FOR ALL OPERATIONS, PARTITION SESS_LOCK_02 VALUES(2) COMPRESS FOR ALL OPERATIONS, … PARTITION SESS_LOCK_31 VALUES(31) COMPRESS FOR ALL OPERATIONS ) ENABLE ROW MOVEMENT;

где COMPRESS FOR ALL OPERATIONS — команда на сжатие по DML операциям.

Особенности секционирования по циклу.

Чтобы провести секционирование по циклу надо иметь в таблице столбец, который может выступать ключем обычного RANGE секционирования таблицы по дате (типа CREATED, использованного выше) и из которого можно извлечь номер текущего дня или месяца. Это могут быть, например, столбцы, в которых вводиться время создания записи или изменения записи и т.д.
При секционировании по циклу возникает дополнительная задача очистки старых секций, чтобы освободить место в секции для ввода новых данных. Для этого был разработаны JOB Sheduler, запускающие процедуры очистки секций ADM.P_TRUNC_PART_DDMM.
JOB для таблицы PAGES запускается раз в месяц, а для таблицы SESS_LOCK ежедневно. Процедура очистки секций этими JOB является универсальной, работающей как для секционирования по циклу по месяцам в течение года, так и для секционирования по циклу по дням в течение месяца.

Режим процедуры определяется параметром PARAM_DDMM, задаваемым в JOB. В JOB так же через параметр DELTA_TRUNC задается число месяцев (число дней), в пределах которых подлежит сохранить данные в таблице. Вид JOB Sheduler для таблицы SESS_LOCK, запускающего ежедневно процедуру ADM.P_TRUNC_PART_DDMM очистки секций с сохранением данных за 14 дней, имеет вид:

Для секционирования по месяцам JOB-команда примет вид

BEGIN DBMS_SCHEDULER.CREATE_JOB (job_name => 'ADM.JOB_TRUNCATE_SESS_LOCK', job_type => 'PLSQL_BLOCK', repeat_interval => 'FREQ=DAILY; INTERVAL=1; BYHOUR=09; BYMINUTE=20', job_action => ' DECLARE OWNER VARCHAR2(30):=''ADM''; TABLE_NAME VARCHAR2(30):=''PAGES''; DELTA_TRUNC NUMBER:=14; PARAM_DDMM VARCHAR2(2):=''DD''; BEGIN ADM.P_TRUNC_PART_DDMM (OWNER, TABLE_NAME, DELTA_TRUNC, PARAM_DDMM); END; ', start_date=>SYSDATE, enabled => TRUE, comments => 'Truncate table SESS_LOCK' ); END;

Для секционирования по месяцам в JOB команда repeat_interval примет вид:
repeat_interval => ‘FREQ=MONTHLY; INTERVAL=1’,
а PARAM_DDMM примет значение «MM».

Универсальная процедура очистки секций ADM.P_TRUNC_PART_DDMM приводиться ниже.

CREATE OR REPLACE PROCEDURE ADM.P_TRUNC_PART_DDMM (P_OWNER IN VARCHAR2, P_TABLE_NAME IN VARCHAR2, P_DELTA_TRUNC IN NUMBER , P_DDMM IN VARCHAR2 ) IS Part_pos number; Part_pos_curent number; Part_value number; Part_valuev varchar2(30); Part_delta_minus number; Part_delta_plus number; Lastday_minus number:=12; Lastday_plus number:=12; Delta_trunc number:=P_delta_trunc; Sysdat date:=sysdate; --Процедура truncate секции procedure p_alter(p_part_pos in number) as part_name varchar2(100);str varchar2(500); begin Select min(partition_name) into Part_name from ALL_TAB_PARTITIONS where table_owner=P_OWNER and table_name=P_TABLE_NAME and partition_position=p_part_pos; Str:='ALTER TABLE '||P_OWNER||'.'||P_TABLE_NAME||' TRUNCATE PARTITION '|| PART_NAME||' UPDATE GLOBAL INDEXES'; Execute immediate(str); Exception when others then null; end; BEGIN Part_pos_curent:=to_number(to_char(sysdat,''||P_DDMM||'')); if P_DDMM='DD' then Lastday_minus:= to_number (to_char (last_day (trunc(sysdat,'MM')-1),''||P_DDMM||'')); Lastday_plus:= to_number(to_char(last_day(sysdat),''||P_DDMM||'')); end if; if Delta_trunc>Lastday_minus-1 then Delta_trunc:=Lastday_minus-1;end if; --1.Определение секции, очищаемой ранее текущей Part_delta_minus:=Part_pos_curent-Delta_trunc-1; if Part_delta_minus>=1 then Part_pos:=Part_delta_minus; else Part_pos:=Lastday_minus+Part_delta_minus; end if; P_ALTER(PART_POS); --2. Определение секции, очищаемой следующей после текущей if Part_pos_curent

Выводы.

Циклическое секционирование наиболее эффективно для таблиц, у которых имеется ограниченное время жизни данных в секциях, например, в течение нескольких дней внутри месяца, года или несколько месяцев внутри года (при этом в столбцах таблицы могут как быть, так не быть LOB-столбцы).

Преимуществами циклического секционирования являются:

отсутствие необходимости создания новых и уничтожения старых секций;
быстрая очистка секций операцией Truncate, что особенно эффективно для таблиц с BLOB столбцами;
оперативное освобождение табличного пространства;
упрощенный контроль за операциями над секциями в силу фиксирования имен секций.

К дополнительным расходам при секционировании по циклу можно отнести необходимость создания JOB и процедуры очистки секций для освобождения места для ввода новых данных. Поскольку JOB и процедура очистки представлены выше в готовом виде, а время их работы незначительно, то это не является существенным ограничением на использования секционирования по циклу. При этом следует учесть, что очистка секций с целью удалений старых данных (порой с удалением секций) широко используется и в традиционном секционировании, что потребует так же разработки JOB и процедур очистки секций.

Oracle/ClickHouse. DWH. Партицирование как средство быстрого обновления данных

В этой статье хотелось бы рассмотреть такой вопрос - как частичное обновление больших объемов данных в таблицах, которые активно используются пользователями на чтение. Задача является типовой, и с ней сталкивается каждый инженер данных. При этом не важно на какой ступеньке своей карьерной лестницы вы находитесь, Junior или Senior, такие задачи будут.

Что мы имеем:

Огромная витрина с данными - SALES, содержащая множество атрибутов по продажам в абстрактной системе.
Существует ETL-процесс, не важно на какой технологии, который подготавливает SALES_INCREMENT для витрины на основании ряда источников, которые могут поставлять данные по одной продаже в разные периоды времени с задержками до недели-месяца (период инкремента).
Каждая итерация инкремента содержит полные актуальные данные за несколько полных месяцев назад + новые данные. Скажем 500млн+ строк.
Имеются аналитические системы, которые постоянно обращаются к данным SALES для чтения. Их природа также не важна. Это могут быть прямые запросы из отчетов, запросы для промежуточной агрегации и формирования более узких витрин данных. Главное, что нужно понимать - данные постоянно читаются. И даже ночью у вас нет спасения, чтобы спокойно провести пересчет.

Задача: Обновить данные в таблице SALES на основании SALES_INCREMENT таким образом, чтобы вам не пришлось объясняться перед разгневанными пользователями и коллегами за зависания запросов или испорченные данные в смежных витринах.

Попытки решить без партицирования

На ум сразу приходит стандартный алгоритм: Insert для новых строк, update для старых и задача решена.

Но, например в Oracle, таким подходом можем уложить запросы пользователей по snapshot too old в случае, если запросы долгие, а наше обновление переполнит журнал и приведет к его скидыванию в архив. И не важно какими порциями вы фиксируете данные. Про БД, где нет версионности, вообще молчу. Система просто встанет из-за блокировки таблиц на чтение.

Кроме этого - новые строки мы вставим легкими запросами, а вот старые нам придется обновлять через merge или иными методами, нагружая систему еще и выборками данных, помимо их вставки. Во избежание merge и сложной логики можно удалять обновляемые строки и просто заменять их новым. Использовать принятый в DWH delete + insert. Но тут раздуваем undo/redo и рискуем надолго уйти в Downtime если система по какой то причине начнет откатывать транзакцию. Понятное дело, что опытные разработчики модифицируют данную схему, чтобы все работало. Вплоть до отключения журналов. Но это долго, сложно, дорого по ресурсам и иногда эффективность достигается путем отказа от ACID (Привет от хинтов или связки drop table + rename tmp_table to table)

В общем писать потоковую загрузку с insert/merge/update/delete можно. И так даже делают. И это работает. Но наша цель найти более эффективный и современный способ.

И этот способ существует - партиции. Их изобретали не только для эффективной работы с выборками по данным, которые можно разбить на части по ключу, но также для удобного менеджмента по этому ключу - отключение неактуальных данных, переключения огромных массивов данных между таблицами БД. Рассмотрим этот метод подробно.

ClickHouse

Данную базу выбрал как пример, по одной простой причине, переключение партиций в ней максимально простая операция. И работа с партициями поставлена на поток. Вызвано это тем, что колоночно-аналитическая природа СУБД и ее фокус на скорострельности ограничивает возможности по обновлению существующих данных.

Все что нужно сделать для эффективного решения вопросов обновления - прибегнуть к стандартной DDL операции:

alter table sales replace partition (202308) from sales_increment

В данном случае вы замените все данные в SALES за август 2023 года (ну при условии что идет партицирование по месяцам) копией данных из SALES_INCREMENT. В SALES_INCREMENT данные останутся - далее вы можете делать с ними что хотите. Чаще всего таблицу просто удаляют после копирования всех партиций до следующей итерации ETL.

Иногда может возникнуть необходимость дописать данные, не удаляя весь август. Тогда используем move partition:

alter table sales move partition (202308) from sales_increment

В данном случае мы переместим данные из SALES_INCREMENT и допишем их в SALES. В SALES_INCREMENT данные пропадут.

Ну а для автоматизации процесса, можно загнать копирование в цикл по всем доступным партициям инкрементальной таблицы.

select distinct partition from system.parts where table = 'sales_increment'

Вообще в ClickHouse много инструментов для менеджмента данных на уровне партиций. Более подробно про них можно прочитать в документации к БД: ClickHouse. Работа с партициями

Oracle

Я не могу не остановиться на данной СУБД, так как это мой хлеб. И в какой то момент времени мне подкинули идею провернуть трюк с партициями и в нашей БД. Однако все оказалось не так просто как в ClickHouse, но реализовать технологию после пары приседанй удалось.

В чем трудность подобного фокуса в Оракл? Да в том, что данная СУБД поддерживает обмен партициями в двух режимах:

Из непартицированной таблицы в партицированную
Из партицированной таблицы в непартицированную

По своим архитектурным или иным соображениям не реализовали в Oracle решение по обмену между двумя партицированными таблицами.

Но мы же с вами понимаем, что подобное ограничение никак не мешает нам совершить обмен. Просто придется добавить промежуточную непартицированную таблицу и перегонять содержимое через нее. Приступим.

Нам потребуется документация на exchange partition: Oracle. Работа с партициями

Первое, что нужно сделать - это поместить данные из исходной партицированной таблицы в промежуточную непартицированную sales_increment_staging:

alter table sales_increment exchange partition SYS_XXX with table sales_increment_staging without validation

Хочу обратить внимание, что without validation критично ускоряет процесс переключения, а так как мы все таки размышляем с позиции DWH, то считаем, что данные уже миллион раз проверены на целостность, и делать это еще раз - пустая трата ресурсов и времени. Однако, если с этим будут проблемы - всегда можно убрать директиву.

Следующим шагом перемещаем данные из промежуточной таблицы в целевую

alter table sales exchange partition SYS_YYY with table sales_increment_staging without validation

Происходит полная замена данных в целевой таблице. Потому стоит помнить, что если мы применяем подобный подход в Oracle, то партиция инкремента должна содержать полные данные. Формально БД на уровне метаданных превращает таблицу в партицию. А старую партицию удаляет (также на уровне метаданных).

Выглядит это примерно так:

Сегмент данных промежуточной таблицы SALES_INCREMENT_STAGING заменяет сегмент с партицией в таблице SALES
Старый сегмент с партицией SALES остается в БД. Т.е пока живут запросы чтения, начавшиеся до фиксации переключения партиций, данный сегмент остается видимым ими по требованиям уровня изоляции Read Commited

Хочу обратить отдельное внимание на следствие данного алгоритма - версионность данных Oracle работает при использовании данной DDL операции. И все запросы смогут дочитать данные, которые были в таблице SALES до того как их подменили. А новые запросы увидят уже новую партицию. Данный вопрос подробно не рассматривается нигде, но мы вынуждены были провести тесты, чтобы убедиться что система переноса безопасна.

Также из алгоритма вытекает требование - осторожно относиться к создаваемой таблице. Она должна иметь такие же настройки табличного пространства, что и те таблицы, с которыми мы производим обмен. С версии 12.2 появился лайфхак, который позволит нам избавиться от головной боли при создании SATGING таблиц:

create table sales_increment_staging for exchange with table sales_increment

Одной очень полезной функцией оказалась возможность менять партиции по значению. Для этого нужно использовать конструкцию partition for. В данном случае можно будет подменить партицию по значению, а не по имени партиции. Т.е если вы знаете, что в таблице sales_increment_staging находится август 2023 года, то просто передайте значение ключа партицирования в качестве параметра.

alter table sales exchange partition for (date'2023-08-01') with table sales_increment_staging without validation

Еще одним отличием обмена партициями по имени и по значению является тот факт, что обмен по имени требует наличия партиции с этим именем в целевой таблице, а по значению - не требует. Ну при условии того, что включено автосоздание партиций.

Однако все СУБД требуют соблюдения ряда правил, для того, чтобы технология работала:

Одинаковая структура таблиц - для всех СУБД. Это логичное требование
Одинаковые ключи партицирования для случаев с СУБД, где доступен обмен между двумя партицированными таблицами
Одинаковая политика хранения таблиц на диске (для ClickHouse)
И прочее, о чем можно прочитать в документации к конкретной СУБД

Что же мы получили:

Быстрое и простое обновление данных в целевых таблицах, не вынуждающее нас писать сложный код.
Почти все затраты на утилизацию и блокировки ложатся на стандартные механизмы обеспечения согласованности данных СУБД, так как мы работаем со стандартным DDL.
Все UNDO/REDO пишутся для таблицы инкремента, а не для основной таблицы. Таким образом в том же Oracle, мы защищаем себя от ошибки snapshot too old на длительных выборках по SALES.
Мы вообще отвязываемся от логики ETL. Т.е даже если поменяется код, наша логика обновления всегда будет работать. Нужно только актуализировать структуры таблиц.
Нам не нужно будет ограничивать ETL по объему, опасаясь долгого обновления основной таблицы. Операция обмена партициями быстра в любой БД и на самых больших объемах.
Мы можем варьировать периоды инкремента. Грузить месяц несколько раз в день. Полгода по ночам. Год раз в месяц. Все зависит от бизнеса и частоты обновления данных в тот или иной период в прошлом.

партиционирование
oracle
clickhouse
exchange partition

Oracle Partitioning: Оперативное перемещение и восстановление исторических данных

При решении задачи хранения и обеспечения доступа к историческим данным очень часто возникает задача выгрузки архивных данных на резервный носитель (например, на магнитную ленту) с возможностью оперативного восстановления этой информации и обеспечения доступа к ней пользователей. Эта проблема наиболее актуальна для хранилищ данных, хотя может применяться и для обработки архивных данных OLTP-систем.

В данной статье описывается способ решения этой проблемы с помощью опции Partitioning базы данных Oracle Database.

Ниже представлена иллюстрация данного подхода, который включает в себя: идентификацию исторических данных, их перемещение во временную таблицу, экспорт и копирование на резервный носитель.

Иллюстрация подхода перемещения исторических данных

Первым шагом является определение секций, содержащих исторические данные. Исторические данные – это данные за прошлые периоды, над которыми в будущем не будут проводиться операции изменения. Затем секции, содержащие исторические данные, перемещаются в заранее подготовленную временную таблицу. Следующим шагом производится экспорт метаданных для Transport Table Space (TTS). В заключении производится перенос файла с метаданными и файла табличного пространства на резервный носитель.

Далее будет детально рассматриваться процесс экспорта и импорта табличного пространства для одного раздела секционированной таблицы CALLS (информация о телефонных звонках клиентов) схемы DWH.

 SQL> CREATE TABLE DWH.CALLS ( 2 CALLS_ID NUMBER (15) NOT NULL, 3 STRT_DT_KEY DATE NOT NULL, 4 BSN_EV_TP_ID NUMBER (5) NOT NULL, 5 STRT_TM DATE NOT NULL, 6 END_TM DATE NOT NULL, 7 CTY_FR NUMBER (15) NOT NULL, 8 CTY_TO NUMBER (15) NOT NULL, 9 A_NUM VARCHAR2 (20) NOT NULL, 10 B_NUM VARCHAR2 (20) NOT NULL, 11 PRICE_AMT NUMBER (15,4) NOT NULL, 12 CHG_AMT NUMBER (15,4) NOT NULL, 13 CHG_CALL_DUR NUMBER (15) NOT NULL, 14 CALL_DUR NUMBER (15) NOT NULL, 15 IS_DEL_IND NUMBER (1) NOT NULL, 16 UPD_DT DATE NOT NULL, 17 PPN_DT DATE NOT NULL, 18 SRC_STM_ID NUMBER (5) NOT NULL 19 ) 20 TABLESPACE TBS_CALLS 21 PARTITION BY RANGE (STRT_DT_KEY) 22 SUBPARTITION BY LIST (BSN_EV_TP_ID) 23 SUBPARTITION TEMPLATE ( 24 SUBPARTITION "SP_BSNEV1" values ( 1 ), 25 SUBPARTITION "SP_BSNEV2" values ( 2 ), 26 SUBPARTITION "SP_BSNEV3" values ( 3 ), 27 SUBPARTITION "SP_BSNEV4" values ( 4 ), 28 SUBPARTITION "SP_BSNEV5" values ( 5 ), 29 SUBPARTITION "SP_BSNEV6" values ( 6 ), 30 SUBPARTITION "SP_BSNEV7" values ( 7 ), 31 SUBPARTITION "SP_BSNEV8" values ( 8 ), 32 SUBPARTITION "SP_BSNEV9" values ( 9 )) 33 ( 34 PARTITION P_0106 VALUES LESS THAN (TO_DATE('2006-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE TBS_CALLS_0106_1, 35 PARTITION P_0206 VALUES LESS THAN (TO_DATE('2006-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE TBS_CALLS_0206_1, 36 PARTITION P_0306 VALUES LESS THAN (TO_DATE('2006-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN')) TABLESPACE TBS_CALLS_0306_1, 37 PARTITION P_MAXV VALUES LESS THAN (MAXVALUE) TABLESPACE TBS_CALLS_PMAXV 38 ); Table created

Описанный подход был принят как основной для задач перемещение и восстановление исторических данных хранилища корпоративной информации компании “ОАО Ростелеком”.

2. Определение исторических данных

Для выявления исторических данных, то есть тех данных которые не будут больше изменяться, администратор должен ежемесячно проводить мониторинг их появления. Перечень данных, которые следует признавать историческими, определяют бизнес-требования. Часто правило определения исторических данных сводится к такому условию: историческими признаются те данные, срок хранения которых превышает определенный лимит, например, 5 лет от текущего момента.

Для автоматизации выявления исторических данных в конкретной таблице фактов, возможно выполнение следующего запроса (обращение к словарю Oracle Database):

 select COUNT_DAY, TABLE_OWNER, TABLE_NAME, PARTITION_NAME from (select TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'MM.YYYY'), 'MM.YYYY') - TO_DATE(substr(t.partition_name, 3, 2)||'.20'|| substr(t.partition_name,5,2),'MM.YYYY')) AS COUNT_DAY, T.TABLE_OWNER, T.TABLE_NAME, T.PARTITION_NAME from all_tab_partitions t ) where COUNT_DAY > 1825 /* 5 лет в днях */;

Данный запрос вернет перечень разделов (см. поле PARTITION_NAME) по таблицам, данные в которых являются историческими (срок хранения превышает 5 лет). Эти данные необходимо архивировать и перенести на резервный носитель.

3. Перемещение исторических данных

Для перемещения раздела таблицы с историческими данными будет использована технология перемещаемых табличных пространств (Transportable Tablespace). Для перемещения табличных пространств необходимо провести следующие действия:

Создать временную таблицу, в которую будут перемещены исторические данные.
Переместить во временную таблицуисторические данные путем смены разделов (exchange partition).
Убрать все логические и физические связи табличного пространства и раздела таблицы со всеми объектами кроме временной таблицы.
Сделать табличное пространство доступным только для чтения (read only).
Сделать экспорт метаданных табличного пространства раздела с историческими данными (для успешного выполнения экспорта и импорта необходимо, чтобы пользователь, из-под которого выполняются данные операции, обладал правами exp_full_database и imp_full_database соответственно).
Скопировать файл с метаданными и файлы данных табличного пространства с историческими данными в папку для переноса на резервный носитель.
Сделать архив, включив в него: файл с метаданными, файлы табличного пространства, дополнительный файл с описанием.
Удалить табличное пространство с историческими данными из БД.

Ниже приведена последовательность действий по перемещению исторических данных из раздела P_0106 таблицы CALLS.

Данные раздела P_0106 хранятся в табличном пространстве TBS_CALLS_0106_1, которое в свою очередь, состоит из двух файлов: TBS_CALLS_0106_1_001.dbf и TBS_CALLS_0106_1_002.dbf.

Ниже все скрипты будут выполняться из-под пользователя system.

4. Создание временной таблицы

Создадим временную таблицу, в которую в последствии переместим раздел с историческими данными.

 SQL> create table DWH.CALLS$EXP$P_0106 2 TABLESPACE TBS_CALLS_0106_HIST 3 PARTITION BY LIST ("BSN_EV_TP_ID") 4 ( 5 PARTITION "SP_BSNEV1" values ( 1 ) TABLESPACE TBS_CALLS_0106_HIST, 6 PARTITION "SP_BSNEV2" values ( 2 ) TABLESPACE TBS_CALLS_0106_HIST, 7 PARTITION "SP_BSNEV3" values ( 3 ) TABLESPACE TBS_CALLS_0106_HIST, 8 PARTITION "SP_BSNEV4" values ( 4 ) TABLESPACE TBS_CALLS_0106_HIST, 9 PARTITION "SP_BSNEV5" values ( 5 ) TABLESPACE TBS_CALLS_0106_HIST, 10 PARTITION "SP_BSNEV6" values ( 6 ) TABLESPACE TBS_CALLS_0106_HIST, 11 PARTITION "SP_BSNEV7" values ( 7 ) TABLESPACE TBS_CALLS_0106_HIST, 12 PARTITION "SP_BSNEV8" values ( 8 ) TABLESPACE TBS_CALLS_0106_HIST, 13 PARTITION "SP_BSNEV9" values ( 9 ) TABLESPACE TBS_CALLS_0106_HIST 14 ) 15 as select * from DWH.CALLS where 1=2; Table created

5. Перемещение данных во временную таблицу

Выполняем команду смены раздела (exchange paertition) P_0106 (раздел с историческими данными) между таблицей CALLS и временной таблицей CALLS$EXP$P_0106.

 SQL> alter table DWH.CALLS exchange partition P_0106 with table DWH.CALLS$EXP$P_0106 without validation; Table altered

6. Удаление связей

Сделать экспорт метаданных табличного пространства можно только тогда, когда оно не связано с другими объектами базы данных.

Для проверки наличия связей необходимо выполнить следующие процедуру и запрос (их необходимо выполнять из-под пользователя SYS):

 SQL> conn sys/pass@DWH as sysdba Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Connected as SYS SQL> SQL> EXECUTE DBMS_TTS.transport_set_check('TBS_CALLS_0106_1', TRUE); PL/SQL procedure successfully completed SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS -------------------------------------------------------------------------------- Default Partition (Table) Tablespace TBS_CALLS for CALLS not contained in transp Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS$EXP$P_0106 no Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not Default Composite Partition (Table) Tablespace TBS_CALLS_0106_HIST for CALLS not 19 rows selected SQL>

Если запрос к представлению TRANSPORT_SET_VIOLATIONS возвращает записи, то это значит, что взаимосвязи раздела с другими объектами базы данных существуют. Необходимо, чтобы запрос к данному представлению НЕ возвращал строк. Для этого необходимо изменить табличные пространства для раздела P_0106 таблицы CALLS – переместить раздел в табличное пространство TBS_CALLS_0106_HIST и переместить метаданные о таблице CALLS$EXP$P_0106 в табличное пространство TBS_CALLS_0106_1:

 SQL> ALTER TABLE DWH.CALLS MODIFY default attributes FOR PARTITION P_0106 tablespace TBS_CALLS_0106_HIST; Table altered SQL> ALTER TABLE DWH.CALLS$EXP$P_0106 MODIFY default attributes tablespace TBS_CALLS_0106_1; Table altered SQL>

Выполним проверку наличия взаимосвязей повторно.

 SQL> EXECUTE DBMS_TTS.transport_set_check('TBS_CALLS_0106_1', TRUE); PL/SQL procedure successfully completed SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS -------------------------------------------------------------------------------- SQL>

В представлении TRANSPORT_SET_VIOLATIONS записи отсутствуют – взаимосвязей нет.

7. Атрибут "только для чтения"

Сделать экспорт метаданных табличного пространства можно только тогда, когда оно находится в режиме "только для чтения". Сделать табличное пространство доступным только для чтения можно, выполнив следующую команду:

 SQL> ALTER TABLESPACE TBS_CALLS_0106_1 READ ONLY; Tablespace altered SQL>

8. Экспорт табличного пространства

Произведем экспорт метаданных табличного пространства. Для этого будет использована технология DataPump и, соответственно, утилита expdp.

В командной строке необходимы выполнить команду экспорта (см. скрипт – export.sh) в директорию определенною в переменной DATA_PUMP_DIR базы данных.

 $ expdp system/pass@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_TABLESPACES=TBS_CALLS_0106_1 TRANSPORT_FULL_CHECK=Y LOGFILE= TBS_CALLS_0106_1.log; Export: Release 10.2.0.4.0 - 64bit Production on Friday, 17 April, 2009 10:39:42 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 With the Partitioning, Oracle Label Security, OLAP and Data Mining options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02": system/********@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_TABLESPACES=TBS_CALLS_0106_1 TRANSPORT_FULL_CHECK=Y LOGFILE= TBS_CALLS_0106_1.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully loaded/unloaded ************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_02 is: /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_02" successfully completed at 10:40:11 $

Перейдем в директорию, которую определяет переменная DATA_PUMP_DIR.

 $ cd /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/ $

Просмотрим ее содержимое.

 $ ls TBS_CALLS_0106_1.DMP TBS_CALLS_0106_1.log $

9. Копирование файлов

Скопируем файл с метаданными TBS_CALLS_0106_1.DMP и файлы данных БД TBS_CALLS_0106_1_001.dbf, TBS_CALLS_0106_1_002.dbf в директорию /backup/DWH/TBS_CALLS_0106_1_HIST, предназначенную для временного хранения архивов, перед переносом на резервный носитель. Предварительно директорию TBS_CALLS_0106_1_HIST необходимо создать в /backup/DWH/.

 $ cd /backup/DWH/ $ mkdir TBS_CALLS_0106_1_HIST $ ls TBS_CALLS_0106_1_HIST $ $ cp /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP /backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1.DMP $ cp /wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf /backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_001.dbf $ cp /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf /backup/DWH/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_002.dbf $ $ cd TBS_CALLS_0106_1_HIST/ $ ls TBS_CALLS_0106_1.DMP TBS_CALLS_0106_1_001.dbf TBS_CALLS_0106_1_002.dbf $

Рекомендуется создать текстовый файл /backup/DWH/TBS_CALLS_0106_1.txt, в котором описать месторасположение файлов с данными экспортируемого табличного пространства. И затем включить данный текстовый файл в архив.

Для создания файла с описанием можно выполнить следующие действия (в операционной системе Unix):

Создать файл: touch TBS_CALLS_0106_1.txt.
Открыть файл на редактирование: cat > TBS_CALLS_0306_1.txt.
Внести в файл текст.
По окончанию редактирования файла нажать Cntr+D.

 $ touch TBS_CALLS_0106_1.txt $ cat > TTBS_CALLS_0106_1.txt /wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf $

10. Создание архива

Создадим архив с содержимым директории TBS_CDR_0306_1_HIST, используя утилиту tar. Этот архив, впоследствии, и будет перемещен на резервный носитель.

 $ cd backup/DWH/ $ tar -cf - TBS_CALLS_0106_1_HIST | gzip -c > TBS_CALLS_0106_1_HIST.tar.gz $ ls TBS_CALLS_0106_1_HIST TBS_CALLS_0106_1_HIST.tar.gz $

Архив создан. Теперь можно удалить исторические данные из таблицы БД.

11. Удаление табличного пространства

Удалим табличное пространство TBS_CALLS_0106_1

 SQL> drop tablespace TBS_CALLS_0106_1 including contents and datafiles; Tablespace dropped SQL>

Вместе с табличным TBS_CALLS_0106_1 пространством удалится и временная таблица CALLS$EXP$P_0106.

Для облегчения в дальнейшем процесса восстановления в таблице с данными (в нашем примере это таблица CALLS) раздел, в котором были исторические данные, лучше оставить.

12. Восстановление исторических данных

Для восстановления исторических данных из архива необходимо провести следующие действия:

Скопировать архив с историческими данными с резервного носителя в директорию для восстановления.
Распаковать архив.
Скопировать файл с метаданными в папку для восстановления и файлов с данными в папку (или папку) сервера базы данных, где они находились до проведения экспорта.
Импорт исторических данных во временную таблицу.
Смена табличных пространств.

13. Копирование и распаковка архива

Скопируем архив с историческими данными с резервного носителя в директорию для восстановления. В нашем примере это будет директория /backup/Restore. Обычно эту функцию выполняет администратор системы резервного копирования.

Подключимся к серверу, на котором работает наша СУБД, под пользователем операционной системы oracle, используя командную строку.

 login as: oracle Using keyboard-interactive authentication. Password:

Извлечём файлы из архива.

 $ cd /backup/Restore/ $ gunzip -c TBS_CALLS_0106_1_HIST.tar.gz | tar -xf - $

14. Копирование файлов

Скопирем файл с метаданными TBS_CALLS_0106_1.DMP в директорию /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/,

файл данных TBS_CALLS_0106_1_001.dbf в директорию /wh/oracle/disk1/DWH/;

файл данных TBS_CALLS_0106_1_002.dbf в директорию /wh/oracle/disk0/DWH/.

 $ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1.DMP /u01/app/oracle/product/10.2.0/db_1/admin/DWH/dpdump/TBS_CALLS_0106_1.DMP $ $ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_001.dbf /wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf $ $ cp /bkup/Restore/TBS_CALLS_0106_1_HIST/TBS_CALLS_0106_1_002.dbf /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf $

15. Импорт исторических данных

Выполним команду экспорта метаданных табличного пространства (см. скрипт – import.sh) в директорию, определенную в переменной DATA_PUMP_DIR базы данных.

 $ impdp system/pass@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_DATAFILES=/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf, /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf; Import: Release 10.2.0.4.0 - 64bit Production on Friday, 17 April, 2009 11:08:44 Copyright (c) 2003, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 With the Partitioning, Oracle Label Security, OLAP and Data Mining options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/********@DWH DIRECTORY=DATA_PUMP_DIR DUMPFILE=TBS_CALLS_0106_1.DMP TRANSPORT_DATAFILES=/wh/oracle/disk1/DWH/TBS_CALLS_0106_1_001.dbf, /wh/oracle/disk0/DWH/TBS_CALLS_0106_1_002.dbf Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 11:08:51 $

После окончания импорта метаданных табличного пространства в схеме DWH появится таблица CALLS$EXP$P_0106.

16. Смена табличных пространств

Осуществим смену (partitio6 exchange) между таблицей CALLS$EXP$P_0106 и таблице CALLS.

 SQL> alter table DWH.CALLS exchange partition P_0106 with table DWH.CALLS$EXP$P_0106 without validation; Table altered SQL> В случае, если это необходимо, можно изменить атрибут «только для чтения». SQL> ALTER TABLESPACE TBS_CALLS_0106_1 READ WRITE; Tablespace altered SQL>

17. Заключение

База данных Oracle Database предоставляет гибкий механизм управления табличными пространствами секционированных таблиц, что позволяет достаточно просто организовать управление архивными данными, как в OLTP-системах, так и в хранилищах данных.

Полный архив скриптов можно загрузить по данной ссылке.

18. Дополнительная информация

Oracle Database Utilities 10g Release 2 (10.2) Part Number B14215-01 (раздел посвященный DataPump).
Doc ID: 09585.1 от 04.09.2002 на Oracle Metalink.
Doc ID: 114915.1 от 30.03.2008 на Oracle Metalink.

Oracle Magazine Online - Русское издание. Ковтун М.В. 2009