extract specific partition name from table

Hi,
Another way is to get the partition name for a value. Here are some solutions:
http://oraclesponge.blogspot.com/2006/05/getting-subpartition-name-for-row.html
Regards,
Franck.

to find the partition that has data of a certain value you will need to look > your date
and then pick the lowest one

something like this...

SELECT *
FROM (SELECT p.*, get_partition_date(USER, table_name, partition_name) part_date
      FROM user_tab_partitions p
      WHERE table_name = 'TEST'
      AND   get_partition_date(USER, table_name, partition_name) >
                TO_DATE('2000-11-03', 'yyyy-mm-dd')
      ORDER BY part_date)
WHERE ROWNUM = 1

Hi Stuber,
below is the issue , am getting while trying to retreive partition name. I want to retreive partition name base on actual date and not max value as in Rane partition, i can see the High value set does not represent the actual date it contains data.
If i am querying for 11/02/2009 , i want to retreive partition name emp_20091103 which actually contains that dta. Please advise-
Can anyone advise me on following scenario-
I have a table which is partition by range on Create_Date Column.
I add partition in following way-
ALTER TABLE Emp ADD PARTITION Emp_20091102 VALUES LESS THAN ( to_date('11/03/2009'',''MM/DD/YYYY'' ))';-- This will store values less than 11/03/2009. and similarly, I have many similar partition base on Create date.

I created procedure to retreive partition name base on high value.
As high_value column in user_tab_partitions have '11/03/2009' as high value for partition Emp_20091102,though it contain data less than 11/03/2009. but i want to retreive partition name base on "less than max value criteria"
When i try to execute following query,
SELECT partition_name
FROM user_tab_partitions
WHERE table_name = Emp
AND   get_partition_date(USER, table_name, partition_name)=TO_DATE('11/03/2009','MM/DD/YYYY')- I get output as partition name 20091103 which actually does not contain data for 11/03/2009 but contains data for 11/02/2009. I want to receive partition name base on the actual date data it contains which should be Emp_20091102 partition in this case.
Below is the procedure, i have used  to retreive partition name-
CREATE OR REPLACE FUNCTION Hina_get_partition_date(p_owner IN VARCHAR2,
                                              p_table IN VARCHAR2,
                                              p_partition IN VARCHAR2)
    RETURN DATE
IS
    v_str VARCHAR2(32767);
    v_date DATE;
BEGIN
    SELECT   high_value
      INTO   v_str
      FROM   dba_tab_partitions
     WHERE   table_owner = p_owner AND table_name = p_table AND partition_name = p_partition;
 
    IF UPPER(v_str) = 'MAXVALUE'
    THEN
        v_date := TO_DATE('9999-12-31 23:59:59', 'yyyy-mm-dd hh24:mi:ss');
    ELSE
        EXECUTE IMMEDIATE 'select ' || v_str || ' from dual' INTO   v_date;
    END IF;
    RETURN v_date;
EXCEPTION
    WHEN OTHERS
    THEN
        DBMS_OUTPUT.put_line(v_str || ' ' || SQLERRM);
        RETURN NULL;
END;

Also,
Stuber,
If i want to put a specific date data into one specific partition--
say p1_20090103 will have data for 20090102 only,
p1_20090104 will have data for 20090103 only.
for which am creating daily partitions base on sysdate..
For this only range partitioning will work and not List partitioning. Please advise if am wrong.

you "could" use list partitioning, but you'd have to list every possible date value you wanted in your partitions.  Since one day has 86400 seconds that could each be  distinct date type,  it's not really feasible, or legal to list all of them.

if your data was just for a single day (time=00:00:00) or hour  (minutes/seconds=00:00) then it might be possible but still not particularly useful.

I'd use range partitioning, plus, in 11g your range partitions can be generated automatically for you based on a daily interval

Hi,

I'm not sure to understand why you need the partition name.
But as you say that you want it for actual data (then I hunderstand for rows that re already in table), you should consider to use pmarker:
- you get the rowid for the firstrow that fits your date
- you query dba_objects where data_object_id = dbms_mview.pmarker( <the rowid you get> )
and you have partition name

This is very fast (because the rowid has the partition information insite it - as the data object id)

Regards,
Franck.