как получить имена разделов в Oracle, когда я ввожу дату

WITH DATA AS (
select table_name,
       partition_name,
       to_date (
          trim (
          '''' from regexp_substr (
                     extractvalue (
                       dbms_xmlgen.getxmltype (
                       'select high_value from all_tab_partitions where table_name='''
                                || table_name
                                || ''' and table_owner = '''
                                || table_owner
                                || ''' and partition_name = '''
                                || partition_name
                                || ''''),
                             '//text()'),
                          '''.*?''')),
          'syyyy-mm-dd hh24:mi:ss')
          high_value_in_date_format
  FROM all_tab_partitions
 WHERE table_name = 'SALES' AND table_owner = 'SH'
 )
 SELECT * FROM DATA
   WHERE high_value_in_date_format < SYSDATE
/

TABLE_NAME           PARTITION_NAME       HIGH_VALU
-------------------- -------------------- ---------
SALES                SALES_Q4_2003        01-JAN-04
SALES                SALES_Q4_2002        01-JAN-03
SALES                SALES_Q4_2001        01-JAN-02
SALES                SALES_Q4_2000        01-JAN-01
SALES                SALES_Q4_1999        01-JAN-00
SALES                SALES_Q4_1998        01-JAN-99
SALES                SALES_Q3_2003        01-OCT-03
SALES                SALES_Q3_2002        01-OCT-02
SALES                SALES_Q3_2001        01-OCT-01
SALES                SALES_Q3_2000        01-OCT-00
SALES                SALES_Q3_1999        01-OCT-99
SALES                SALES_Q3_1998        01-OCT-98
SALES                SALES_Q2_2003        01-JUL-03
SALES                SALES_Q2_2002        01-JUL-02
SALES                SALES_Q2_2001        01-JUL-01
SALES                SALES_Q2_2000        01-JUL-00
SALES                SALES_Q2_1999        01-JUL-99
SALES                SALES_Q2_1998        01-JUL-98
SALES                SALES_Q1_2003        01-APR-03
SALES                SALES_Q1_2002        01-APR-02
SALES                SALES_Q1_2001        01-APR-01
SALES                SALES_Q1_2000        01-APR-00
SALES                SALES_Q1_1999        01-APR-99
SALES                SALES_Q1_1998        01-APR-98
SALES                SALES_H2_1997        01-JAN-98
SALES                SALES_H1_1997        01-JUL-97
SALES                SALES_1996           01-JAN-97
SALES                SALES_1995           01-JAN-96

28 rows selected.

SQL>

Используйте желаемую дату вместо SYSDATE в запросе выше. Или вы можете передать его как INPUT через FUNCTION и RETURN набор результатов.

Найдите имя раздела по дате, ЕСЛИ у вас есть значимый столбец даты в таблице в Oracle DB

WITH table_sample AS (select COLUMN_WITH_DATE from table SAMPLE (5))



SELECT uo.SUBOBJECT_NAME AS "PARTITION_NAME_1"
FROM table_sample sw,

  SYS.USER_OBJECTS uo 

WHERE sw.COLUMN_WITH_DATE = TRUNC(SYSDATE) -- ENTER DATE HERE AS 'DD-MM-YYYY 00:00:00'

AND OBJECT_ID              = dbms_rowid.rowid_object(sw.rowid)

AND ROWNUM                 < 2;

Я знаю, что эта проблема старая, но я наткнулся на нее в поисках чего-то и подумал, что буду взвешивать, чтобы другие не пошли по дороге, описанной выше. Предоставленные ответы делают ее намного сложнее, чем должно быть. Вы можете использовать dbms_rowid.rowid_object(), чтобы получить идентификатор объекта данных строки и присоединить его к user_objects, all_objects или dba_objects (в зависимости от того, что соответствует вашим потребностям).

Примерно так должно работать ...

select distinct 
       o.subobject_name
  from user_objects o,
       my_table x
 where o.object_name = 'MY_TABLE'
   and dbms_rowid.rowid_object( x.rowid ) = o.data_object_id
   and trunc( x.stamp ) > ( current_timestamp - 31 );

Я использую это для кода, который должен определять имена разделов по разным причинам (запросы, DML и т. Д.). Я даже использовал это совсем недавно, чтобы идентифицировать разделы, которые выпали за пределы определенного окна, как функцию автоматического удаления для таблицы с интервалами.

Решение для одного SQL: (high_value должно быть преобразовано в дату в правильном формате!)

SELECT
 partition_name p
  from user_tab_partitions
 where table_name='TEST'
 AND high_value < to_date('4444-01-01','yyyy-mm-dd') AND high_value > SYSDATE;

Решение PL / SQL:

Создайте глобальный тип;

create type ty_partition_names is table of varchar2(30);
/

Функция:

create or replace function get_part_name(p_date in date)
return ty_partition_names is
d date;
retp ty_partition_names := ty_partition_names();
mind date:=to_date('4444-01-01','yyyy-mm-dd');
str varchar2(32000);
idx number := 0;
cursor c is
select high_value, partition_name p
  from user_tab_partitions
 where table_name='test';
begin
  for r in c loop
     str := r.high_value;
     /*execute immediate 'select '||str||' from dual' into d;     */
     if p_date<str and str <mind then
        retp.extend(1);
        idx := idx + 1;
        retp(idx):=r.p;
        mind:=str;
     end if;
  end loop;
  return retp;
end;

И наконец,

SELECT * FROM TABLE(get_part_name(sysdate));