Oracle SQL Precision and Scale

Precision and Scale are two basic parts of Oracle number data type, and almost every Oracle SQL developer will encounter it. But recently I was confused on how much exactly Oracle SQL can store when giving precision and scale.

There are topics on SOF (http://stackoverflow.com/questions/5689369/what-is-the-difference-between-precision-and-scale-in-oracle) and also the definitions are given in Oracle document (http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313).

But one point I couldn’t find anywhere (maybe I didn’t notice in document), is that when exactly Oracle DB will throw error when storing numbers.

I had thought since Precision is number of total digits in a number and Scale is number of digits that right to decimal point, if the Precision is violated, i.e., when total number of digits more than defined Precision, Oracle will throw out error.

But it turns out that both Precision and Scale alone are not used to determine the error. It actually only throw out error when:
Number of Significant Digits (usually digits to the left of decimal point) > Precision – Scale

So if a number type defined as Number(8,2), so we have below scenario:

123456.12 Valid Stored as 123456.12
123456.123 Valid Stored as 123456.12
1234567 Invalid

Similarly, for type Number(8,-2):

1234567890 Valid Stored as 1234567800
123456.123 Valid Stored as 123400
12345678901 Invalid

Another interesting thing is that if you define the type as Number only, without giving Precision and Scale, Oracle will automatically take maximum precision which is 38, and use flexible scale based on input. But once you give the Precision, the scale will automatically becomes 0. So Number(38) is same as Number(38,0).

Find Oracle DB Locking

First to find out the session that caused the locking. Use any of below two SQLs.

Once you have session id, query v$session to find SQL_ID or PREV_SQL_ID.

And get the query from v$sql

Check DB Logon History