Query to check identity columns in a table and enable/disable options.

Please run the below query to get the output.

SELECT owner,
table_name,
column_name,
generation_type,
identity_options
FROM dba_tab_identity_cols
ORDER BY owner, table_name;

Generation type is below.

• GENERATED ALWAYS AS IDENTITY → Oracle always provides the value, you cannot insert into it directly (unless you temporarily change it).

• GENERATED BY DEFAULT AS IDENTITY → Oracle provides the value if you don’t supply one, but you can insert your own value if needed.

Ex:

alter table ABC.TAB_TRK modify id generated always as identity; – Disable

alter table ABC.TAB_TRK modify id generated by default as identity; – Enable