how to drop the generation type of oracle table-CodePudding

I have a table that generates an id column using 'GENERATED ALWAYS AS IDENTITY'. The creation script is like the following.

CREATE Table DATA_MIGRATION.Test_alter (
id INTEGER GENERATED ALWAYS AS IDENTITY 
 (START WITH 1 
 INCREMENT BY 2 
 MAXVALUE 100 
 CACHE 10 
 CYCLE),
 name VARCHAR(255), PRIMARY KEY (id)
);

I need to insert some values using this. But later I want to generate the values using a sequence. I have tried to follow the oracle docs which suggest a script that follows

ALTER TABLE Test_alter (MODIFY id DROP IDENTITY);

However, I am getting an error saying invalid ALTER TABLE option. I am using oracle 18g.

CodePudding user response：

Document you read is related to Oracle NoSQL database, and that's not what you use.

Anyway:

SQL> SELECT banner FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production

SQL> CREATE TABLE test_alter
  2  (
  3     id         INTEGER
  4                  GENERATED ALWAYS AS IDENTITY
  5                     (START WITH 1 INCREMENT BY 2 MAXVALUE 100 CACHE 10 CYCLE),
  6     name       VARCHAR (255),
  7     PRIMARY KEY (id)
  8  );

Table created.

SQL> ALTER TABLE test_alter
  2     MODIFY id DROP IDENTITY;

Table altered.

SQL>