How to find out if a column is an auto increment field in oracle?

Question

How to find out if a column is an auto increment field in oracle?

Answer 1

You can't. Oracle doesn't have auto-increment fields.

Oracle supports sequences, which are separate database objects for generating numbers. These seuqences can be used in the insert statement or in a before insert trigger to generate a primary key value for the new record.

The fields themselves are just normal field and there is no relation between the sequence and the field.

So the only hope is to parse a trigger and see if you can find evidence of it being filled there using a sequence. That would be a tough job, though, and I doubt if you can make this reliable enough. After all, the trigger could fire a function which returns the value, so you have to go all the way if you want to be sure.

Answer 2

As of Oracle 12.1 identity columns (with self generated sequences) can be identified by looking at the "DATA_DEFAULT" column of view DBA_TAB_COLUMS (or USER_TAB_COLUMNS or ALL_TAB_COLUMNS). They will look like "#OWNER"."ISEQ$$_nnnnn".nextval