[Change] oracle dump function

Nov 25 posted in Oracle (RSS), 166 views.

dump function (number type) Note

dump function can see the table listed in the datafile in store content.
Oracle's NUMBER type of up to three parts, which are the highest of three parts, said bits, the data section, sign bit. Which contains a negative sign bit, a positive number does not include the sign bit (10 hex or 102). In addition, the value 0 is rather special, it contains a highest value bits 80 bits (16 hex), no data part.

Usage: DUMP (expr [, number_format [, start_position] [, length]])

Instructions: dump (col_name, 8 | 10 | 16 | 17), of which 8 | 10 | 16 | 17 to number_format, respectively, 8 octal, 10 decimal (the default), 16 hex, single character.

DUMP function, the output format similar to this:

Type "[length]", symbol / index-bit [number one, number two, number three ,......, Digital 20]

Members have the following meanings:

1. Type: Number Type, Type = 2 (type of code can be found from the Oracle documentation, see the end of the)

2. Length: refers to the number of bytes stored in

3. Symbol / index-bit

In storage, Oracle on the positive and negative, respectively, to store conversion:

Positive number: Add a storage (in order to avoid Null), the actual algorithm is to reduce a need to "128
Negative: The 101 reduced, if the total length of less than 21 bytes, the last plus a 102 (which is required in order to sort) must be "128

Index-bit conversion:

Positive: index = sign / index-bit - 193 (first byte)
Negative: Index = 62 - the first byte

4. From "number one" begin to be effective data bits

From "number 1" began as the most significant bit, the stored value calculated as follows:

Results of the calculation together the following:

Each "digits" multiplied by 100 ^ (index-N) (N is the effective number of bits of the sequence of bits, the first significant digit of N = 0)

Examples ==== === numbe type
Example 1: (difference between positive and negative)
SQL> select dump (0) from dual;

DUMP (0)
--------------------------------
Typ = 2 Len = 1: 128
#
Example 1 can be seen, why "128 judged to be positive, while the" 128 judged to be negative (10 decimal)

# # 16 hex analysis of positive and negative
SQL> select dump (0,16) from dual;

DUMP (0,16)
------------------------------
Typ = 2 Len = 1: 80----------- ---------( only high)

1 row selected.

DUMP (0) the result is 0x80, as has already been mentioned that bit 0 is only high, there is no data bits. As the 0 particular, are neither positive nor are negative, so that the use of high-bit with 80, said enough, I'm not, and other data conflicts, Oracle will be considered for space-saving part of eliminating the need for later data. But why does Oracle choose 0x80 says 0? We know that positive and negative, opposite each other number, for each positive number has a corresponding negative. Therefore, if we want to use the code, said values, said positive and negative coding should be equally divided, so as to ensure that the Oracle says the data range is reasonable. The 0x80 binary encoding is 10 million, exactly half the maximum value a byte encoding, therefore, Oracle select 0x80 to represent the 0, is very justified.

@ @ @ @

Example 2: (positive number)
SQL> select dump (1,10) from dual;

DUMP (1,10)
------------------------------------
Typ = 2 Len = 2: 193,2
| |
High, the data part of the

1 row selected.

#
SQL> select dump (1) from dual;

DUMP (1)
------------------------------------
Typ = 2 Len = 2: 193,2

1 row selected.
#
Example 2 can be seen dump (1,10) and dump (1) output is the same: Typ = 2 Len = 2: 193,2
Type is number, 2 bytes, because a positive number so "128, because it is stored at all times a positive number +1

"Index": 193 - 193 = 0
"Number 1" 2 - 1 = 1 * 100 ^ (0-0)
= 1

@@@@@@
Example 3: (positive index change)
SQL> select dump (99) from dual;

DUMP (99)
----------------------------------------
Typ = 2 Len = 2: 193,100

1 row selected.

#

SQL> select dump (100) from dual;

DUMP (100)
------------------------------------
Typ = 2 Len = 2: 194,2

1 row selected.

3 cases of contrast, we know that the first byte index change to 194 from 193

@@@@@@@@@

Example 4: (negative)
SQL> select dump (-98) from dual;

DUMP (-98)
------------------------------------------
Typ = 2 Len = 3: 62,3,102
| | |
High, the data part of the sign bit

1 row selected.
"Index of" 62 - 62 = 0
"Number 1" 101 - 3 = 98 * 100 ^ (0-0)
= 98

#
SQL> select dump (-99) from dual;

DUMP (-99)
------------------------------------------
Typ = 2 Len = 3: 62,2,102

1 row selected.

"Index of" 62 - 62 = 0
"Number 1" 101 - 2 = 99 * 100 ^ (0-0)
= 99

# # #
SQL> select dump (-100) from dual;

DUMP (-100)
----------------------------------------------
Typ = 2 Len = 3: 61,100,102

1 row selected

Finally add 102 is the goal:
Is actually stored in the -98 to 62,3
-99 To 62,2
-100 To 61,100

That -98 "-99" -100

However, if the
SQL> select dump (-98.001) from dual;

DUMP (-98.001)
-------------------------------------------------- ------
Typ = 2 Len = 5: 62,3,101,91,102

1 row selected.

Through this that there is no 102: -98.001 to 62,3,101,91 "-98 to 62,3

102: -98.001 to 62,3,101,91,102
-98 To 62,3,102

Therefore, the increase aimed at sorting the need for 102!

Example 5: (digital bit algorithm)
SQL> select dump (7654321) from dual;

DUMP (7654321)
-------------------------------------------------- ----
Typ = 2 Len = 5: 196,8,66,44,22

1 row selected.

"Index": 196 - 193 = 3
"Number 1" 8 - 1 = 7 * 100 ^ (3-0) 7 million
"Number 2" 66 - 1 = 65 * 100 ^ (3-1) 650000
"Number 3" 44 - 1 = 43 * 100 ^ (3-2) 4300
"Digital 4" 22 - 1 = 21 * 100 ^ (3-3) 21

#
SQL> select dump (87654321) from dual;

DUMP (87654321)
-------------------------------------------------- ------
Typ = 2 Len = 5: 196,88,66,44,22

1 row selected.

"Index": 196 - 193 = 3
"Number 1" 88 - 1 = 87 * 100 ^ (3-0) 87 million
"Number 2" 66 - 1 = 65 * 100 ^ (3-1) 650000
"Number 3" 44 - 1 = 43 * 100 ^ (3-2) 4300
"Digital 4" 22 - 1 = 21 * 100 ^ (3-3) 21

×××××××××××××××
Type Code (Reference)
Code data type oracle version of the
1 varchar2 7
2 number 7
8 long 7
12 date 7
23 raw 7
24 long raw 7
69 rowid 7
96 char 7
112 clob 8
113 blob 8
114 bfile 8
180 timestamp 9i
181 timestamp with timezone 9i
182 interval year to month 9i
183 interval day to second 9i
208 urowid 8i
231 timestamp with local timezone 9i