1,分区自动维护
CREATE OR REPLACE PACKAGE partition_managment IS
/*======================================================*/
/* 增加分区 */
/*======================================================*/
PROCEDURE add_partition(p_tab_name IN VARCHAR2);
/*======================================================*/
/* 删除分区 */
/*======================================================*/
PROCEDURE drop_partition(p_tab_name IN VARCHAR2);
END partition_managment;
/
CREATE OR REPLACE PACKAGE BODY partition_managment IS
PROCEDURE log(p_comm IN VARCHAR2, p_type IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
/*INSERT INTO CLEAN_OUT_LOG
(WHEN, COMM, TYPE)
VALUES
(SYSDATE, P_COMM, P_TYPE);
COMMIT;
*/
dbms_output.put_line('info:' || p_comm || ' type:' || p_type);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
dbms_output.put_line('ERROR IN partition_management.LOG');
END log;
/*======================================================*/
/* 增加分区,增加新的分区 */
/*======================================================*/
PROCEDURE add_partition(p_tab_name IN VARCHAR2) IS
l_ddl VARCHAR2(200);
l_partition_name VARCHAR2(200);
l_split_date VARCHAR2(8);
l_partition_num INT;
BEGIN
--get partition name
SELECT to_char(add_months(SYSDATE, 2), 'YYYYMM') || '01',
'P' || to_char(add_months(SYSDATE, 1), 'YYYYMM')
INTO l_split_date, l_partition_name
FROM dual;
--does it exist?
SELECT COUNT(*)
INTO l_partition_num
FROM user_tab_partitions
WHERE table_name = p_tab_name
AND partition_name = l_partition_name;
IF l_partition_num > 0 THEN
log('partition ' || l_partition_name || ' 已经存在,skip', 'I');
RETURN;
END IF;
--create
BEGIN
l_ddl := 'alter table ' || p_tab_name ||
' split partition pmax at (to_date(''' || l_split_date ||
''',''YYYYMMDD'')) into (' || ' partition ' ||
l_partition_name || ', partition pmax)';
log('add new partition,ddl:' || l_ddl, 'I');
EXECUTE IMMEDIATE l_ddl;
log('add new partition ok,ddl:' || l_ddl, 'I');
EXCEPTION
WHEN OTHERS THEN
log('Error when create a new partition:' ||
substr(dbms_utility.format_error_stack, 1, 900),
'E');
END;
END add_partition;
/*======================================================*/
/* 删除分区,每调用一次,删除最早的一个超过13个月的分区 */
/*======================================================*/
PROCEDURE drop_partition(p_tab_name IN VARCHAR2) IS
l_ddl VARCHAR2(200);
l_partition_name VARCHAR2(200);
BEGIN
--find the oldest partition
BEGIN
SELECT partition_name
INTO l_partition_name
FROM (SELECT *
FROM user_tab_partitions
WHERE table_name = p_tab_name
ORDER BY partition_name)
WHERE rownum < 2;
EXCEPTION
WHEN OTHERS THEN
log('No partition found for table:' || p_tab_name, 'W');
END;
--is older than 13 month?
BEGIN
IF (add_months(SYSDATE, -13) >
to_date(substr(l_partition_name, 2) || '01', 'YYYYMMDD')) THEN
l_ddl := 'alter table ' || p_tab_name || ' drop partition ' ||
l_partition_name;
log('drop partition,ddl:' || l_ddl, 'I');
EXECUTE IMMEDIATE l_ddl;
log('drop partition ok:' || l_ddl, 'I');
ELSE
log('最早的分区不需要删除:' || l_partition_name, 'I');
END IF;
EXCEPTION
WHEN OTHERS THEN
log('Error when drop partition:' ||
substr(dbms_utility.format_error_stack, 1, 900),
'E');
END;
END drop_partition;
END partition_managment;
/
2,历史分区归档:
--create directory:
create directory ext_local as '/local/TEST';
create directory ext_store as '/store/TEXT';
--grant privilege:
grant read,write on ext_local to test;
grant read,write on ext_store to test;
--create external table:
create table ext_200201
organization external(type oracle_dump
default directory ext_local location('ext_200201.dmp'))
as
select /*+ full(orderitem) */ * from orderitem
where shipdate >= to_date('20020101','YYYYMMDD')
and shipdate < to_date('20020201','YYYYMMDD');
--drop table
drop table ext_200201;
--cp
cp -p ext_200201.dmp /store/TEST/
--drop partition
alter table orderitem drop partition pt200201;
--restore
create table ext_200201_c(
...)
organization external(type oracle_dump
default directory ext_store location('ext_200201.dmp'));
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-696995/,如需转载,请注明出处,否则将追究法律责任。