[20250804]关于oracle getlong函数.txt

来源：这里教程网时间：2026-03-03 22:36:31 作者：

[20250804]关于oracle getlong函数.txt --//偶尔检查系统视图dba_views时发现一个getlong函数，是用于取出表中字段的long类型的数据转换为varchar2类型，测试时遇到一些 --//问题，自己无法解析做一个记录。 1.环境： SCOTT@book01p> @ ver2 ============================== PORT_STRING : x86_64/Linux 2.4.xx VERSION : 21.0.0.0.0 BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production Version 21.3.0.0.0 BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production CON_ID : 0 PL/SQL procedure successfully completed. 2.分析： SYS@book> @ v2 dba_views Show SQL text of views matching "dba_views"... V_OWNER VIEW_NAME TEXT -------- ---------- ----------------------------------------------------------------- SYS DBA_VIEWS select OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME, EDITIONING_VIEW, READ_ONLY, CONTAINER_DATA, BEQUEATH, ORIGIN_CON_ID, DEFAULT_COLLATION, CONTAINERS_DEFAULT, CONTAINER_MAP, EXTENDED_DATA_LINK, EXTENDED_DATA_LINK_MAP, HAS_SENSITIVE_COLUMN, ADMIT_NULL, PDB_LOCAL_ONLY from int$dba_views no rows selected SYS@book> @ v2 int$dba_views Show SQL text of views matching "int$dba_views"... V_OWNER VIEW_NAME TEXT -------- ------------- -------------------------------------------------------------------------------------- SYS INT$DBA_VIEWS select u.name, u.user#, o.name, o.obj#, o.type#, v.textlength, v.text, getlong(1, v.rowid), t.typetextlength, t.typetext, t.oidtextlength, t.oidtext, t.typeowner, t.typename, decode(bitand(v.property, 134217728), 134217728, (select sv.name from superobj$ h, sys."_CURRENT_EDITION_OBJ" sv where h.subobj# = o.obj# and h.superobj# = sv.obj#), null), decode(bitand(v.property, 32), 32, 'Y', 'N'), decode(bitand(v.property, 16384), 16384, 'Y', 'N'), decode(bitand(v.property/4294967296, 134217728), 134217728, 'Y', 'N'), decode(bitand(o.flags,8),8,'CURRENT_USER','DEFINER'), case when bitand(o.flags, (65536+131072+4294967296))>0 then 1 else 0 end, to_number(sys_context('USERENV', 'CON_ID')), nls_collation_name(nvl(o.dflcollid, 16382)), -- CONTAINERS_DEFAULT decode(bitand(v.property, power(2,72)), power(2,72), 'YES', 'NO'), -- CONTAINER_MAP decode(bitand(v.property, power(2,80)), power(2,80), 'YES', 'NO'), -- EXTENDED_DATA_LINK decode(bitand(v.property, power(2,52)), power(2,52), 'YES', 'NO'), -- EXTENDED_DATA_LINK_MAP decode(bitand(v.property, power(2,79)), power(2,79), 'YES', 'NO'), -- HAS_SENSITIVE_COLUMN decode(bitand(v.property, power(2,89)), power(2,89), 'YES', 'NO'), -- ADMIT_NULL decode(bitand(v.property, power(2,78)), power(2,78), 'YES', 'NO'), -- PDB_LOCAL_ONLY decode(bitand(v.property, power(2,30)), power(2,30), 'YES', 'NO') from sys."_CURRENT_EDITION_OBJ" o, sys.view$ v, sys.user$ u, sys.typed_view$ t where o.obj# = v.obj# and o.obj# = t.obj#(+) and o.owner# = u.user# no rows selected --//发现getlong(1, v.rowid),v表示表别名sys.view$。对应dba_views的text_vc字段。 --//前面已经分析过1个表仅仅有1个long类型字段，getlong函数仅仅返回varchar2(4000),也就是前面4000字节。前面的1不知道表示什 --//么。 SYS@book> @ desc sys.getlong FUNCTION sys.getlong RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OPCODE NUMBER IN P_ROWID ROWID IN --//第一个参数OPCODE不知道表示什么参数。第2个参数表示rowid很容易理解，而且一个表仅仅1个long类型字段，找到rowid，其他相关 --//信息很容易定位。 3.首先在普通用户表建立long类型看看： SCOTT@book01p> create table tt (id number , la long,lb long , vc varchar2(32)); create table tt (id number , la long,lb long , vc varchar2(32)) * ERROR at line 1: ORA-01754: a table may contain only one column of type LONG --//一个表仅仅包含1个long类型。 SCOTT@book01p> create table tt (id number , la long, vc varchar2(32)); Table created. SCOTT@book01p> insert into tt values (1,'1测试2','abcdef'); 1 row created. SCOTT@book01p> insert into tt values (2,'a中文b','ABCDEF'); 1 row created. SCOTT@book01p> commit; Commit complete. SCOTT@book01p> select rowid, id,la c20 ,vc from scott.tt ; ROWID ID C20 VC ------------------ ---------- -------------------- -------------------------------- AAAj+9AASAAAAnlAAA 1 1测试2 abcdef AAAj+9AASAAAAnlAAB 2 a中文b ABCDEF SYS@book01p> GRANT EXECUTE ON SYS.getlong TO SCOTT; Grant succeeded. SCOTT@book01p> select sys.getlong(1,tt.rowid),tt.* from tt; ERROR: ORA-01410: invalid ROWID ORA-06512: at "SYS.CDBVIEW_INTERNAL", line 236 ORA-06512: at "SYS.CDBVIEW", line 60 ORA-06512: at "SYS.GETLONG", line 5 no rows selected --//报错，ORA-01410: invalid ROWID SCOTT@book01p> @ oerrz ora-01410 01410, 00000, "invalid ROWID" // *Cause: // *Action: --//第1个参数换其他数字参数看看。 SCOTT@book01p> select sys.getlong(3,tt.rowid) c10,tt.* from tt; C10 ID LA VC ---------- ---------- ------------------------------ -------------------------------- 1 1测试2 abcdef 2 a中文b ABCDEF --//大于等于3都不报错，但是返回NULL值。这是遇到的第1个问题。等于0，-1，-2都不报错。 --//仅仅等于1，2时报ORA-01410: invalid ROWID错误。 4.看看第2个问题： SCOTT@book01p> create view v_emp as select * from emp; View created. --//修改v2.sql脚本加入text_vc字段。 $ cat tpt/v2.sql -- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. col view_name for a30 col text for a100 word_wrap col text_vc for a100 word_wrap col v_owner for a25 prompt Show SQL text of views matching "&1"... select owner v_owner, view_name, text,text_vc from dba_views where upper(view_name) LIKE upper(CASE WHEN INSTR('&1','.') > 0 THEN SUBSTR('&1',INSTR('&1','.')+1) ELSE '&1' END ) ESCAPE '\' AND owner LIKE CASE WHEN INSTR('&1','.') > 0 THEN UPPER(SUBSTR('&1',1,INSTR('&1','.')-1)) ELSE user END ESCAPE '\' / select view_name, view_definition text from v$fixed_View_definition where upper(view_name) like upper('&1'); SYS@book01p> @ v2 scott.v_emp Show SQL text of views matching "scott.v_emp"... V_OWNER VIEW_NAME TEXT TEXT_VC -------- ---------- ---------------------------------------------------------------------------- ----------------------------------------------------------------------------- SCOTT V_EMP select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp select "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" from emp no rows selected --//text与text_vc都有显示。 --//但是在pdbs下如果查询系统dba_XXXX视图，text的返回是NULL。 SYS@book01p> @ v2 dba_views Show SQL text of views matching "dba_views"... V_OWNER VIEW_NAME TEXT TEXT_VC -------- --------- ------------------------- ----------------------------------------------------------------- SYS DBA_VIEWS select OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME, EDITIONING_VIEW, READ_ONLY, CONTAINER_DATA, BEQUEATH, ORIGIN_CON_ID, DEFAULT_COLLATION, CONTAINERS_DEFAULT, CONTAINER_MAP, EXTENDED_DATA_LINK, EXTENDED_DATA_LINK_MAP, HAS_SENSITIVE_COLUMN, ADMIT_NULL, PDB_LOCAL_ONLY from int$dba_views no rows selected --//而前面的使用v2.sql脚本查询，在cdb下查询text是信息显示的，看前面开始分析的执行。 --//实际上使用v2.sql脚本查询dba_views视图时，查询的信息来源cdb的，看执行计划很容易理解。但是一旦在pdb下查询dba_XXX视图， --//text的显示为NULL，而奇怪的是text_vc有显示。 --//通过getlong(1, v.rowid)的有显示，而v.text的反而没有显示，不理解。视乎跨越pdb后long字段信息无法显示。 --//注：我回头看了11g dba_views的定义，发现11g的定义没有text_vc字段。说明oracle视乎很早就发现这个情况。 --//增加了text_vc字段。 --//这是遇到的第2个问题。 5.继续分析，看看10046跟踪的情况： SYS@book01p> @ 10046on 12 Session altered. SYS@book01p> Select text,text_vc from dba_views where view_name='DBA_VIEWS'; TEXT TEXT_VC --------------------------------------- ------------------------------------------------------------------- select OWNER, VIEW_NAME, TEXT_LENGTH, TEXT, TEXT_VC, TYPE_TEXT_LENGTH, TYPE_TEXT, OID_TEXT_LENGTH, OID_TEXT, VIEW_TYPE_OWNER, VIEW_TYPE, SUPERVIEW_NAME, EDITIONING_VIEW, READ_ONLY, CONTAINER_DATA, BEQUEATH, ORIGIN_CON_ID, DEFAULT_COLLATION, CONTAINERS_DEFAULT, CONTAINER_MAP, EXTENDED_DATA_LINK, EXTENDED_DATA_LINK_MAP, HAS_SENSITIVE_COLUMN, ADMIT_NULL, PDB_LOCAL_ONLY from int$dba_views SYS@book01p> @ 10046off Session altered. --//抽取执行的sql语句如下： $ /home/oracle/sqllaji/bin/extractsql.sh /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4388.trc select rowcnt from sys.cdbvw_stats$ where objname = :1 SELECT count(*) FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS"."VIEW_NAME"='DBA_VIEWS' SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") SELECT count(*) FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS"."VIEW_NAME"='DBA_VIEWS' SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") Select text,text_vc from dba_views where view_name='DBA_VIEWS' alter session set events '10046 trace name context off' --//跟踪文件内容如下： ===================== PARSING IN CURSOR #139867038360544 len=54 dep=1 uid=0 oct=3 lid=0 tim=6564967337 hv=821795332 ad='6c9d61d0' sqlid='7z7n648sgr6h4' select rowcnt from sys.cdbvw_stats$ where objname = :1 END OF STMT PARSE #139867038360544:c=302,e=303,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=6564967331 BINDS #139867038360544: Bind#0 oacdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00 oacflg=10 fl2=0001 frm=01 csi=852 siz=32 off=0 kxsbbbfp=7f355507a538 bln=32 avl=13 flg=05 value="INT$DBA_VIEWS" EXEC #139867038360544:c=1297,e=1410,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=2518224374,tim=6564968827 FETCH #139867038360544:c=9,e=10,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=4,plh=2518224374,tim=6564968873 STAT #139867038360544 id=1 cnt=0 pid=0 pos=1 obj=187 op='TABLE ACCESS BY INDEX ROWID CDBVW_STATS$ (cr=1 pr=0 pw=0 str=1 time=11 us cost=0 size=79 card=1)' STAT #139867038360544 id=2 cnt=0 pid=1 pos=1 obj=188 op='INDEX UNIQUE SCAN I_CDBVW_STATS$_OBJNAME (cr=1 pr=0 pw=0 str=1 time=10 us cost=0 size=0 card=1)' CLOSE #139867038360544:c=3,e=3,dep=1,type=0,tim=6564969012 ===================== PARSING IN CURSOR #139867029578192 len=124 dep=1 uid=0 oct=3 lid=0 tim=6564992279 hv=1700977282 ad='72569668' sqlid='771p8qtkq5qn2' SELECT count(*) FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS"."VIEW_NAME"='DBA_VIEWS' END OF STMT PARSE #139867029578192:c=22583,e=22669,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=1,plh=937114868,tim=6564992278 CLOSE #139867029578192:c=3,e=4,dep=1,type=0,tim=6564992377 ===================== PARSING IN CURSOR #139867029578192 len=101 dep=1 uid=0 oct=3 lid=0 tim=6565029482 hv=3671610882 ad='63351528' sqlid='0hmxb5bddhph2' SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") END OF STMT PARSE #139867029578192:c=35865,e=36870,p=0,cr=12,cu=0,mis=1,r=0,dep=1,og=1,plh=1671069832,tim=6565029482 CLOSE #139867029578192:c=28,e=27,dep=1,type=0,tim=6565029646 ===================== PARSING IN CURSOR #139867029381920 len=124 dep=1 uid=0 oct=3 lid=0 tim=6565029852 hv=1700977282 ad='72569668' sqlid='771p8qtkq5qn2' SELECT count(*) FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS"."VIEW_NAME"='DBA_VIEWS' END OF STMT PARSE #139867029381920:c=17,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=937114868,tim=6565029852 CLOSE #139867029381920:c=2,e=2,dep=1,type=0,tim=6565029910 ===================== PARSING IN CURSOR #139867029381920 len=101 dep=1 uid=0 oct=3 lid=0 tim=6565029964 hv=3671610882 ad='63351528' sqlid='0hmxb5bddhph2' SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") END OF STMT PARSE #139867029381920:c=16,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1671069832,tim=6565029963 CLOSE #139867029381920:c=1,e=1,dep=1,type=0,tim=6565030015 ===================== PARSING IN CURSOR #139867039493536 len=101 dep=1 uid=0 oct=3 lid=0 tim=6565030390 hv=3671610882 ad='63351528' sqlid='0hmxb5bddhph2' SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") END OF STMT PARSE #139867039493536:c=23,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1671069832,tim=6565030390 CLOSE #139867039493536:c=13,e=14,dep=1,type=1,tim=6565030463 ===================== PARSING IN CURSOR #139867029854856 len=101 dep=1 uid=0 oct=3 lid=0 tim=6565030772 hv=3671610882 ad='63351528' sqlid='0hmxb5bddhph2' SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") END OF STMT PARSE #139867029854856:c=27,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1671069832,tim=6565030771 CLOSE #139867029854856:c=13,e=14,dep=1,type=1,tim=6565030846 ===================== PARSING IN CURSOR #139867039515736 len=62 dep=0 uid=0 oct=3 lid=0 tim=6565031055 hv=1170073132 ad='70a58918' sqlid='aqhmf1p2vvtjc' Select text,text_vc from dba_views where view_name='DBA_VIEWS' END OF STMT PARSE #139867039515736:c=65814,e=65951,p=0,cr=27,cu=0,mis=1,r=0,dep=0,og=1,plh=2689020808,tim=6565031054 WAIT #139867039515736: nam='PX Deq: Join ACK' ela= 1200 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565032463 WAIT #139867039515736: nam='PX Deq: Join ACK' ela= 377 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565032906 WAIT #139867039515736: nam='PX Deq: Parse Reply' ela= 6333 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565039367 WAIT #139867039515736: nam='PX Deq: Parse Reply' ela= 3103 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565042583 EXEC #139867039515736:c=589,e=11712,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2689020808,tim=6565042821 WAIT #139867039515736: nam='Disk file operations I/O' ela= 23 FileOperation=8 fileno=0 filetype=8 obj#=795 tim=6565042890 WAIT #139867039515736: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=795 tim=6565043067 WAIT #139867039515736: nam='SQL*Net message from client' ela= 699 driver id=1413697536 #bytes=1 p3=0 obj#=795 tim=6565043808 WAIT #139867039515736: nam='PX Deq: Execute Reply' ela= 888 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565044746 WAIT #139867039515736: nam='PX Deq: Execute Reply' ela= 158 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565044971 WAIT #139867039515736: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=795 tim=6565045005 FETCH #139867039515736:c=159,e=1172,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2689020808,tim=6565045022 WAIT #139867039515736: nam='SQL*Net message from client' ela= 422 driver id=1413697536 #bytes=1 p3=0 obj#=795 tim=6565045474 WAIT #139867039515736: nam='PX Deq: Execute Reply' ela= 16531 sleeptime/senderid=2 passes=2 p3=0 obj#=795 tim=6565062044 WAIT #139867039515736: nam='PX Deq: Execute Reply' ela= 99 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565062211 WAIT #139867039515736: nam='PX Deq: Signal ACK EXT' ela= 389 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565062663 WAIT #139867039515736: nam='PX Deq: Signal ACK EXT' ela= 2 sleeptime/senderid=0 passes=0 p3=0 obj#=795 tim=6565062707 WAIT #139867039515736: nam='PX Deq: Slave Session Stats' ela= 99 sleeptime/senderid=1 passes=1 p3=0 obj#=795 tim=6565062888 WAIT #139867039515736: nam='PX Deq: Slave Session Stats' ela= 1 sleeptime/senderid=0 passes=0 p3=0 obj#=795 tim=6565062917 WAIT #139867039515736: nam='enq: PS - contention' ela= 334 name|mode=1347616774 instance=1 slave ID=1 obj#=795 tim=6565063275 WAIT #139867039515736: nam='enq: PS - contention' ela= 117 name|mode=1347616774 instance=1 slave ID=0 obj#=795 tim=6565063434 FETCH #139867039515736:c=585,e=17974,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2689020808,tim=6565063483 STAT #139867039515736 id=1 cnt=1 pid=0 pos=1 obj=0 op='PX COORDINATOR (cr=0 pr=0 pw=0 str=1 time=12836 us)' STAT #139867039515736 id=2 cnt=0 pid=1 pos=1 obj=0 op='PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 str=0 time=0 us cost=1 size=414200 card=200)' STAT #139867039515736 id=3 cnt=0 pid=2 pos=1 obj=0 op='PX PARTITION LIST ALL PARTITION: 1 2 (cr=0 pr=0 pw=0 str=0 time=0 us cost=1 size=414200 card=200)' STAT #139867039515736 id=4 cnt=0 pid=3 pos=1 obj=0 op='EXTENDED DATA LINK FULL INT$DBA_VIEWS (cr=0 pr=0 pw=0 str=0 time=0 us cost=1 size=414200 card=200)' WAIT #139867039515736: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=795 tim=6565063657 --//自己手工执行看看： SYS@book01p> select rowcnt from sys.cdbvw_stats$ where objname = 'INT$DBA_VIEWS'; no rows selected SYS@book01p> select rowcnt from sys.cdbvw_stats$ ; no rows selected SYS@book01p> SELECT count(*) FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS"."VIEW_NAME"='DBA_VIEWS'; COUNT(*) ---------- 0 SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") --//返回43行。这个查询pdb里面的数据。 --//实际上中间这些sql语句根本没有执行： SELECT count(*) FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS"."VIEW_NAME"='DBA_VIEWS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.02 0.02 0 8 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.02 0.02 0 8 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) ******************************************************************************** SQL ID: 0hmxb5bddhph2 Plan Hash: 0 SELECT /*CROSSCON*/ /* */ /*"INT$DBA_VIEWS"*/ /* NONE */ * FROM NO_COMMON_DATA("SYS"."INT$DBA_VIEWS") call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 4 0.03 0.03 0 12 0 0 Execute 0 0.00 0.00 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.03 0.03 0 12 0 0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS (recursive depth: 1) ******************************************************************************** SQL ID: aqhmf1p2vvtjc Plan Hash: 2689020808 Select text,text_vc from dba_views where view_name='DBA_VIEWS' call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.06 0.06 0 27 0 0 Execute 1 0.00 0.01 0 0 0 0 Fetch 2 0.00 0.01 0 0 0 1 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.06 0.09 0 27 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Number of plan statistics captured: 1 Rows (1st) Rows (avg) Rows (max) Row Source Operation ---------- ---------- ---------- --------------------------------------------------- 1 1 1 PX COORDINATOR (cr=0 pr=0 pw=0 time=12836 us starts=1) 0 0 0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=414200 card=200) 0 0 0 PX PARTITION LIST ALL PARTITION: 1 2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=414200 card=200) 0 0 0 EXTENDED DATA LINK FULL INT$DBA_VIEWS (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=414200 card=200) --//我查询相关网站，基本无法查询到getlong函数的简单介绍。不过找到一个链接说明long类型： https://www.oracle-developer.net/display.php?id=430 --//working with long columns --//摘要如下： The use of LONG values is subject to these restrictions: A table can contain only one LONG column. You cannot create an object type with a LONG attribute. LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULL constraints). LONG columns cannot be indexed. LONG data cannot be specified in regular expressions. A stored function cannot return a LONG value. You can declare a variable or argument of a PL/SQL program unit using the LONG data type. However, you cannot then call the program unit from SQL. Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column. In addition, LONG columns cannot appear in these parts of SQL statements: GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements The UNIQUE operator of a SELECT statement The column list of a CREATE CLUSTER statement The CLUSTER clause of a CREATE MATERIALIZED VIEW statement SQL built-in functions, expressions, or conditions SELECT lists of queries containing GROUP BY clauses SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators SELECT lists of CREATE TABLE ... AS SELECT statements ALTER TABLE ... MOVE statements SELECT lists in subqueries in INSERT statements --//视乎第2个问题可以这样解析在pdb下查询一部分信息来自本地，另外一部分来自cdb。 @ v2 scott.v_emp --//相当于查询信息来自本地信息，text有信息显示。 @ v2 dba_views --//相当于查询信息来自cdb，text无信息显示。理论讲类似执行如下： SCOTT@book01p> select * from tt union select * from tt; select * from tt union select * from tt * ERROR at line 1: ORA-00997: illegal use of LONG datatype --//另外看了链接介绍oracle可以使用ctas加to_lob 方式建立新表转换long类型为lob类型。但是单独的执行select报错 SCOTT@book01p> select id,to_lob(la),vc from tt ; select id,to_lob(la),vc from tt * ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got LONG SCOTT@book01p> create table tx as select id,la ,vc from tt ; create table tz as select id,la ,vc from tt * ERROR at line 1: ORA-00997: illegal use of LONG datatype SCOTT@book01p> create table tx as select id,to_lob(la) la ,vc from tt ; Table created. SCOTT@book01p> @desc tx Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NUMBER 2 LA CLOB 3 VC VARCHAR2(32) SCOTT@book01p> select * from tx ; ID LA VC ---------- -------------------- -------------------------------- 1 1测试2 abcdef 2 a中文b ABCDEF --//另外的方式就是使用sqlplus的copy命令。 SCOTT@book01p> help copy COPY ---- Copies data from a query to a table in the same or another database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2. COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query where database has the following syntax: username[/password]@connect_identifier SCOTT@book01p> copy to scott/book@book01p create ty using select * from tt; Array fetch/bind size is 100. (arraysize is 100) Will commit when done. (copycommit is 0) Maximum long size is 1000000. (long is 1000000) Table TY created. 2 rows selected from DEFAULT HOST connection. 2 rows inserted into TY. 2 rows committed into TY at scott@book01p. SCOTT@book01p> @desc ty Name Null? Type ------------------------------- -------- ---------------------------- 1 ID NUMBER(38) 2 LA LONG 3 VC VARCHAR2(32)

编辑推荐：

返回列表

[20250804]关于oracle getlong函数.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推