Oracle：如何删除特定分区的子分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12364808/
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
Oracle: how to drop a subpartition of a specific partition
提问by Hans-Peter St?rr
I am using an oracle 11 table with interval partitioning and list subpartitioning like this (simplified):
我正在使用带有间隔分区和列表子分区的 oracle 11 表，如下所示（简化）：
CREATE TABLE LOG
(
ID NUMBER(15, 0) NOT NULL PRIMARY KEY
, MSG_TIME DATE NOT NULL
, MSG_NR VARCHAR2(16 BYTE)
) PARTITION BY RANGE (MSG_TIME) INTERVAL (NUMTOYMINTERVAL (1,'MONTH'))
SUBPARTITION BY LIST (MSG_NR)
SUBPARTITION TEMPLATE (
SUBPARTITION login VALUES ('FOO')
, SUBPARTITION others VALUES (DEFAULT)
)
(PARTITION oldvalues VALUES LESS THAN (TO_DATE('01-01-2010','DD-MM-YYYY')));
How do I drop a specific subpartitition for a specific month without knowing the (system generated) name of the subpartition? There is a syntax "alter table ... drop subpartition for (subpartition_key_value , ...)"but I don't see a way to specify the month for which I am deleting the subpartition. The partition administration guidedoes not give any examples, either. 8-}
如何在不知道子分区的（系统生成的）名称的情况下删除特定月份的特定子分区？有一个语法“alter table ... drop subpartition for (subpartition_key_value, ...)”，但我没有看到一种方法来指定我删除子分区的月份。该分区管理指南没有给出任何的例子，无论是。8-}
采纳答案by Hans-Peter St?rr
As it turns out, the "subpartition for" syntax does indeed work, though that seems to be a secret Oracle does not want to tell you about. :-)
事实证明，“subpartition for”语法确实有效，尽管这似乎是 Oracle 不想告诉您的秘密。:-)
ALTER TABLE TB_LOG_MESSAGE DROP SUBPARTITION FOR
(TO_DATE('01.02.2010','DD.MM.YYYY'), 'FOO')
This deletes the subpartition that would contain MSG_TIME 2010/02/01 and MSG_NR FOO. (It is not necessary that there is an actual row with this exact MSG_TIME and MSG_NR. It throws an error if there is no such subpartition, though.)
这将删除包含 MSG_TIME 2010/02/01 和 MSG_NR FOO 的子分区。（没有必要有一个包含这个确切 MSG_TIME 和 MSG_NR 的实际行。但是，如果没有这样的子分区，它会抛出一个错误。）
回答by Vincent Malgrat
You can use the metadata tables to get the specific subpartition name:
您可以使用元数据表来获取特定的子分区名称：
SQL> insert into log values (1, sysdate, 'FOO');
1 row(s) inserted.
SQL> SELECT p.partition_name, s.subpartition_name, p.high_value, s.high_value
2 FROM user_tab_partitions p
3 JOIN
4 user_tab_subpartitions s
5 ON s.table_name = p.table_name
6 AND s.partition_name = p.partition_name
7 AND p.table_name = 'LOG';
PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE HIGH_VALUE
--------------- ------------------ ------------ ----------
OLDVALUES OLDVALUES_OTHERS 2010-01-01 DEFAULT
OLDVALUES OLDVALUES_LOGIN 2010-01-01 'FOO'
SYS_P469754 SYS_SUBP469753 2012-10-01 DEFAULT
SYS_P469754 SYS_SUBP469752 2012-10-01 'FOO'
SQL> alter table log drop subpartition SYS_SUBP469752;
Table altered.
If you want to drop a partition dynamically, it can be tricky to find it with the ALL_TAB_SUBPARTITIONSview because the HIGH_VALUEcolumn may not be simple to query. In that case you could use DBMS_ROWIDto find the subpartition object_idof a given row:
如果你想动态删除一个分区，用ALL_TAB_SUBPARTITIONS视图找到它可能会很棘手，因为该HIGH_VALUE列可能不容易查询。在这种情况下，您可以使用DBMS_ROWID来查找object_id给定行的子分区：
SQL> insert into log values (4, sysdate, 'FOO');
1 row(s) inserted.
SQL> DECLARE
2 l_rowid_in ROWID;
3 l_rowid_type NUMBER;
4 l_object_number NUMBER;
5 l_relative_fno NUMBER;
6 l_block_number NUMBER;
7 l_row_number NUMBER;
8 BEGIN
9 SELECT rowid INTO l_rowid_in FROM log WHERE id = 4;
10 dbms_rowid.rowid_info(rowid_in =>l_rowid_in ,
11 rowid_type =>l_rowid_type ,
12 object_number =>l_object_number,
13 relative_fno =>l_relative_fno ,
14 block_number =>l_block_number ,
15 row_number =>l_row_number );
16 dbms_output.put_line('object_number ='||l_object_number);
17 END;
18 /
object_number =15838049
SQL> select object_name, subobject_name, object_type
2 from all_objects where object_id = '15838049';
OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
--------------- --------------- ------------------
LOG SYS_SUBP469757 TABLE SUBPARTITION
回答by user2487458
Thanks for the post - it was very useful for me.
感谢您的帖子 - 它对我非常有用。
One observation though on the above script to identify the partition and delete it:
尽管对上述脚本进行了一项观察以识别分区并将其删除：
The object_idreturned by dbms_rowid.rowid_infois not the object_idof the all_objectstable. It is actually the data_object_id. It is observed that usually these ids match. However, after truncating the partitioned table several times, these ids diverged in my database. Hence it might be reasonable to instead use the data_object_idto find out the name of the partition:
在object_id返回的dbms_rowid.rowid_info不是object_id对的all_objects表。它实际上是data_object_id. 据观察，这些 id 通常是匹配的。但是，在多次截断分区表后，这些 ID 在我的数据库中出现了分歧。因此，使用data_object_id来找出分区的名称可能是合理的：
select object_name, subobject_name, object_type
from all_objects where data_object_id = '15838049';
From the table description of ALL_OBJECTS:
OBJECT_ID Object number of the object DATA_OBJECT_ID Object number of the segment which contains the object
OBJECT_ID 对象的对象编号 DATA_OBJECT_ID 包含对象的段的对象编号
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_rowid.htm
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_rowid.htm
In the sample code provided in the above link, DBMS_ROWID.ROWID_OBJECT(row_id)is used instead to derive the same information that is given by dbms_rowid.rowid_info. However, the documentation around this sample mentions that it is a data object number from the ROWID.
在上述链接中提供的示例代码中，DBMS_ROWID.ROWID_OBJECT(row_id)用于派生由 提供的相同信息dbms_rowid.rowid_info。但是，围绕此示例的文档提到它是来自 ROWID 的数据对象编号。
Examples
This example returns the ROWID for a row in the EMP table, extracts the data object number from the ROWID, using the ROWID_OBJECT function in the DBMS_ROWID package, then displays the object number:
DECLARE object_no INTEGER; row_id ROWID; ... BEGIN
SELECT ROWID INTO row_id FROM emp WHERE empno = 7499; object_no := DBMS_ROWID.ROWID_OBJECT(row_id); DBMS_OUTPUT.PUT_LINE('The obj. # is '|| object_no); ...
例子
本示例返回 EMP 表中一行的 ROWID，从 ROWID 中提取数据对象编号，使用 DBMS_ROWID 包中的 ROWID_OBJECT 函数，然后显示对象编号：
DECLARE object_no INTEGER; row_id ROWID; ... BEGIN
SELECT ROWID INTO row_id FROM emp WHERE empno = 7499; object_no := DBMS_ROWID.ROWID_OBJECT(row_id); DBMS_OUTPUT.PUT_LINE('The obj.# is '|| object_no); ...
