Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Sequence Cache reset in 12c

Shamed HAug 27 2019 — edited Aug 29 2019

Hi Friends,

Wishing you a Good Day !!

I have a query on a sequence cache reset in 12c Database. As I know the sequence cache is at DB level in 12c ,  but what I am looking for is that the reasons where the cache is reset

Sequence

CREATE SEQUENCE  DOC_SEQ 

MINVALUE 1 MAXVALUE 9999999999

INCREMENT BY 1 START WITH 3000175009 CACHE 5000 NOORDER  NOCYCLE  NOPARTITION ;

As you can see the CACHE value is 5000 , but we are seeing in our DB the sequence's CACHE is getting lost for some reasons . From our analysis it is found that any restart of DB will reset the CACHE , but even there is no restart in DB CACHE is getting lost . Is there any values (reset time) at DB level which will reset the CACHE value

Note: I am not looking for the nextval function where the number will be lost , if the number is not used

Regards,

SH

This post has been answered by Sven W. on Aug 27 2019
Jump to Answer

Comments

Solomon Yakobson

And what CREATE TYPE has to do with generating series of numbers? And how generate_series would help eample in link you posted:

with sample as (
                select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' as multipart_lines
                        --There are more rows in the actual table.
                  from  dual
               )
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  sample,
        lateral(
                select  level part_num,
                        regexp_substr(multipart_lines,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(multipart_lines,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )
/

  PART_NUM VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ----------
         1          1          0          5          0
         1          2         10         10      11.18
         1          3         30          0      33.54
         2          1         50         10      33.54
         2          2         60         10      43.54

SQL>

SY.

Solomon Yakobson

and if you are on 19C:

create or replace
  function split_multipart_line(
                                p_line varchar2
                               )
    return varchar2
    sql_macro
    is
    begin
        return q'[
select  part_num,
        vertex_num,
        to_number(regexp_substr(vertex,'[^ ]+')) x,
        to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
        to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
  from  dual,
        lateral(
                select  level part_num,
                        regexp_substr(p_line,'\(([^()]+)',1,level,null,1) part
                  from  dual
                  connect by level < regexp_count(p_line,'\(')
               ),
        lateral(
                select  level vertex_num,
                        regexp_substr(part,'[^,]+',1,level) vertex
                  from  dual
                  connect by level <= regexp_count(part,',') + 1
               )]';
end;
/

Function created.

SQL> with sample as (
  2                  select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' multipart_lines from dual union all
  3                  select '((1 2 3, 4 5 6, 7 8 9, 10 11 12),(22 33 44, 55 66 77))' multipart_lines from dual
  4                 )
  5  select  l.*
  6    from  sample,
  7          lateral(
  8                  select  *
  9                    from  split_multipart_line(multipart_lines)
 10                 ) l
 11  /


  PART_NUM VERTEX_NUM          X          Y          Z
---------- ---------- ---------- ---------- ----------
         1          1          0          5          0
         1          2         10         10      11.18
         1          3         30          0      33.54
         2          1         50         10      33.54
         2          2         60         10      43.54
         1          1          1          2          3
         1          2          4          5          6
         1          3          7          8          9
         1          4         10         11         12
         2          1         22         33         44
         2          2         55         66         77


11 rows selected.

SQL>

SY.

Mike Kutz

19c+ code. Its also on GitHub

package generate_series
authid definer
as
    /* package of SQL_MACROS for generating series of values
    *
    * @headcom
    */

    /* Generates a series of integers.
    *
    * @param start_value initial integer
    * @param n The number of row to generate
    * @return Series of integers
    */
    function of_numbers( start_value in int, n in int ) return varchar2 SQL_MACRO(TABLE);

    /* Generates a series of integers.
    *
    * @param start_value initial date
    * @param n The number of row to generate
    * @return Series of Dates
    */
    function of_dates( start_value in date, n in int ) return varchar2 SQL_MACRO(table);
end;
/

create or replace
package body generate_series
as
    function of_numbers( start_value in int, n in int ) return varchar2 SQL_MACRO(TABLE)
    as
    begin
        return q'[select of_numbers.start_value + (level - 1) number_series
        from dual
        connect by level <= of_numbers.n]';
    end;

    function of_dates( start_value in date, n in int ) return varchar2 SQL_MACRO(table)
    as
    begin
        return q'[select of_dates.start_value + (level - 1) date_series
        from dual
        connect by level <= of_dates.n]';
    end;
end;
/

1 - 3

Post Details

Added on Aug 27 2019
9 comments
3,291 views