java long raw_Long raw和Long类型总结

Long和Long raw是要被Oracle淘汰的数据类型，今天总结一下他们的Storage Feature是因为俺的老板的要求。

对于哪些对Long和Long raw一些存储细节不是很清楚的朋友，而且又喜欢看看我拙劣的英文水平的朋友，推荐此文。

话不多说，直接奉上我的原文邮件(ps，测试都在在测试环境中完成，纯属playbox的垃圾机器)：

Summary of some behaviors of long raw and long

The LONG and LONG RAW datatypes are supported only for backward compatibility with existing applications. For new applications, use CLOB or NCLOB in place of LONG, and BLOB or BFILE in place of LONG RAW.

The maximum width of a Long or Long raw column is 2G.

The maximum length of which you can retrieve a value from a Long raw column into a Long raw variable is 32760 bytes.

Just like varchar2, Long or Long raw column physically is stored in line.

However, LOB can be stored out of line.

So , Long or Long raw column will caused more row chaining and row migration.

I think this is the main problem and disadvantage of Long or Long raw.

Below are some tests by myself and related conclusions:

1.Adding column will not move long raw/long to the last column.

create table haozhu (id number,name long raw);

insert into haozhu values (1,'aaa');

commit;

alter table haozhu add id2 number;

insert into haozhu values(111,'bbb',222);

commit;

SQL> select SEGMENT_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where wner = 'LONGRAW_USER' order by 4;

SEGMENT_NAME                   SEGMENT_TYPE                                              FILE_ID   BLOCK_ID     BLOCKS

------------------------------ ------------------------------------------------------ ---------- ---------- ----------

HAOZHU                         TABLE                                                           4         81          8

alter system dump datafile 4 block min 81 block max 88;

tab 0, row 0, @0x1f97

tl: 9 fb: --H-FL-- lb: 0x1  cc: 2

col  0: [ 2]  c1 02

col  1: [ 2]  0a aa

tab 0, row 1, @0x1f89

tl: 14 fb: --H-FL-- lb: 0x2  cc: 3

col  0: [ 3]  c2 02 0c

col  1: [ 2]  0b bb

col  2: [ 3]  c2 03 17

2.we can not move,can not CTAS tables with long/long raw

SQL> create table haozhu2 as select * from haozhu;

create table haozhu2 as select * from haozhu

*

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

SQL> alter table haozhu move;

alter table haozhu move

*

ERROR at line 1:

ORA-00997: illegal use of LONG datatype

3.exp/expdp a table with long/long raw column which is not the last column,

after imp/impdp, the column will be physically stored as the last column(no change to logical columns’ order).

SQL> select * from haozhu;

ID N        ID2

---------- - ----------

1 0

111 0        222

5000 0       4999

exp userid=longraw_user/longraw_user FILE=haozhu.dmp  TABLES=haozhu

drop table haozhu;

imp userid=haozhu/haozhu FILE=haozhu.dmp  TABLES=haozhu

SQL> desc haozhu

Name                                                                                    Null?    Type

--------------------------------------------------------------------------------------- -------- -----------------------------------------------------------

ID                                                                                               NUMBER

NAME                                                                                             LONG RAW

ID2                                                                                              NUMBER

SQL> select * from haozhu;

ID N        ID2

---------- - ----------

1 0

111 0        222

5000 0       4999

SQL> select SEGMENT_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where wner = 'HAOZHU' and SEGMENT_NAME='HAOZHU';

SEGMENT_NAME                   SEGMENT_TYPE                                              FILE_ID   BLOCK_ID     BLOCKS

------------------------------ ------------------------------------------------------ ---------- ---------- ----------

HAOZHU                         TABLE                                                           4         89          8

alter system dump datafile 4 block min 89 block max 96;

tab 0, row 0, @0x1f96

tl: 10 fb: --H-FL-- lb: 0x1  cc: 3

col  0: [ 2]  c1 02

col  1: *NULL*

col  2: [ 2]  0a aa                                ->long raw column becomes the last column physically

tab 0, row 1, @0x1f88

tl: 14 fb: --H-FL-- lb: 0x1  cc: 3

col  0: [ 3]  c2 02 0c

col  1: [ 3]  c2 03 17

col  2: [ 2]  0b bb

tab 0, row 2, @0x1f7b

tl: 13 fb: --H-FL-- lb: 0x1  cc: 3

col  0: [ 2]  c2 33

col  1: [ 3]  c2 32 64

col  2: [ 2]  0c cc

end_of_block_dump

SQL> select COL#,SEGCOL#,NAME from COL$ where OBJ#=44150;

COL#    SEGCOL# NAME

---------- ---------- ------------------------------------------------------------------------------------------

1          1 ID

2          3 NAME

3          2 ID2

Ps, COL# is the logical order ,while SEGCOL# is the physical storage order for columns.

4.EXPDP can not support longrow/long column through network_link.

on source DB:

USER is "HAOZHU_USER"

SQL>  create table expdptest (id number,name long raw);

SQL> insert into expdptest values(1,'aaa');

SQL>  insert into expdptest values(2,'bbb');

SQL> commit;

on target DB:

USER is "LONGRAW_USER"

SQL> Create public Database Link cshan10

2  Connect To HAOZHU_USER identified by HAOZHU_USER

3  using 'cshan10';

SQL> create directory haozhu as '/export/home/oracle/haozhu';

expdp LONGRAW_USER/LONGRAW_USER DIRECTORY=haozhu NETWORK_LINK=cshan10 TABLES=expdptest DUMPFILE=expdptest.dmp LOGFILE=expdptest.log

“

ORA-31679: Table data object "HAOZHU_USER"."EXPDPTEST" has long columns, and longs can not be loaded/unloaded using a network link

Job "LONGRAW_USER"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 03:34:49

“

Ps, actually there are no rows exported into the dumpfile.

So after impdp, the imported table will be empty.

5.BUG 5598333:EXPDP CORRUPTS THE DATA FOR A LONG COLUMN

This is a known case for us that if we add one column to a table which has already a Long column,

Then we use empdp/impdp , the long raw column will be corrupted.

Actually there 3 conditions for the bug and all of them must be there , then we can re-produce it.

From the metalink bug:

“

A data corruption problem exists after a Data Pump import under the following conditions:

1. the character set of the export database was a multibyte character set such as al32utf8

2. the table contained a column of long datatype

3. the table had columns added after it was created using the SQL alter table statement.

“

In my own test environment, I tested multi times and can not reproduce this bug.

In the last,I found that is because my character set is US7ASCII,which is single-byte.

on US7ASCII database(Single byte)

SQL> select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME='NLS_CHARACTERSET';

PROPERTY_VALUE

----------------------------------------

US7ASCII

USER is "LONGRAW_USER"

SQL> create table longtest (id number,name long);

SQL> alter table longtest add id2 number;

SQL> insert into longtest values (1,'aaabbbccc',2);

SQL> commit;

SQL> select * from longtest;

ID NAME                                                                                    ID2

---------- -------------------------------------------------------------------------------- ----------

1 aaabbbccc                                                                                 2

expdp LONGRAW_USER/LONGRAW_USER DIRECTORY=haozhu TABLES=longtest DUMPFILE=longtest.dmp LOGFILE=longtest.log

SQL> drop table longtest;

impdp LONGRAW_USER/LONGRAW_USER DIRECTORY=haozhu TABLES=longtest DUMPFILE=longtest.dmp

SQL> select * from longtest;

ID NAME                                                                                    ID2

---------- -------------------------------------------------------------------------------- ----------

1 aaabbbccc2

on UTF8 database(multi bytes)

SQL> select PROPERTY_VALUE from DATABASE_PROPERTIES where PROPERTY_NAME='NLS_CHARACTERSET';

PROPERTY_VALUE

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

UTF8

SQL> select * from longtest;

ID NAME                                                                                    ID2

---------- -------------------------------------------------------------------------------- ----------

1 aaabbbccc                                                                                 2

expdp HAOZHU_USER/HAOZHU_USER DIRECTORY=haozhu TABLES=longtest DUMPFILE=longtest.dmp LOGFILE=longtest.log

SQL> drop table longtest;

impdp HAOZHU_USER/HAOZHU_USER DIRECTORY=haozhu TABLES=longtest DUMPFILE=longtest.dmp

SQL> select * from longtest;

ID NAME                                                                                    ID2

---------- -------------------------------------------------------------------------------- ----------

1  a a a b b b c c c                                                                       2

6.Long raw does not have the BUG of above no.5

There are many tests on it and are all confirmed by myself to support no 6.

I think this is because Long raw is for binary contents and it is not related to the character set of database.

on UTF8 database(multi bytes)

create table longrawtest (id number,name long raw);

alter table longrawtest add id2 number;

insert into longrawtest values (1,'aaabbbccc',2);

commit;

SQL> select SEGMENT_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID,BLOCKS

2  from dba_extents where wner = 'HAOZHU_USER' and SEGMENT_NAME='LONGRAWTEST';

SEGMENT_NAME                   SEGMENT_TYPE          FILE_ID   BLOCK_ID     BLOCKS

------------------------------ ------------------ ---------- ---------- ----------

LONGRAWTEST                    TABLE                      10      11632          2

alter system dump datafile 10 block min 11632 block max 11633;

tab 0, row 0, @0x1f91

tl: 15 fb: --H-FL-- lb: 0x1  cc: 3

col  0: [ 2]  c1 02

col  1: [ 5]  0a aa bb bc cc

col  2: [ 2]  c1 03

end_of_block_dump

expdp HAOZHU_USER/HAOZHU_USER DIRECTORY=haozhu TABLES=longrawtest DUMPFILE=longrawtest.dmp LOGFILE=longrawtest.log

drop table longrawtest;

impdp HAOZHU_USER/HAOZHU_USER DIRECTORY=haozhu TABLES=longrawtest DUMPFILE=longrawtest.dmp

SQL> select SEGMENT_NAME,SEGMENT_TYPE,FILE_ID,BLOCK_ID,BLOCKS

2  from dba_extents where wner = 'HAOZHU_USER' and SEGMENT_NAME='LONGRAWTEST';

SEGMENT_NAME                   SEGMENT_TYPE          FILE_ID   BLOCK_ID     BLOCKS

------------------------------ ------------------ ---------- ---------- ----------

LONGRAWTEST                    TABLE                       1      25835          2

alter system dump datafile 1 block min 25835 block max 25836;

block_row_dump:

tab 0, row 0, @0x1f79

tl: 15 fb: --H-FL-- lb: 0x0  cc: 3

col  0: [ 2]  c1 02

col  1: [ 2]  c1 03

col  2: [ 5]  0a aa bb bc ccànot corrupted,but put at the last column

end_of_block_dump

Thanks,

Hao