oracle 在oracle 11G中自动删除最旧的分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5536830/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Drop oldest partition automatically in oracle 11G
提问by Vivek
I have a requirement to drop partition from an interval partitioned table, if the partition is older than three months.
如果分区早于三个月，我需要从间隔分区表中删除分区。
Is there a oracle utility/function to do this? Or if not, how to implement this? Please guide me.
是否有一个 oracle 实用程序/函数来执行此操作？或者如果没有，如何实现？请指导我。
Database version: Oracle 11G
采纳答案by Bob Jarvis - Reinstate Monica
I don't know of any oracle utility or function to do this. You can find the information you need to write your own program to do this in the DBA_TAB_PARTITIONS or ALL_TAB_PARTITIONS views, similar to the following:
我不知道有任何 oracle 实用程序或函数可以执行此操作。您可以在 DBA_TAB_PARTITIONS 或 ALL_TAB_PARTITIONS 视图中找到编写自己的程序所需的信息，类似于以下内容：
SELECT TABLE_OWNER, TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM SYS.DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = strSchema AND
TABLE_NAME = strTable
where strSchema and strTable are the schema and table you're interested in. HIGH_VALUE is a LONG field which contains the code for a call to the TO_DATE function (assuming your table is partitioned on a date field); you'll need to assign HIGH_VALUE to a LONG field, then assign the LONG to a VARCHAR2 in order to get the value somewhere it can be manipulated, in a manner similar to:
其中 strSchema 和 strTable 是您感兴趣的架构和表。 HIGH_VALUE 是一个 LONG 字段，其中包含调用 TO_DATE 函数的代码（假设您的表在日期字段上分区）；您需要将 HIGH_VALUE 分配给 LONG 字段，然后将 LONG 分配给 VARCHAR2 以便在可以操作的地方获取值，其方式类似于：
lHigh_value LONG;
strDate_clause VARCHAR2(100);
lHigh_value := aRow.HIGH_VALUE;
strDate_clause := lHigh_value;
Then you just need to extract the appropriate fields from the DATE clause in order to determine which partitions you need to drop.
然后您只需要从 DATE 子句中提取适当的字段，以确定您需要删除哪些分区。
Share and enjoy.
分享和享受。
回答by Adam Musch
This is wonky and inelegant, but it does work for casting the VALUES LESS THAN some_dateexpression in DBA_TAB_PARTITIONS, at least for range partitioning, including interval partitioning, in 10g and 11g. Inspired by Tom Kyte's "Evaluate Expression"question. Your mileage may vary.
这既VALUES LESS THAN some_date奇怪又不优雅，但它确实适用于在 DBA_TAB_PARTITIONS 中转换表达式，至少对于 10g 和 11g 中的范围分区，包括间隔分区。受到Tom Kyte 的“评估表达”问题的启发。你的旅费可能会改变。
declare
l_hival varchar2(4000);
l_sql varchar2(4000);
l_high_date date;
l_cursor integer default dbms_sql.open_cursor;
l_rows_back number;
begin
-- partition position = 1 always grabs the "oldest" partition
select high_value
into l_hival
from dba_tab_partitions
where table_name = <my_range_partitioned_table>
and partition_position = 1;
dbms_sql.parse (l_cursor,
'begin :retval := ' || l_hival || '; end;',
dbms_sql.native);
dbms_sql.bind_variable (l_cursor, ':retval', l_high_date);
l_rows_back := dbms_sql.execute (l_cursor);
dbms_sql.variable_value (l_cursor, ':retval', l_high_date);
dbms_output.put_line (to_char(l_high_date, 'yyyy-mm-dd-hh24.mi.ss'));
end;
/
As it's PL/SQL, it could be encapsulated into a function to return the "high value" for any partitioned table passed in as arguments.
由于它是 PL/SQL，因此可以将其封装到一个函数中，以返回作为参数传入的任何分区表的“高值”。
回答by Ronnis
I too are interested if there is an automatic solution in 11g.
In older versions, I use one of two approaches:
如果 11g 中有自动解决方案，我也很感兴趣。
在旧版本中，我使用以下两种方法之一：
Sticking to a strict name standard for partition names, for example
SALE201101,SALE201102(for january and february 2011), enables you to extract relevant data fromALL_TAB_PARTITIONSand then you can drop whatever partition is the oldest.Suck it up and use a tiny metadata table with one column for partition_name and one correctly typed column for the time period (whether it's date, week, month, bimonthly, yearly, retail weeks). Then I select the oldest time period and drop the associated partition.
坚持严格的分区名称标准，例如
SALE201101，SALE201102（2011 年 1 月和 2 月），您可以从中提取相关数据ALL_TAB_PARTITIONS，然后您可以删除任何最旧的分区。总结一下，使用一个很小的元数据表，其中一列用于 partition_name，一列用于时间段（无论是日期、周、月、双月、年、零售周）。然后我选择最旧的时间段并删除关联的分区。
This isn't "full automatisch", but it makes automation easier.
这不是“全自动”，但它使自动化更容易。
