database 如何找到记录oracle的分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13659957/
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
how to find partition of record oracle
提问by Hyman
I have a table and there is a partition on it.
我有一张桌子，上面有一个分区。
There are 16 hash partition which is starting from SUBSCRIBER_01 .. etc
有 16 个哈希分区，从 SUBSCRIBER_01 .. 等开始
Table name: SUBSCRIBER
表名：订阅者
Partition Column: CUSTOMER_ID (VARCHAR2 10)
分区列：CUSTOMER_ID (VARCHAR2 10)
Database : 11g
数据库：11g
How can I find partition of a record?
如何找到记录的分区？
Like Customer_ID=933587
喜欢 Customer_ID=933587
回答by David Aldridge
Select the rowid for the row, and the DBMS_RowID.RowID_Object()procedure will extract the data object id.
选择行的rowid，DBMS_RowID.RowID_Object()程序将提取数据对象id。
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_rowid.htm#i997153
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_rowid.htm#i997153
Look up that data object id in the data dictionary and read the subobject_name.
在数据字典中查找该数据对象 id 并读取 subobject_name。
For example:
例如：
SELECT dbms_rowid.rowid_object(ROWID) data_object_id
FROM INVOICE
WHERE INVOICE_ID = 2268041139:
-- data_object_id = 546512
select * from user_objects where data_object_id = 546512;
-- SUBOBJECT_NAME = 'PART_P2099_P00'
-- OBJECT_TYPE = TABLE PARTITION
-- OBJECT_ID = 464826
-- DATA_OBJECT_ID = 546512
回答by KevinBui
You can using this script:
您可以使用此脚本：
SELECT L.*, O.SUBOBJECT_NAME
FROM
(
SELECT DBMS_ROWID.ROWID_OBJECT(ROWID) DATA_OBJECT_ID, L.*
FROM YOUR_TABLE L
--WHERE L.ID = 123
) L
JOIN
(
SELECT SUBOBJECT_NAME, DATA_OBJECT_ID
FROM USER_OBJECTS
) O ON O.DATA_OBJECT_ID = L.DATA_OBJECT_ID
;
回答by Art
I think you can only find the partition name where cust_id = 123... allocated:
我认为您只能找到 cust_id = 123... 分配的分区名称：
SELECT partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = your_table;
回答by Rakesh Chouhan
try this,
尝试这个，
select distinct SUBOBJECT_NAME from user_objects where data_object_id = (SELECT distinct dbms_rowid.rowid_object(ROWID) data_object_id FROM <your table name> WHERE <your partition key> = <partition key value>and rownum=1)
I am using hash partitioning here.
我在这里使用哈希分区。
回答by Hyman
I found the answer.
我找到了答案。
DECLARE
pi_partition varchar2(50);
v_sql varchar2(250);
i number;
sy number;
BEGIN
FOR i IN 1..16
LOOP
IF i < 10 THEN
pi_partition:= 'SUBSCRIBER_0'||i;
ELSE
pi_partition:= 'SUBSCRIBER_'||i;
END IF;
sy:=0;
v_sql:= 'select count(*) into :say from subscriber partition('||pi_partition||') where customer_ID='||''''||'933564'||'''';
--dbms_output.put_line(pi_partition);
EXECUTE IMMEDIATE v_sql INTO sa;
IF sy > 0 THEN
DBMS_OUTPUT.PUT_LINE('Result: ' || sy||pi_partition);
EXIT;
END IF;
END LOOP;
END;
