Partition name from table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Partition name from table

  1. #1
    Join Date
    Jul 2000
    Posts
    31

    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.

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,967
    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';

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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)
    /
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jul 2000
    Posts
    31
    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.

  5. #5
    Join Date
    Jul 2000
    Posts
    31
    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.

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    ah hah. okey-dokey then.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width