Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Oracle Max ID in Tables

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 10.5.1.3
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

You can use below.

SELECT MAX(ID) MaxId FROM TableName

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.

INTEGER

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

SHORTINTEGER

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

LONGINTEGER

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

DECIMAL

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

SHORTDECIMAL

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

NUMBER

A decimal number with up to 38 significant digits in the range of -(10**125) to +(10**125).
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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

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

ASKER

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

ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>modify (  column_name LONG );

NO.  Do not use a LONG data type.  They are a nightmare to work with.
That gave me exactly what I needed
Yes changing datatype is bit difficult.

But sometimes we have to do it.  You can use datatype based on your requirement.
Pawan,
really ? suggesting a long data type ?

Read the post upgrade tasks in the upgrade guide.
http://docs.oracle.com/database/121/UPGRD/afterup.htm#UPGRD12441
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:

SHORTINTEGER
LONGINTEGER
SHORTDECIMAL

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