Oracle SQL: Selecting data and partition name from table and truncating partitions

Thanks to your hint about the rowid, I found a solution. If you have the rowid, it should be possible to determine the object the row belongs to.

A minimal example with 4 hash partitions:

CREATE TABLE pt (i NUMBER) PARTITION BY HASH (i) (PARTITION pt1, PARTITION pt2, PARTITION pt3, PARTITION pt4);INSERT INTO pt SELECT ROWNUM FROM all_objects WHERE ROWNUM < 20;

Now, each row has a ROWID. You can find out the object number via DBMS_ROWID.ROWID_OBJECT. The dictionary table USER_OBJECTS has then the object_name (= the name of the table) and the subobject_name (= the name of the partition):

SELECT i,        ROWID AS row_id,        dbms_rowid.rowid_object(ROWID) AS object_no,       (SELECT subobject_name           FROM user_objects          WHERE object_id = dbms_rowid.rowid_object(pt.ROWID)) AS partition_name  FROM pt ORDER BY 3;I   ROW_ID              OBJECT_NO PARTITION_NAME6   AAALrYAAEAAAATRAAA  47832   PT111  AAALrYAAEAAAATRAAB  47832   PT113  AAALrYAAEAAAATRAAC  47832   PT19   AAALrZAAEAAAATZAAA  47833   PT210  AAALrZAAEAAAATZAAB  47833   PT212  AAALrZAAEAAAATZAAC  47833   PT217  AAALrZAAEAAAATZAAD  47833   PT219  AAALrZAAEAAAATZAAE  47833   PT22   AAALraAAEAAAAThAAA  47834   PT35   AAALraAAEAAAAThAAB  47834   PT318  AAALraAAEAAAAThAAD  47834   PT38   AAALraAAEAAAAThAAC  47834   PT31   AAALrbAAEAAAATpAAA  47835   PT43   AAALrbAAEAAAATpAAB  47835   PT44   AAALrbAAEAAAATpAAC  47835   PT47   AAALrbAAEAAAATpAAD  47835   PT4

1) no. you cannot do that, you will have to query all_tab_partitions to find out the partition for a ceratain value.

2) alter table x truncate partition y

Instead of finding the partition name, use the value in the PARTITION FOR syntax:

ALTER TABLE MYTABLE TRUNCATE PARTITION FOR ('ABC');

Although this operation will not affect the data in other partitions it may make your indexes UNUSABLE. Either rebuild the relevant indexes or use UPDATE INDEXES in the DDL.