Aravindan GP
asked on
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.
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.
ASKER
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 ?
Can you give me the script which will be helpful in monitoring ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
Thank you
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