How to reset identity columns in Oracle (PL SQL)

Question

I need to write a script to reset all auto-incrementing fields in the database to 0.

Answer 1

I want to share the solution to the problem.

To get a list of table names, I do the following:

SELECT T.TABLE_NAME FROM ALL_TAB_COLUMNS C
INNER JOIN ALL_TABLES T ON C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME
WHERE T.OWNER = 'SCHEME'
AND C.DATA_DEFAULT IS NOT NULL

Further in the loop, you can generate dynamic sql as follows:

ALTER TABLE TABLE_NAME
MODIFY ID 
GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1);

And we execute the dynamic SQL line through EXECUTE IMMEDIATE.

Below is an example from my project:

PROCEDURE RESET_ALL_IDENTITY_COLUMNS IS
BEGIN
  FOR TABLES_WITH_IDENTITY IN (SELECT T.TABLE_NAME FROM ALL_TAB_COLUMNS C
                               INNER JOIN ALL_TABLES T ON C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME
                               WHERE T.OWNER = 'DZR'
                               AND C.DATA_DEFAULT IS NOT NULL)
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || TABLES_WITH_IDENTITY.TABLE_NAME || ' MODIFY ID GENERATED BY DEFAULT ' ||
                     'ON NULL AS IDENTITY (START WITH 1)';
  END LOOP;
END;

Good luck :)