直接创建一张带有LOB字段的表
SQL> create table TEST
(
N_MESSAGE_ID NUMBER(10,0) not null,
N_EVENT_ID NUMBER(10,0) not null,
C_EVENT_VALUE CLOB,
C_EVENT_TYPE VARCHAR2(50),
N_RECIPIENT_ID NUMBER(5,0) not null,
N_IS_RESENT NUMBER(1) default 0,
D_PROCESSING_END_DATE TIMESTAMP,
N_EJB_VERSION_ID NUMBER(10),
D_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
);
Table created.
查询创建后的表的LOB字段名称和LOB索引名称,发现数据库分配了SYS开头的名称
SQL> select owner,table_name,column_name,segment_name,tablespace_name,index_name from dba_lobs where table_name = 'TEST' and owner = 'SCOTT';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------ -------------------- -------------------- ------------------------- -------------------- ---------------------------
SCOTT TEST C_EVENT_VALUE SYS_LOB0000016805C00003$$ USER_OTA4G SYS_IL0000016805C00003$$
从数据库规范的角度出发,使用下面的语法进行表的创建
SQL> create table TEST
(
N_MESSAGE_ID NUMBER(10,0) not null,
N_EVENT_ID NUMBER(10,0) not null,
C_EVENT_VALUE CLOB,
C_EVENT_TYPE VARCHAR2(50),
N_RECIPIENT_ID NUMBER(5,0) not null,
N_IS_RESENT NUMBER(1) default 0,
D_PROCESSING_END_DATE TIMESTAMP,
N_EJB_VERSION_ID NUMBER(10),
D_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
)
tablespace "USERS"
lob
(C_EVENT_VALUE) --LOB字段名称
store as
LOB_SEGMENT_TEST --LOB段(LOB Segment)的名称,如果不指定则使用数据库分配的SYS_开头的名称
(tablespace "USERS" INDEX INDX_LOB_TEST (tablespace "USERS" )); --INDEX_LOB_TEST是LOB索引的名称,如果不指定则使用数据库分配的SYS_开头的名称,两个表空间分别是LOB段表空间和索引表空间的名称
Table created.
SQL> select owner,table_name,column_name,segment_name,tablespace_name,index_name from dba_lobs where table_name = 'TEST' and owner = 'SCOTT';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------ -------------------- -------------------- ------------------------- -------------------- ---------------------------
SCOTT TEST C_EVENT_VALUE LOB_SEGMENT_TEST USERS INDX_LOB_TEST
SQL> create table TEST
(
N_MESSAGE_ID NUMBER(10,0) not null,
N_EVENT_ID NUMBER(10,0) not null,
C_EVENT_VALUE CLOB,
C_EVENT_TYPE VARCHAR2(50),
N_RECIPIENT_ID NUMBER(5,0) not null,
N_IS_RESENT NUMBER(1) default 0,
D_PROCESSING_END_DATE TIMESTAMP,
N_EJB_VERSION_ID NUMBER(10),
D_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
);
Table created.
查询创建后的表的LOB字段名称和LOB索引名称,发现数据库分配了SYS开头的名称
SQL> select owner,table_name,column_name,segment_name,tablespace_name,index_name from dba_lobs where table_name = 'TEST' and owner = 'SCOTT';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------ -------------------- -------------------- ------------------------- -------------------- ---------------------------
SCOTT TEST C_EVENT_VALUE SYS_LOB0000016805C00003$$ USER_OTA4G SYS_IL0000016805C00003$$
从数据库规范的角度出发,使用下面的语法进行表的创建
SQL> create table TEST
(
N_MESSAGE_ID NUMBER(10,0) not null,
N_EVENT_ID NUMBER(10,0) not null,
C_EVENT_VALUE CLOB,
C_EVENT_TYPE VARCHAR2(50),
N_RECIPIENT_ID NUMBER(5,0) not null,
N_IS_RESENT NUMBER(1) default 0,
D_PROCESSING_END_DATE TIMESTAMP,
N_EJB_VERSION_ID NUMBER(10),
D_TIMESTAMP TIMESTAMP(9) WITH TIME ZONE
)
tablespace "USERS"
lob
(C_EVENT_VALUE) --LOB字段名称
store as
LOB_SEGMENT_TEST --LOB段(LOB Segment)的名称,如果不指定则使用数据库分配的SYS_开头的名称
(tablespace "USERS" INDEX INDX_LOB_TEST (tablespace "USERS" )); --INDEX_LOB_TEST是LOB索引的名称,如果不指定则使用数据库分配的SYS_开头的名称,两个表空间分别是LOB段表空间和索引表空间的名称
Table created.
SQL> select owner,table_name,column_name,segment_name,tablespace_name,index_name from dba_lobs where table_name = 'TEST' and owner = 'SCOTT';
OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME INDEX_NAME
------------ -------------------- -------------------- ------------------------- -------------------- ---------------------------
SCOTT TEST C_EVENT_VALUE LOB_SEGMENT_TEST USERS INDX_LOB_TEST
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26506993/viewspace-2134092/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26506993/viewspace-2134092/