CODEWITHSUNDEEP
oracle-databaseoracle11gpartitioningdatabase-partitioning
Dr. Hans-Peter Störr
Dr. Hans-Peter Störr

Reputation: 25976

Oracle: how to drop a subpartition of a specific partition

I am using an oracle 11 table with interval partitioning and list subpartitioning like this (simplified):

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-}

Upvotes: 7

Views: 32129

Answers (3)

user2487458
user2487458

Reputation: 11

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:

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

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.

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); ...

Upvotes: 1

Dr. Hans-Peter Störr
Dr. Hans-Peter Störr

Reputation: 25976

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. :-)

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.)

Upvotes: 3

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

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:

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

Upvotes: 3

Related Questions