Oracle Max ID in Tables

Posted on 2016-09-27
Last Modified: 2016-09-28
Is there a way in PSQL to look at the tables  in (PROD1) and see...

The Maximum possible ID for that table (Or set of tables)
And the current MAX(ID) in that table(s)

This is an old Oracle Database
I have access to TOAD
Question by:lrbrister
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41819050
You can use below.


Open in new window

The maximum possible Id depends on the datatype you have used for Id column.

As per Oracle documentation below are the minimum and the maximum value we can store for numeric data types in Oracle.


A whole number in the range of (-2**31) to (2**31)-1.


A whole number in the range of (-2**15) to (2**15)-1.


A whole number in the range of (-2**63) to (2**63)-1.


A decimal number with up to 15 significant digits in the range of -(10**308) to +(10**308).


A decimal number with up to 7 significant digits in the range of -(10**38) to +(10**38).


A decimal number with up to 38 significant digits in the range of -(10**125) to +(10**125).
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41819051
Maximum possible is likely based on the data type and the maximum value it can hold.

If generated by a sequence, then it is the maximum value allowed by the sequence.

If you have a list of tables, then the above comments apply to the entire list.

'old Oracle Database' doesn't mean anything.  What version?  Please include all 4 numbers, or example

All that said, can explain just a little more about exactly what you are trying to do?

Author Comment

ID: 41819068
Two tables id's reached the "set " maximum allowed number

And inserts stopped

I want to know the current max ID

And the maximum allowed "ceiling" ID on the table
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 28

Expert Comment

by:Pawan Kumar
ID: 41819072
In this case you need to alter the datatype to the higher datatype.

Do take backup of the existing table and then try.

Alter Table table_name 
modify (  column_name LONG );

Open in new window

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 41819074
Sorry but that doesn't tell me anything.

If you are getting some error message, please provide it.

For example, if you have a sequence called MYSEQ and exceed it's value then you should get:
ORA-08004: sequence MYSEQ.NEXTVAL exceeds MAXVALUE and cannot be instantiated

To get the max value for it:
select max_value from user_sequences where sequence_name='MYSEQ';

If you mean something different, please provide more details.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 41819075
>>modify (  column_name LONG );

NO.  Do not use a LONG data type.  They are a nightmare to work with.

Author Closing Comment

ID: 41819076
That gave me exactly what I needed
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41819077
Yes changing datatype is bit difficult.

But sometimes we have to do it.  You can use datatype based on your requirement.
LVL 37

Expert Comment

by:Geert Gruwez
ID: 41819203
really ? suggesting a long data type ?

Read the post upgrade tasks in the upgrade guide.
LVL 35

Expert Comment

ID: 41819600
Why would you recommend changing the data type from a numeric type to a character type?  That makes absolutely no sense at all.  Not only is it not a numeric type, but it is no longer a supported data type and hasn't been since 9i.  It hasn't even been in the documentation for quite a few versions.

Also, I am curious where you get the documentation reference for these types:


They aren't in the documentation and will generate an error if you try to use them.  INTEGER and DECIMAL just get translated to NUMBER(38).

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question