-
Partition name from table
Hi,
How do find out the partition name where the data from the table is stored.
For example:
select partition_name, column_a from table;
(partition_name is not one of my columns in this table)
Thanks,
Una.
-
I'm not sure what you want.
Does this help?
Code:
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = 'TABLE';
or
Code:
SELECT partition_name
FROM all_tab_partitions
WHERE owner = 'OWNER' AND
table_name = 'TABLE';
-
You can get the partition name from the subobject_name column of the all/user/dba_objects view. The object id for the partition is retrieved by the supplied procedure DBMS_MVIEW.PMARKER().
For example ...
Code:
Select
ao.subobject_name,
mt.my_column
from
all_objects ao,
my_table mt
where
ao.object_id = DBMS_MVIEW.PMARKER(mt.rowid)
/
-
Hi slimdave,
This is exactly what I'm looking for - but for some reason, the object_id returned from the dbms_mview.pmarker(rowid) doesn't exist in the user_objects table.
Any ideas?
Thanks,
Una.
-
I figured it out....
the DBMS_MVIEW.PMARKER(rowid) returns the data_object_id from the user_objects table, not the object_id.
Thanks for all your help.
- Una.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|