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

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

Long raw和Long型別總結

相關文章