Solved

Oracle Max ID in Tables

Posted on 2016-09-27
10
25 Views
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 10.5.1.3
0
Comment
Question by:lrbrister
  • 3
  • 3
  • 2
  • +2
10 Comments
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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).
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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?
0
 

Author Comment

by:lrbrister
Comment Utility
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
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>modify (  column_name LONG );

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

Author Closing Comment

by:lrbrister
Comment Utility
That gave me exactly what I needed
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Yes changing datatype is bit difficult.

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

Expert Comment

by:Geert Gruwez
Comment Utility
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
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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).
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now