1、long类型无法在where子句中使用
select * from all_views where test like '%HELLO%'--提示TEST标识符无效
--查找使用long型的表和字段
select table_name, column_name
from dba_tab_columns
where data_type in ('LONG', 'LONG RAW')
AND owner = 'SYS'
and table_name like 'DBA%';
2、long类型可以在where子句中使用的方法
2.1 将其转为varchar2类型
--text为long型，将其转为varchar2型
select *
from (select owner,
view_name,
long_help.substr_of('select text from dba_views where owner=:owner and view_name=:view_name',
1,
4000,
'owner',
owner,
'view_name',
view_name) substr_of_view_text
from dba_views
where owner = 'LTTFM')
where upper(substr_of_view_text) like '%G3E%';
--high_value为long型，将其转为varchar2型
select *
from (select table_owner,
table_name,
partition_name,
long_help.substr_of('select high_value from all_tab_partitions where table_owner=:o and table_name=:n and partition_name=:p',
1,
4000,
'o',
table_owner,
'n',
table_name,
'p',
partition_name) high_value
from all_tab_partitions
where table_name = 'T'
and table_owner = 'LTTFM')
where high_value like '%2003%';
2.2 使long型可以在where子句中使用
to_lob内置函数和一个全局性临时表，将long或long_raw临时转换为clob或blob
create table g_temp as select to_lob(text) a from dba_views where view_name='VC_COIL_T';
lob类型介绍
select dbms_metadata.get_ddl('TABLE','T') FROM dual;
dbms_metadata.get_ddl('TABLE','T') --查看表t中的lob字段具体内容
----------------------------------------------------------------------------
CREATE TABLE "LTTFM"."T"
( "ID" NUMBER(*,0),
"TXT" CLOB,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GCOMM" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GCOMM"
LOB ("TXT") STORE AS (
TABLESPACE "GCOMM" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))
lob属性
*一个表空间
*ENABLE STORAGE IN ROW
*CHUNK 8192
*PCTVERSION 10
*一个完整的STORAGE子句
lob存储的原理
select segment_name,segment_type from user_segments;
segment_name segment_type
------------------ ----------------
SYS_C0039321 INDEX
SYS_IL0000086681C00003$$ LOBINDEX
SYS_LOB0000097889C00003$$ LOBSEGMENT
T TABLE
index-支持主键约束；
LOBINDEX-用于执行lob的导航，找出其中的某些部分；
LOBSEGMENT-存储lob数据(lob数据也可能存储在T表中)
lob属性介绍
1、lob表空间
lob数据与t表存储的表空间分开的原因
1）如果表数据量大，每行有一个很大的lob，那么lob就会极为庞大，为lob数据单独使用一个表空间有利于备份和恢复以及空间管理；
2）另外，默认情况下，lob不在缓冲区中进行缓存，因此，对每个lob访问，不论是读还是写，都会带来一个物理I/O，所以在实际中有些对象会经历
更多的I/O，将这些对象分离到它们自己的磁盘上就很有意义。
备注：lobindex和lobsegment总是会在同一个表空间中。（oracle8i release3之前可以分开）
2、IN ROW子句
该子句控制了lob数据是否与表分开存储。
ENABLE STORAGE IN ROW（默认行为） —— 表示当lob<=4000字节，则不分开存储，若lob>4000字节则分开存储;
DISABLE STORAGE IN ROW
in row 子句的作用，用下面的例子来说明：
create table t (id int primary key,in_row clob,out_row clob)
lob (in_row) store as (enable storage in row) --lob数据存在行内
lob (out_row) store as (disable storage in row); --lob数据存在行外
--往t表插入数据
insert into t
select rownum,
owner || ' ' || object_name ||' ' || object_type || ' ' || status,
owner || ' ' || object_name ||' ' || object_type || ' ' || status
from all_objects;
alter session set sql_trace=true;
alter session set tracefile_identifier='look';
GRANT EXECUTE ON SYS.dbms_monitor TO lttfm;--dbms_monitor必须授权给lttfm用户才能使用
--取数据
declare
l_cnt number;
l_date varchar2(32765);
begin
select count(1) into l_cnt from t;
dbms_monitor.session_trace_enable;--开启跟踪
for i in 1 .. l_cnt loop
select in_row into l_date from t where id = i;
select out_row into l_date from t where id = i;
end loop;
end;
--查看TKPROF报告
SELECT IN_ROW FROM T WHERE ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 86146 2.56 2.52 0 0 0 0
Fetch 86146 3.03 3.51 0 258438 0 86146
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 172293 5.59 6.04 0 258438 0 86146
Rows Row Source Operation
------- ---------------------------------------------------
86146 TABLE ACCESS BY INDEX ROWID T (cr=258438 pr=0 pw=0 time=3358141 us)
86146 INDEX UNIQUE SCAN SYS_C0024616 (cr=172292 pr=0 pw=0 time=1434647 us)(object id 92266)
SELECT OUT_ROW FROM T WHERE ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 86146 2.35 2.38 0 0 0 0
Fetch 86146 13.93 66.53 86146 603025 0 86146
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 172293 16.29 68.92 86146 603025 0 86146
Rows Row Source Operation
------- ---------------------------------------------------
86146 TABLE ACCESS BY INDEX ROWID T (cr=258438 pr=0 pw=0 time=3338588 us)
86146 INDEX UNIQUE SCAN SYS_C0024616 (cr=172292 pr=0 pw=0 time=1421749 us)(object id 92266)
注：获取in_row列明显要快得多，而且所占的资源也远远少于out_row列。out_row列需要增加对lobindex段的I/O（为找到lob的各个部分）。
out_row列获取86146行会带来86146次物理I/O，会导致同样数目的“直接路径读写”I/O等待。因此，如果lob数据不是很大，采用lob数据缓存可以解决这个问题。
查看更新与插入这两个字段的区别
--更新或插入部分行
create sequence s start with 100000;
declare
l_cnt number;
l_date varchar2(32765);
begin
dbms_monitor.session_trace_enable;
for i in 1 .. 100 loop
update t
set in_row = to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss')
where id = i;
update t
set out_row = to_char(sysdate, 'dd-mm-yyyy hh24:mi:ss')
where id = i;
insert into t (id, in_row) values (s.nextval, 'hello world');
insert into t (id, out_row) values (s.nextval, 'hello world');
end loop;
end;
--查看tkprof
UPDATE T SET IN_ROW = TO_CHAR(SYSDATE, 'dd-mm-yyyy hh24:mi:ss') WHERE ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.04 0.02 0 200 208 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.04 0.02 0 200 208 100
Rows Row Source Operation
------- ---------------------------------------------------
100 UPDATE T (cr=200 pr=0 pw=0 time=14884 us)
100 INDEX UNIQUE SCAN SYS_C0024616 (cr=200 pr=0 pw=0 time=2210 us)(object id 92266)
UPDATE T SET OUT_ROW = TO_CHAR(SYSDATE, 'dd-mm-yyyy hh24:mi:ss') WHERE ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.09 0.24 0 1500 2231 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.09 0.24 0 1500 2231 100
Rows Row Source Operation
------- ---------------------------------------------------
100 UPDATE T (cr=1500 pr=0 pw=100 time=242598 us)
100 INDEX UNIQUE SCAN SYS_C0024616 (cr=200 pr=0 pw=0 time=2048 us)(object id 92266)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write 300 0.00 0.01
注：由以上的TKPROF可以看到，out_row占用了更多的资源（除了维护表本身还要维护lobindex和lobsegment）。insert操作也类似。
INSERT INTO T (ID, IN_ROW) VALUES (S.NEXTVAL, 'hello world')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.00 0.02 0 2 323 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.00 0.02 0 2 323 100
INSERT INTO T (ID, OUT_ROW) VALUES (S.NEXTVAL, 'hello world')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.09 0.11 0 1005 1527 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.09 0.11 0 1005 1527 100
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write 299 0.00 0.01
总结：如果使用一个lob，而且很多串都能在行内放下（即小于4000字节），那么默认的 enable storage in row 就是一个不错的想法。
3、chunk子句
lob数据存储在块中。chunk大小必须是oracle块大小的整数倍。如果lob大小为7kb，那么chunk设置为8kb最合适，如果设置为32kb，则会浪费约25kb
的空间。同时也要注意块不要太多，因为块越多，索引越大。
4、pctversion子句
用于控制lob的读一致性。lobsegment并不使用undo来记录修改；而是直接在lobsegment本身中维护信息的版本。lobindex会像其它段一样生成undo，
但是lobsegment不会。相反，修改一个lob时，oracle会分配一个新的chunk，并且仍保留原来的chunk。如果回滚了事务，对lob索引所做的修改会回滚
，索引将指向原来的chunk。undo维护会在lob段本身中执行。因此，oracle会使用lobsegment，并使用lobindex的读一致性视图来撤销对lob的修改。
--展示lob的读一致性
create table t (id int primary key,txt clob) lob(txt)store as (disable storage in row);
insert into t values (1,'hello world');commit;
--游标c的读一致映像来自undo段，lob的读一致映像来自lob段本身
declare
l_clob clob;
cursor c is
select id from t;
l_id number;
begin
select txt into l_clob from t;
open c;
update t set id = 2, txt = 'Goodbye';
commit;
dbms_output.put_line(dbms_lob.substr(l_clob, 100, 1));
fetch c
into l_id;
dbms_output.put_line('id = ' || l_id);
close c;
end;
输出结果：
hello world
id = 1
表t查询结果：
select * from t;
ID TXT
-- -----------
2 Goodbye
pctversion控制着用于实现LOB数据版本化的已分配LOB空间的百分比。对于许多使用情况来说，默认设置10%就够了，因为很多情况下，只要insert和获取LOB。但是，若是经常修改LOB，频繁读读LOB，与此同时另外某个会话正在修改这些LOB，10%可能就太小了。如果处理LOB时遇到一个ORA-22924错误，解决方案并不是增加undo表空间大小，也不是增加undo保留时间（undo_retention），如果使用手动undo管理，那么增加更多RBS空间也不能解决问题，而使用如下命令：alter table tablename modify lob （lobname）（pctversion n）;并增加lobsegment中为实现数据版本化所用的空间大小。
5、retention子句
retention子句与pctversion子句是互斥的，如果数据库使用自动undo管理，就可以使用这个子句。retention子句并非在lobsegment中保留某个百分比的空间
来实现lob的版本化，而是基于时间的机制来保留数据。数据库会设置参数undo_retention，指定要把undo信息保留多长时间来保证一致读。这种情况也适用于
lob数据。
注：不能使用该子句来指定保留时间，而要从数据库的undo_retention设置来继承它。
6、cache子句
cache/nocache/cache reads
这个子句控制了lobsegment数据是否存储在缓冲区缓存中。默认的nocache指示，每个访问都是从磁盘的一个直接读，同样，每个写都是对磁盘的
直接写。cache reads 允许缓存从磁盘读的lob数据，但是lob数据的写操作必须直接写至磁盘。cache则允许读和写时都能缓存lob数据。
默认设置为cache，如果lob数据量不大，对其缓存就没有什么意义。如果存在多个lob的加载，那么加载每一行时都必须等待这个I/O完成。所以这些
lob的缓存很合理。
alter table tablename modify lob(lobname)(cache);--打开缓存
alter table tablename modify lob(lobname)(nocache);--关闭缓存
对于一个很大的lob初始加载，启用lob的缓存很有意义。
注：应充分使用keep池或回收池。并非在默认缓存中将lobsegment数据域所有“常规”数据一同缓存，可以使用保持池或回收池将其分开缓存。采用
这种方式，既能缓存lob数据，且不影响系统中现有的数据缓存。
7、lob storage 子句
该子句也使用于lobsegment何lobindex。
BFILE类型介绍
使用bfile，还必须要使用一个oracle directory 对象。
--创建目录
create table t(id int primary key,os_file bfile);
create or replace directory my_dir as 'D:\oracle\my_dir';--创建目录(注意在本电脑上要先创建好my_dir目录)
insert into t values(1,bfilename('MY_DIR','FGISDBTBS.DBF'));
select dbms_lob.getlength(os_file) from t;--查看表中os_file字段的长度
注：默认的目录名都是大写，如果要用小写的就必须在创建时将其用引号括起来，如下：
create or replace directory "my_dir2" as 'D:\oracle';