本文共 3164 字，大约阅读时间需要 10 分钟。

间隔分区表查分区名称

--转换Long类型函数准备：create or replace package long_helpauthid current_userasfunction substr_of( p_query in varchar2,p_from  in number,p_for   in number,p_name1 in varchar2 default NULL,p_bind1 in varchar2 default NULL,p_name2 in varchar2 default NULL,p_bind2 in varchar2 default NULL,p_name3 in varchar2 default NULL,p_bind3 in varchar2 default NULL,p_name4 in varchar2 default NULL,p_bind4 in varchar2 default NULL )return varchar2;end;/create or replace package body long_helpas    g_cursor number := dbms_sql.open_cursor;    g_query  varchar2(32765);procedure bind_variable( p_name in varchar2, p_value in varchar2 )isbegin    if ( p_name is not null )    then        dbms_sql.bind_variable( g_cursor, p_name, p_value );    end if;end;function substr_of( p_query in varchar2,  p_from  in number,  p_for   in number,  p_name1 in varchar2 default NULL,  p_bind1 in varchar2 default NULL,  p_name2 in varchar2 default NULL,  p_bind2 in varchar2 default NULL,  p_name3 in varchar2 default NULL,  p_bind3 in varchar2 default NULL,  p_name4 in varchar2 default NULL,  p_bind4 in varchar2 default NULL )return varchar2as    l_buffer       varchar2(4000);    l_buffer_len   number;begin    if ( nvl(p_from,0) <= 0 )    then        raise_application_error        (-20002, 'From must be >= 1 (positive numbers)' );    end if;    if ( nvl(p_for,0) not between 1 and 4000 )    then        raise_application_error        (-20003, 'For must be between 1 and 4000' );    end if;    if ( p_query <> g_query or g_query is NULL )    then        if ( upper(trim(nvl(p_query,'x'))) not like 'SELECT%')        then            raise_application_error            (-20001, 'This must be a select only' );        end if;        dbms_sql.parse( g_cursor, p_query, dbms_sql.native );        g_query := p_query;    end if;    bind_variable( p_name1, p_bind1 );    bind_variable( p_name2, p_bind2 );    bind_variable( p_name3, p_bind3 );    bind_variable( p_name4, p_bind4 );    dbms_sql.define_column_long(g_cursor, 1);    if (dbms_sql.execute_and_fetch(g_cursor)>0)    then        dbms_sql.column_value_long        (g_cursor, 1, p_for, p_from-1,         l_buffer, l_buffer_len );    end if;    return l_buffer;end substr_of;end;/--查询对应的表名，分区名和范围分区对应的时间SELECT *  FROM (SELECT TABLE_OWNER,                TABLE_NAME,                PARTITION_NAME,               substr(LONG_HELP.SUBSTR_OF('SELECT HIGH_VALUEFROM   DBA_TAB_PARTITIONS WHERE TABLE_OWNER=:TABLE_OWNER AND TABLE_NAME=:TABLE_NAMEAND PARTITION_NAME=:PARTITION_NAME',                                     1,                                     4000,                                     'TABLE_OWNER',                                     TABLE_OWNER,                                     'TABLE_NAME',                                     TABLE_NAME,                                     'PARTITION_NAME',                                     PARTITION_NAME),11,19) HIGH_VALUE           FROM DBA_TAB_PARTITIONS           where table_name in('XXX')           ) b           where b.high_value='2020-08-11 00:00:00' --选择11号，分区数据对应的是10号数据 order by HIGH_VALUE;----可以指定查询分区内容，用来确认上述查询的分区名称对应的实际数据select * from 分区表名 partition(分区名);

老博客地址:

转载地址：http://nzsof.baihongyu.com/