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 guide does not give any examples, either. 8-}
如何在不知道子分区的(系统生成的)名称的情况下，在特定的月份删除特定的子分区?有一个语法“alter table…”drop subpartition for (subpartition_key_value，…)“但是我没有看到一个方法来指定要删除子分区的月份。分区管理指南也没有给出任何示例。8 - }
3 个解决方案
#1
3
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_SUBPARTITIONS view because the HIGH_VALUE column may not be simple to query. In that case you could use DBMS_ROWID to find the subpartition object_id of a given row:
如果您想动态地删除一个分区，那么使用all_tab_subpartition视图查找分区可能比较困难，因为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
#2
3
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. :-)
事实证明，“子分区”语法确实有效，尽管这似乎是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。但是，如果没有这样的子分区，它会抛出一个错误。
#3
1
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_id returned by dbms_rowid.rowid_info is not the object_id of the all_objects table. 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_id to find out the name of the partition:
dbms_rowid返回的object_id。rowid_info不是all_objects表的object_id。它实际上是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
包含对象的段的对象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_rowidget . rowid_object (row_id)来派生dbms_rowidget .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:
这个示例返回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); ...声明object_no整数;row_id ROWID;…开始从emp中选择ROWID到row_id，其中empno = 7499;object_no:= DBMS_ROWID.ROWID_OBJECT(row_id);DBMS_OUTPUT。PUT_LINE(obj。#“| | object_no);…
#1
3
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_SUBPARTITIONS view because the HIGH_VALUE column may not be simple to query. In that case you could use DBMS_ROWID to find the subpartition object_id of a given row:
如果您想动态地删除一个分区，那么使用all_tab_subpartition视图查找分区可能比较困难，因为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
#2
3
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. :-)
事实证明，“子分区”语法确实有效，尽管这似乎是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。但是，如果没有这样的子分区，它会抛出一个错误。
#3
1
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_id returned by dbms_rowid.rowid_info is not the object_id of the all_objects table. 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_id to find out the name of the partition:
dbms_rowid返回的object_id。rowid_info不是all_objects表的object_id。它实际上是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
包含对象的段的对象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_rowidget . rowid_object (row_id)来派生dbms_rowidget .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:
这个示例返回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); ...声明object_no整数;row_id ROWID;…开始从emp中选择ROWID到row_id，其中empno = 7499;object_no:= DBMS_ROWID.ROWID_OBJECT(row_id);DBMS_OUTPUT。PUT_LINE(obj。#“| | object_no);…