• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

Sequences and table volume

Hi,

There was a table with a column based on sequence numbers.
This column is the primary key of the table.
When the table count hits the value 9999, a system error saying all sequences are used will come. Is there any script that can be run by DBA, to monitor this and avoid this overflow ?

Please help..

This is Oracle database.
0
neoarwin
Asked:
neoarwin
  • 2
  • 2
1 Solution
 
DavidSenior Oracle Database AdministratorCommented:
A SQL script could be written to query the column value, then mail the DBA when a threshold is passed.  

Unfortunately, primary keys are unique by definition, so any recycling action would be pointless.

Assuming the PK column has a numeric datatype, there is little reason to constrain the counter to four digits -- that sounds to be a poor design.

Subject to your environment and situation, I suspect the solution should be to drop the sequence, and to recreate it using the parameter NOMAXVALUE.  The syntax for the DROP and CREATE statements are found here: click me
0
 
neoarwinAuthor Commented:
I don't need a solution as this is 3rd party vendor tool.
Can you give me the script which will be helpful in monitoring ?
0
 
slightwv (䄆 Netminder) Commented:
>>Can you give me the script which will be helpful in monitoring ?

You can query user_sequences with the sequence_names you want to monitor and look at the last_value column to see if you are getting close to the max_value column.
0
 
DavidSenior Oracle Database AdministratorCommented:
I'm puzzled about what action the DBA is expected to take when your threshold is reached.  Yes, one choice is to delete obsolete rows -- but the issue is, the sequence has hit its defined maximum value.  As written, the next insert would be for value 10000 -- that's your error.

The sequence cannot be altered, only dropped and recreated as I explained above.  I'll clarify the obvious, that the new MINVALUE must be higher than the highest existing sequence -- such as 10000.   Hope this helps.
0
 
neoarwinAuthor Commented:
Thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now