How can we manage the gap in a primary key column created
by a sequence?
Ex:a company has empno as primary key generated by a
sequence and some employees leaves in between.What is the
best way to manage this gap?

Answers were Sorted based on User's Feedback

How can we manage the gap in a primary key column created by a sequence? Ex:a company has empno a..

Answer / poornima

using cursor we find the gap in sequence, but this method
is quite complicated and slow process...

so we use some analyical fuctions such as lead and lag as

{LEAD | LAG} (value_expression, offset, default)

Is This Answer Correct ?    5 Yes 0 No

How can we manage the gap in a primary key column created by a sequence? Ex:a company has empno a..

Answer / harikanthkoneti

yes...yes........ you can do it simply by updating that
table using rownum yar. see my example below.
SQL> create table test(sno number(2),sname varchar2(20));

Table created.

now if you have the data in the table like follows.

SQL> select * from test;

---------- --------------------
1 sdf
3 sdf
6 ewr
7 rt
90 drt

now just do the following simple statement.

SQL> update test set sno=rownum;

5 rows updated.

now here is the o/p for eliminating the gap generated by the

SQL> select * from test;

---------- --------------------
1 sdf
2 sdf
3 ewr
4 rt
5 drt
then do the commit. thats all....................
now enjoy with this coding

suppose if you want to allocate the with any kind of series
of values for updating the table just follow the below

SQL> update test set sno=sql.nextval;

NOTE:sql is the sequence.

this statement will updates ur table with
what ever you given with the start with value and it ll
increment by which value you had given in the increment by

Is This Answer Correct ?    1 Yes 3 No

Post New Answer

More Oracle General Interview Questions

What is columnar storage what is the advantage?

0 Answers  

How to retrieve values from data fields in record variables?

0 Answers  

Hi All, Recently I attended a interview for a developer position in an educational university. They asked me the a question, I answered somehow but I was not not satisfied myself. Can anybody reply for it? The question is: A business user comes to you directly with an urgent request:: there is a problem with some students not being able to re-enrol. It appears that ?something has gone wrong with the end-dating of some previous courses they had enrolled in, and this is preventing them from completing their on-line re-enrolments?. Fortunately the user has done some analysis on the situation, and has developed some code to change the end dates. They tell you that they have tested this code in the ?dev? environment, and it works fine. They are asking you to please get this implemented in production as soon as possible. What steps would you take in response to their request ? Thanks

2 Answers  

what are the advantages of running a database in archive log mode?

0 Answers  

what is the syntax of INSERT command?

7 Answers  

Which are the five query types available in oracle?

0 Answers  

What is the function of Redo Log ?

1 Answers  

How we can able to import our own template (users designed MS-Word templates) which has many tabular columns; need to pass some values generate by Oracle-reports9i? Actually need to import more than 400 MS-Word templates into Oracle Reports-9i to minimize layout design in Reports.

0 Answers  

Difference between open_form and call_form in oracle.

0 Answers  

What is a nested table?

0 Answers  

What is the scope of a local variable?

0 Answers  

How can you merge two tables in oracle?

0 Answers  

  • Oracle General Interview Questions Oracle General (1789)
  • Oracle DBA (Database Administration) Interview Questions Oracle DBA (Database Administration) (261)
  • Oracle Call Interface (OCI) Interview Questions Oracle Call Interface (OCI) (10)
  • Oracle Architecture Interview Questions Oracle Architecture (90)
  • Oracle Security Interview Questions Oracle Security (38)
  • Oracle Forms Reports Interview Questions Oracle Forms Reports (509)
  • Oracle Data Integrator (ODI) Interview Questions Oracle Data Integrator (ODI) (120)
  • Oracle ETL Interview Questions Oracle ETL (15)
  • Oracle RAC Interview Questions Oracle RAC (93)
  • Oracle D2K Interview Questions Oracle D2K (72)
  • Oracle AllOther Interview Questions Oracle AllOther (241)