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.