SQL 输入日期时如何在oracle中获取分区名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26399000/
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 get names of partition in oracle while i input a date
提问by Charles Peter
I have a table with many partitions range. I need to get the name of all partition when I give a date. For eg: if I input date 20/09/2014, it should list all partitions before that given date.
我有一个包含许多分区范围的表。当我给出日期时，我需要获取所有分区的名称。例如：如果我输入日期 20/09/2014，它应该列出给定日期之前的所有分区。
create or replace function get_part_name(p_date in date)
return varchar2 is
d date;
retp varchar2(30);
mind date:=to_date('4444-01-01','yyyy-mm-dd');
str varchar2(32000);
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<d and d<mind then
retp:=r.p;
mind:=d;
end if;
end loop;
return retp;
end;
This is returing a single date. I need all the dates, is it possible?
这是返回单个日期。我需要所有日期，可以吗？
采纳答案by Lalit Kumar B
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>
Use your desired date in place of SYSDATEin above query. Or you can pass it as INPUT through the FUNCTIONand RETURNthe result set.
使用您想要的日期代替SYSDATE上述查询。或者你可以把它作为通过INPUTFUNCTION和RETURN结果集。
回答by gidi gob
Find Partition name using date IF you have a meaningful date column in the table in Oracle DB
如果 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;
回答by nvanwyen
I know this issue is old, but I ran across it looking for something and thought I'd weigh in to prevent others from going down the road above.The answers provided make it way more difficult than it has to be. You can use the dbms_rowid.rowid_object()to get the data object id of the row and join that with either user_objects, all_objectsor dba_objects(whichever fits your needs).
我知道这个问题很老，但我在寻找一些东西时遇到了它，并认为我会权衡以防止其他人走上这条路。提供的答案使它变得比它更困难。您可以使用dbms_rowid.rowid_object()来获取行的数据对象 id 并将其与user_objects,all_objects或dba_objects（以您的需要为准）连接起来。
Something like this should work ...
这样的事情应该工作......
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 );
I use this for code that has to determine partition names, for various reasons (queries, DML, etc...). I even used this very recently identify the partitions which have fallen outside of a defined window, as an auto-drop feature for an interval partitioned table.
由于各种原因（查询、DML 等），我将它用于必须确定分区名称的代码。我什至最近使用它来识别超出定义窗口的分区，作为间隔分区表的自动删除功能。
回答by Maheswaran Ravisankar
Single SQL Solution:(high_value has to converted to date with correct format!)
单个 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 Solution:
PL/SQL 解决方案：
Create a global type;
创建一个全局类型；
create type ty_partition_names is table of varchar2(30);
/
Function:
功能：
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;
And Finally,
最后，
SELECT * FROM TABLE(get_part_name(sysdate));
