Script to Generate Sequence creation DDL for all primary keys using the current values

This is best achieved by a PL/SQL procedure that generates the required DDL.

Please create below procedure, which can generate DDLs given a schema name:

CREATE OR REPLACE procedure GenerateSeqDDL(schemaowner in varchar2)
IS
  TYPE RecTyp IS RECORD (
tabname VARCHAR2(30),
colname VARCHAR2(30));

  TYPE TabTyp IS TABLE OF RecTyp INDEX BY BINARY_INTEGER;
  sqtab   TabTyp;
  i BINARY_INTEGER;
  m number;

  cursor c1 is
select cons.table_name, col.column_name, cons.owner
from dba_constraints cons
inner join dba_cons_columns col on cons.owner = col.owner and cons.constraint_name = col.constraint_name
inner join dba_tab_cols cols on col.owner = cols.owner and cols.table_name = col.table_name and col.column_name = cols.column_name
where constraint_type = 'P' and cons.owner=schemaowner and cols.data_type = 'NUMBER';
BEGIN

  i := 1;

  FOR rec in c1
  LOOP
sqtab(i).tabname := rec.table_name;
sqtab(i).colname := rec.column_name;
dbms_output.put_line(rec.table_name);
i := i+1;
  END LOOP;

      FOR i2 IN 1..sqtab.count LOOP
execute immediate 'select max(' || sqtab(i2).colname ||') +1 from ' || schemaowner || '.' || sqtab(i2).tabname into m;
DBMS_OUTPUT.PUT_LINE('CREATE SEQUENCE ' || sqtab(i2).tabname || '_PK_SEQ START WITH ' || m || ' MAXVALUE 999999999999999999999999999 MINVALUE 1 NOCYCLE CACHE 20 NOORDER;');
END LOOP;

ENd;
/

Once created, you can execute it as follows, with the schema name as a parameter.

SET SERVEROUTPUT ON
EXEC GenerateSeqDDL('HR');

This will print out create sequence statements for all primary keys in the HR schema, based on the current max value for each table.