This is a two part question:
1) Is it possible to retrieve the name of the partition that data lives in using a select statement, based on its ROWID
or some other identifier?
eg.
SELECT DATA_ID, CATEGORY, VALUE, **PARTITION_NAME**
FROM MYTABLE
WHERE CATEGORY = 'ABC'
2) Is it possible to truncate a single partition of a table, without deleting the data stored in the other partitions?
I have a table with over a billion rows, hash partitioned by category. Only a handful of the categories have problems with their data, so it does not make sense to recreate the entire table, but deleting data from the table, even with all constraints inactive, is taking far too long.
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_NAME
6 AAALrYAAEAAAATRAAA 47832 PT1
11 AAALrYAAEAAAATRAAB 47832 PT1
13 AAALrYAAEAAAATRAAC 47832 PT1
9 AAALrZAAEAAAATZAAA 47833 PT2
10 AAALrZAAEAAAATZAAB 47833 PT2
12 AAALrZAAEAAAATZAAC 47833 PT2
17 AAALrZAAEAAAATZAAD 47833 PT2
19 AAALrZAAEAAAATZAAE 47833 PT2
2 AAALraAAEAAAAThAAA 47834 PT3
5 AAALraAAEAAAAThAAB 47834 PT3
18 AAALraAAEAAAAThAAD 47834 PT3
8 AAALraAAEAAAAThAAC 47834 PT3
1 AAALrbAAEAAAATpAAA 47835 PT4
3 AAALrbAAEAAAATpAAB 47835 PT4
4 AAALrbAAEAAAATpAAC 47835 PT4
7 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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With