Archive for the ‘PL/SQL’ Category

Reset Oracle Sequence

Posted: December 16, 2009 in Oracle, PL/SQL

While on one of my projects in recent time I faced a situation where we needed to reset the value of the sequence of Oracle. Following are steps I followed to do the same.

1) Suppose you create a SEQUENCE like shown below:

CREATE SEQUENCE TESTSEQ
INCREMENT BY 1
MINVALUE 1
MAXVALUE 500
NOCACHE
NOCYCLE
NOORDER

2) Now you fetch values from SEQUENCE. Lets say I have fetched four times as shown below.

SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual
SELECT TESTSEQ.NEXTVAL FROM dual

3) After executing above four commands the value of the SEQUENCE will be 4. Now suppose I have to reset the value of the SEQUENCE to 1 again. The follow the

following steps. Follow all the steps in the same order as shown below:

 a) ALTER SEQUENCE TESTSEQ INCREMENT BY -3;
 b) SELECT TESTSEQ.NEXTVAL FROM dual
 c) ALTER SEQUENCE TESTSEQ INCREMENT BY 1;
 d) SELECT TESTSEQ.NEXTVAL FROM dual

and voilla you’ve done it!!! Happy learning and keep posting all your nitty oracle tips to help others learn….

All your comments and suggestions are most welcome.

That day I was working on some change in which I needed to find all the stored procedures, triggers, functions and Packages which may contain a variable called “vUserID”. Surprised
Guys to tell you the database of this application was big enough to take me ages to do this task. So what do to…Thinking
After doing some research I found a way to do this using following script.Smile
 

select

DISTINCT(name) from user_source

where

type = PROCEDURE

and

text like ‘%vUserID%’ ;

 _________________________________________________
 

select DISTINCT(name) from user_source

where

type = ‘TRIGGER’

and

text like ‘%vUserID%’ ;

  _________________________________________________
 
select DISTINCT(name) from user_source

where

type = FUNCTION

and

text like ‘%vUserID%’ ;

  _________________________________________________
 

select DISTINCT(name) from user_source

where

type = PACKAGE BODY

and

text like ‘%vUserID%’ ;

 _________________________________________________
And suddenly things became. The above scripts will shows names of all PROCEDURE, TRIGGER, FUNCTION and PACKAGE with the text line “vUserId”. Like wise you can also find the any text in these database objects.
 
So as I always say:- Keep reading, learning and sharing. Please do post your suggestions.