6

I have Oracle version 12.2.0.1.0

We have generic script which create sequence that need to be reuse for different objects (by renaming sequence name):

CREATE SEQUENCE NAME_SEQ MINVALUE 1 MAXVALUE 999999999
INCREMENT BY 1 START WITH 100 CACHE 200 NOORDER  NOCYCLE  NOPARTITION ;

This script isn't working with below error until I remove NOPARTITION :

   ORA-00933: SQL command not properly ended

I found in AskTom that the NOPARTITION is not supported in 12.2

there's been various of things in previous versions of the database that are no longer relevant/supported

Why it's not supported and/or what is the replacement of this feature ?

If there's no replacement can you state why this feature shouldn't be used?

Community
  • 1
  • 1
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
  • 1
    The actual question is: why do you think you need it? Especially taking into account that `nopartition` wasn't even documented in previous versions. –  Oct 10 '18 at 05:54
  • @a_horse_with_no_name I have a "legacy" script which i want to make sure I'm not missing anything significant if I remove `NOPARTITION ` – Ori Marko Oct 10 '18 at 05:59
  • That's the risk when you use undocumented features... – Wernfried Domscheit Oct 10 '18 at 06:12

2 Answers2

5

The (no)partition option for sequences was never documented. And thus never supported.

There was a bug in 12.1 which exposed this via dbms_metadata. It no longer happens in 12.2

Undocumented features can (and as this proves) do change without warning. Using them is strictly at your own risk.

Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
2

If you do a dbms_metadata.get_ddl from database version 12.1 and want to use it in 12.2 then a NOPARTITION is supplied. Now we have to remove it.

Rine Huiden
  • 21
  • 1