Oracle - Reset all sequences in a schema to start with 1 using batch file

Oracle data dictionary has a view called “user_sequences” , which helps out in finding all the available sequences in a data base schema.

So I can say “select * from user_sequences”, which lists out all the sequences and there details.

Now running the below sql queries and spooling the result into a file, gives all the required sql queries to drop and recreate sequences.

SELECT 'Drop sequence '||sequence_name||';' FROM user_sequences;

SELECT ‘create sequence '||sequence_name||' start with 1 increment by 1 nocache;' FROM user_sequences;

So I will write a “Script1.sql” file with content as

set heading off
set linesize 1500
set pagesize 1000;
set feedback off
spool Script2.sql
SELECT 'Drop sequence '||sequence_name||';' FROM user_sequences;
SELECT ‘create sequence '||sequence_name||' start with 1 increment by 1 nocache;' FROM user_sequences;
select 'commit;' from dual;
select 'exit;' from dual;
spool off
exit;


“Resetsequences.bat” (batch file) says

@echo off
setlocal
set /p schema=Please enter your TNSname:
set /p username=Please enter your Schema Username:
set /p password=Please enter your Schema Password:
sqlplus %username%/%password%@%schema% @Script1.sql
sqlplus %username%/%password%@%schema% @Script2.sql


When user double click on the above batch file, it prompts for the data base connection parameters (schema,user id, password).
Once the parameters are given, connets through sql plus and runs the “Script1.sql” file, which spools the result from the queries in Script1.sql to “Script2.sql”.

At last I have “Script2.sql” , which has required queries for dropping and recreating sequences.