[Oracle]大数据类型的操作之CLOB

作者：CCBZZP 来源：IT168 添加时间：2006-5-26 9:53:56

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

　　Copyright ? 1982, 2002, Oracle Corporation. All rights reserved.

　　联机到:
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.

上一篇：[Oracle]一次数据库性能问题的tuning
下一篇：[Oracle]Oracle logminer使用方法总结

站内搜索