大数据类型的操作之CLOB

王朝other·作者佚名 2008-05-31

宽屏版字体: 小|中|大|超大

1. 如何创建带有CLOB类型字段的表?

CREATE TABLE TEST2 (AAA CLOB);

2. 如何对带有CLOB类型字段的表增加记录?

INSERT INTO TEST2 VALUES('CCBZZPCCBZZP');

3. 如何SELECT带有CLOB类型字段的表?

SELECT * FROM TEST2;SELECT * FROM TEST2 WHERE

DBMS_LOB.INSTR(TEST2.AAA,'B',1,1)>0;

4. 如何对带有CLOB类型字段的表更换表空间?

ALTER TABLE TEST2 MOVE TABLESPACE USERS;

5. 如何EXP带有CLOB类型字段的表?

EXP USER/PASSWord FILE=A.DMP TABLES=(TEST2);

6. 如何IMP带有CLOB类型字段的表?

IMP USER/PASSWORD FILE=A.DMP FULL=Y;

7. 从哪个版本开始支持CLOB,BLOB等大字段?

以上测试环境为Oracle92

SQL*Plus: Release 9.2.0.1.0 - ProdUCtion on 星期四 9月 4 12:02:00 2003

联机到:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

SQL> DROP TABLE TEST2;

DROP TABLE TEST2

ERROR 在行 1:

ORA-00942: 表格或视观表不存在

SQL> CREATE TABLE TEST2 (AAA CLOB); 已建立表格. SQL> INSERT INTO TEST2 VALUES('CCBZZPCCBZZP'); 已建立 1 个数据列. SQL> SELECT * FROM TEST2; AAA

---------------------------------------------------------------------- CCBZZPCCBZZP SQL> SELECT * FROM TEST2 WHERE DBMS_LOB.INSTR(TEST2.AAA,'B',1,1)>0; AAA

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

CCBZZPCCBZZP SQL> ALTER TABLE TEST2 MOVE TABLESPACE USERS; 已更改表格. SQL>

d:> exp test/test file=a.dmp tables=(test2) ...... Export done in ZHT16BIG5 character set and ZHT16BIG5 NCHAR character set About to export specified tables via Conventional Path ...

. . exporting table TEST2 0 rows exported

Export terminated successfully without warnings. SQL> drop table test2; 已删除表格. d:> imp test/test file=a.dmp tables=(test2); ......

import done in ZHT16BIG5 character set and ZHT16BIG5 NCHAR character set

. importing TEST's objects into TEST

. . importing table "TEST2" 0 rows imported

Import terminated successfully without warnings.