Skip to Main Content
  • Questions
  • 12c Sequence - nopartition/partition

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jan.

Asked: October 16, 2015 - 8:23 am UTC

Last updated: August 04, 2017 - 7:01 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

What is the meaning of a new parameter when creating a sequence?
From dbms_metadata.get_ddl:

CREATE SEQUENCE "XY"."MY_SEQUENCE" MINVALUE 1000 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE NOPARTITION;

and Connor said...

Nopartition is an undocumented feature.

Partition N generates values in N different ranges. Franck Pachot has written some notes about it at:

http://blog.dbi-services.com/oracle-partitioned-sequences-a-future-new-feature-in-12c/

Remember, this is an undocumented feature. Use at your own risk!

Addenda:
========
This feature is REMOVED in 12.2 so do not use it - you were not meant to using it anyway :-)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Undocumented feature hidden AND unsupported in 12.2

Lars Johan Ulveseth, May 26, 2017 - 9:32 am UTC

In 12.1 DBMS_METADATA.GET_DDL ('SEQUENCE',....) return the undocumented clause PARTITION/NOPARTITION.
The column PARTITION_COUNT exists in all *_SEQUENCE views, and in the underlying table seq$ (PARTCOUNT).

In 12.2 DBMS_METADATA.GET_DDL don't return the undocumented clause any longer.
The column PARTITION_COUNT don't exist in any of the *_SEQUENCE views, but the column PARTCOUNT remains in table $seq.

In 12.2 the create sequence statement fails if PARTITION/NOPARTITION clause is included.
DDL scripts generated in 12.1 DBMS_METADATA.GET_DDL are not runnable in 12.2. since the PARTITION/NOPARTITION now is unsupported.

Example:
Connect to 12.1 instance:
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK ON VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
   DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', false);
END;
/

CREATE SEQUENCE TEST_SEQ;

SELECT DBMS_METADATA.GET_DDL ('SEQUENCE','TEST_SEQ') FROM DUAL;

DROP SEQUENCE TEST_SEQ;

CREATE SEQUENCE  "TEST_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOPARTITION ;
The code listed above succeeds for both the short CREATE SEQUENCE TEST_SEQ; statement and the longer DBMS_METADATA generated statement.


Connect to 12.2 instance and try the 12.1 generated statement:
SQL> CREATE SEQUENCE  "TEST_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOPARTITION ;
CREATE SEQUENCE  "TEST_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOPARTITION
                                                                                                                                     *
ERROR at line 1:
ORA-00933: SQL command not properly ended

When removing the NOPARTITION clause, the create sequence statement is OK:
SQL> CREATE SEQUENCE  "TEST_SEQ"  MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE;

Sequence created.

So this undocumented feature was possible to use in 12.1 and DBMS_METADATA return the clause (this might be a bug?)
In 12.2 the feature is unsupported. Scripts generated from 12.1 DBMS_METADATA will fail in 12.2.


Connor McDonald
May 27, 2017 - 6:42 am UTC

You can read about partitioned sequences here

https://blog.dbi-services.com/oracle-partitioned-sequences-a-future-new-feature-in-12c/

but as you've seen, it was never documented, and subsequently is not in 12.2 anymore.

"Scripts generated from 12.1 DBMS_METADATA will fail in 12.2"

I dont think that's particular profound - there's been various of things in previous versions of the database that are no longer relevant/supported as newer versions are released.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database