goldengate（4）--版本11.1.1 goldengate支持的数据类型

1.数值类型

number

binary float （单精度浮点）

binary double（双精度浮点）

示例：binary float,binary double,number

create table TEST.XY105
(
ID NUMBER not null,
NUMF BINARY_FLOAT,
NUMD BINARY_DOUBLE
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255;
alter table TEST.XY105
add constraint PK_ID_XY105 primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255;

1.1 .源库插入数据

insert into test.xy105
(id, numf, numd)
values
(1234567890.0987654321, 1234567890.0987654321, 1234567890.0987654321);

SQL> select * from test.xy105 ;

ID NUMF NUMD
---------- ---------- ----------
1234567890.09876 1234567940 1234567890.09876

1.2 .目标端也看到了数据

SQL> select * from test.xy105 ;

ID NUMF NUMD
---------- ---------- ----------
1234567890.09876 1234567940 1234567890.09876

2.字符类型

char

varchar2

long

nchar

nvarchar2（数据库字符集ZHS16GBK,一个英文字符占2个bytes,一个汉字占2个bytes;数据库字符集WE8ISO8859P1,一个英文字符占2个bytes,一个汉字占4个bytes）

示例：char,varchar2,long,nchar,nvarchar2

create table TEST.XY106
(
NAME CHAR(10) not null,
TNAME VARCHAR2(20),
DNAME NCHAR(20),
KNAME NVARCHAR2(20),
TEXT LONG
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST.XY106
add constraint PK_NAME_XY106 primary key (NAME)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);

2.1 源库插入数据

insert into test.xy106 values ('aa','bb','cc','dd','eeeeeeeeeeeeeeeeeeeeeee');

SQL> select * from test.xy106;

NAME TNAME DNAME KNAME TEXT
---------- -------------------- ---------------------------------------- ---------------------------------------- --------------------------------------------------------------------------------
aa bb cc dd eeeeeeeeeeeeeeeeeeeeeee

2.2目标端也看到数据了.

SQL> select * from test.xy106;

NAME TNAME DNAME KNAME TEXT
---------- -------------------- ---------------------------------------- ---------------------------------------- --------------------------------------------------------------------------------
aa bb cc dd eeeeeeeeeeeeeeeeeeeeeee

3.二进制数据类型

raw

long raw

示例：raw

create table TEST.XY107
(
ID      NUMBER not null,
PICTURE RAW(8)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TEST.XY107
add constraint PK_ID_XY107 primary key (ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
);

3.1 源端插入数据

insert into test.xy107 values(1,'aa')；

commit;

SQL> select * from test.xy107;

ID PICTURE
---------- ----------------
1 AA

insert into test.xy107 values(2,'E24');

commit;

SQL> /

ID PICTURE
---------- ----------------
1 AA
2 0E24

3.2目标端也可看到数据

SQL> select * from test.xy107;

ID PICTURE
---------- ----------------
1 AA

SQL> /

ID PICTURE
---------- ----------------
1 AA
2 0E24

使用utl_raw.cast_to_raw写入数据到表xy107

3.3源库插入数据

insert into test.xy107 values(3,utl_raw.cast_to_raw('This is a raw type xy107!'));

commit;

SQL> select * from test.xy107;

        ID PICTURE
---------- --------------------------------------------------------------------------------
         1 AA
         2 0E24
         3 546869732069732061207261772074797065207465737421

查看：

SQL> select utl_raw.cast_to_varchar2(picture) from test.xy107 where id=3 ;

UTL_RAW.CAST_TO_VARCHAR2(PICTU
--------------------------------------------------------------------------------
This is a raw type test!

3.4目标端查看数据看到了.

SQL> select * from test.xy107;

        ID PICTURE
---------- --------------------------------------------------------------------------------
         3 546869732069732061207261772074797065207465737421
        10 54686973206973206120726177207479706520787931303721
         1 AA
         2 0E24

查看：

SQL> select utl_raw.cast_to_varchar2(picture) from test.xy107 where id=3 ;

UTL_RAW.CAST_TO_VARCHAR2(PICTU
--------------------------------------------------------------------------------
This is a raw type test!

4.时间数据类型

date

timestamp

（略）

总结一下:

goldengate支持的数据类型：

Numeric data types
● NUMBER up to the maximum size permitted by Oracle
● BINARY FLOAT
● BINARY DOUBLE
Character data types
● CHAR
● VARCHAR2
● LONG
● NCHAR
● NVARCHAR2

Binary data types
● RAW
● LONG RAW
Date and timestamp data types
● DATE
● TIMESTAMP (see non-supported data types for exclusions)
Other supported data types
● ROWID
● VARRAY
● INTERVAL DAY and INTERVAL YEAR if the size of the target column is equal to, or greater than,that of the source.

XML data types
● XMLType for Oracle 9i and later

User defined types (objects)
GoldenGate supports user defined types (UDT) when the source and target objects havethe same structure. The schema names can be different.

Large object data types
● CLOB
● NCLOB
● BLOB

goledgate对大对象LOB字段的支持后续发布。

注意：goledgate不支持一下的数据类型.

Non-supported data types
● ANYDATA
● ANYDATASET
● ANYTYPE
● BFILE（把大对象存储在操作系统称为BFILE,OGG不支持.）
● BINARY_INTEGER
● MLSLABEL
● PLS_INTEGER
● TIMEZONE_ABBR
● TIMEZONE_REGION
● URITYPE
● UROWID

ITPub博客

goldengate（4）

最新文章