[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)