Archive for the ‘Oracle’ 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.

Advertisements
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.

These days I am working on a project in which there was a requirement to track the User. The tracking was to be done at two levels: First the User Id with which the user logs in into the VB(Visual Basic) application (this is easy, from the Login Screen) and secondly the actual user id who has logged into the machine. (Windows)

Click here to learn more..

The other day, while working on a project I needed to find all the tables in a schema with a particular column name. Say for example, I wanted to list all the tables having a column name ’empid’. Following is the Oracle query to do just the same.

select a.table_name, column_name,DATA_TYPE,DATA_LENGTH from all_tab_columns a,USER_ALL_TABLES u
where a.TABLE_NAME=u.TABLE_NAME
and column_name like ’empid%’
order by DATA_LENGTH desc;

This query will also help you to find the Datatype and length of the column name. So go explore..

Also check my post on sys_context function here. This helps to find information about the Oracle environment. Very useful.