[Edited on 10 May 2023, in order to restore text formats altered by the migration to the new Forums software.]
I've recently discovered the following issue on 19c PDBs: attempting to retrieve the full text of the defining query of a view, using the TEXT column of the DBA_VIEWS / ALL_VIEWS / USER_VIEWS views, does not work for views in the SYS schema: the returned value is NULL if querying from within a PDB.
Tested on 19.9 and 19.14.
E.g., being connected as SYS from within a PDB:
select sys_context('USERENV', 'SESSION_USER') as username,
sys_context('USERENV', 'CON_NAME') as pdb_name,
sys_context('USERENV', 'CON_ID') as con_id
from dual;
USERNAME PDB_NAME CON_ID
-------- -------- ------
SYS PDB_RVA 3
select text
from user_views
where view_name = 'USER_VIEWS';
TEXT
-------------
(null)
Of course the correct non-null result is returned if the same query is run from the CDB$ROOT.
Is this a known bug?
I have searched on MOS for relevant articles, but in vain so far.
The plan of the above query—using "_px_cdb_view_enabled"=false to make things simpler—is as follows:
SQL_ID f07cq6cdkdw4r, child number 0
-------------------------------------
select text from user_views where view_name = 'USER_VIEWS'
Plan hash value: 1275650687
----------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | | | 1 |00:00:00.01 | 15 |
| 1 | PARTITION LIST ALL | | 1 | 2 | 270 | 2 (100)| 00:00:01 | 1 | 2 | 1 |00:00:00.01 | 15 |
|* 2 | EXTENDED DATA LINK FULL| INT$DBA_VIEWS | 2 | 2 | 270 | 2 (100)| 00:00:01 | | | 1 |00:00:00.01 | 15 |
----------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1
2 - SEL$F5BB74E1 / INT$DBA_VIEWS@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('19.1.0')
DB_VERSION('19.1.0')
OPT_PARAM('_optimizer_aggr_groupby_elim' 'false')
ALL_ROWS
OUTLINE_LEAF(@"SEL$F5BB74E1")
MERGE(@"SEL$2" >"SEL$1")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
FULL(@"SEL$F5BB74E1" "INT$DBA_VIEWS"@"SEL$2")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("VIEW_NAME"='USER_VIEWS' AND "OWNER"=SYS_CONTEXT('USERENV','CURRENT_USER')))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "TEXT"[LONG,352]
2 - "OWNER"[VARCHAR2,128], "VIEW_NAME"[VARCHAR2,128], "TEXT"[LONG,352]
Noticeable in this plan are the following two operations:
- Operation #1:
PARTITION LIST ALL, with Pstart=1 and Pstop=2
- And its child, operation #2:
EXTENDED DATA LINK FULL on INT$DBA_VIEWS.
If you look at the definition of INT$DBA_VIEWS (which is found in $ORACLE_HOME/rdbms/admin/cdcore_tabs.sql), you'll see that it's the multitenant-enabled equivalent of formerly the DBA_VIEWS view from pre-multitenant releases (e.g. 11.2); new columns have been added of course, and now there's the prominent SHARING=EXTENDED DATA clause, which makes it an extended data-linked object in the first place, if I understand correctly—but I'm miles away from a multitenant expert, so let's not get too deep, too fast into details here.
Operation #2, EXTENDED DATA LINK FULL, makes the actual operations against the INT$DBA_VIEWS view entirely opaque, as far as the cursor plan is concerned. All we know at this stage is that this operation is started twice, once for each partition: that should be one time inside the PDB, and another time in the root container.
We can see what's going on in clear light by tracing the query.
Here's the raw extended SQL trace—with some STAT rows removed for brevity; we're in for a little surprise.
*** CONTAINER ID:(3) 2022-10-26T23:30:19.698428+02:00
CLOSE #140583689916856:c=44,e=44,dep=0,type=1,tim=2072587797876
=====================
PARSING IN CURSOR #140583680301176 len=61 dep=0 uid=0 oct=3 lid=0 tim=2072587799192 hv=455536791 ad='62d33bf0' sqlid='f07cq6cdkdw4r'
select text
from user_views
where view_name = 'USER_VIEWS'
END OF STMT
PARSE #140583680301176:c=255,e=256,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1275650687,tim=2072587799191
=====================
PARSING IN CURSOR #140583689941680 len=78 dep=1 uid=0 oct=3 lid=0 tim=2072587799490 hv=4076050823 ad='81c5e8e0' sqlid='f37ffk7tg76c7'
select 'q''"' || SYS_CONTEXT('USERENV', 'CURRENT_USER') || '"''' from sys.dual
END OF STMT
PARSE #140583689941680:c=21,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1388734953,tim=2072587799489
EXEC #140583689941680:c=23,e=22,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1388734953,tim=2072587799633
FETCH #140583689941680:c=20,e=20,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1388734953,tim=2072587799698
STAT #140583689941680 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 str=1 time=2 us cost=2 size=0 card=1)'
CLOSE #140583689941680:c=0,e=8,dep=1,type=3,tim=2072587799912
EXEC #140583680301176:c=579,e=753,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1275650687,tim=2072587800078
*** 2022-10-26T23:30:19.701042+02:00 (CDB$ROOT(1))
=====================
PARSING IN CURSOR #140583689914984 len=304 dep=1 uid=0 oct=3 lid=0 tim=2072587801309 hv=563226370 ad='76780f68' sqlid='f4tu67sht49s2'
SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) OPT_PARAM('_ENABLE_VIEW_PDB', 'FALSE') */ "OWNER","VIEW_NAME",NULL FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS".SHARING=1 AND ("INT$DBA_VIEWS"."VIEW_NAME"='USER_VIEWS' AND "INT$DBA_VIEWS"."OWNER"=q'"SYS"')
END OF STMT
PARSE #140583689914984:c=60,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1665352184,tim=2072587801308
EXEC #140583689914984:c=75,e=75,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1665352184,tim=2072587801822
FETCH #140583689914984:c=119,e=119,p=0,cr=8,cu=0,mis=0,r=1,dep=1,og=1,plh=1665352184,tim=2072587802003
STAT #140583689914984 id=1 cnt=1 pid=0 pos=1 obj=0 op='RESULT CACHE gsu3v2ytu6bm50tpb1kvx296tr (cr=8 pr=0 pw=0 str=1 time=115 us)'
STAT #140583689914984 id=2 cnt=1 pid=1 pos=1 obj=0 op='FILTER (cr=8 pr=0 pw=0 str=1 time=91 us)'
STAT #140583689914984 id=3 cnt=1 pid=2 pos=1 obj=0 op='HASH JOIN (cr=8 pr=0 pw=0 str=1 time=87 us cost=4 size=90 card=1)'
STAT #140583689914984 id=4 cnt=1 pid=3 pos=1 obj=0 op='NESTED LOOPS (cr=8 pr=0 pw=0 str=1 time=79 us cost=4 size=90 card=1)'
STAT #140583689914984 id=5 cnt=1 pid=4 pos=1 obj=0 op='STATISTICS COLLECTOR (cr=7 pr=0 pw=0 str=1 time=70 us)'
STAT #140583689914984 id=6 cnt=1 pid=5 pos=1 obj=0 op='NESTED LOOPS (cr=7 pr=0 pw=0 str=1 time=66 us cost=3 size=65 card=1)'
...
CLOSE #140583689914984:c=1004,e=2179,dep=1,type=1,tim=2072587804284
*** 2022-10-26T23:30:19.704391+02:00 (PDB_RVA(3))
PARSE #140583689941680:c=31,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1388734953,tim=2072587804658
EXEC #140583689941680:c=31,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1388734953,tim=2072587804793
FETCH #140583689941680:c=38,e=37,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1388734953,tim=2072587804874
STAT #140583689941680 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 str=1 time=2 us cost=2 size=0 card=1)'
CLOSE #140583689941680:c=5,e=5,dep=1,type=3,tim=2072587805012
=====================
PARSING IN CURSOR #140583689811968 len=274 dep=1 uid=0 oct=3 lid=0 tim=2072587805166 hv=1979357833 ad='7677e3e0' sqlid='7y18k89uzp6n9'
SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) OPT_PARAM('_ENABLE_VIEW_PDB', 'FALSE') */ "OWNER","VIEW_NAME","TEXT" FROM NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS" WHERE "INT$DBA_VIEWS"."VIEW_NAME"='USER_VIEWS' AND "INT$DBA_VIEWS"."OWNER"=q'"SYS"'
END OF STMT
PARSE #140583689811968:c=35,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2618961787,tim=2072587805166
EXEC #140583689811968:c=85,e=85,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2618961787,tim=2072587805461
FETCH #140583689811968:c=151,e=151,p=0,cr=7,cu=0,mis=0,r=0,dep=1,og=1,plh=2618961787,tim=2072587805847
STAT #140583689811968 id=1 cnt=0 pid=0 pos=1 obj=0 op='RESULT CACHE 37rw6a2wznnvufy4wpc0hac3w4 (cr=7 pr=0 pw=0 str=1 time=148 us)'
STAT #140583689811968 id=2 cnt=0 pid=1 pos=1 obj=0 op='FILTER (cr=7 pr=0 pw=0 str=1 time=122 us)'
STAT #140583689811968 id=3 cnt=0 pid=2 pos=1 obj=0 op='HASH JOIN (cr=7 pr=0 pw=0 str=1 time=121 us cost=5 size=92 card=1)'
STAT #140583689811968 id=4 cnt=0 pid=3 pos=1 obj=0 op='NESTED LOOPS (cr=7 pr=0 pw=0 str=1 time=112 us cost=5 size=92 card=1)'
STAT #140583689811968 id=5 cnt=0 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=7 pr=0 pw=0 str=1 time=109 us cost=5 size=92 card=1)'
...
CLOSE #140583689811968:c=2398,e=2823,dep=1,type=1,tim=2072587808795
FETCH #140583680301176:c=7184,e=7687,p=0,cr=15,cu=0,mis=0,r=1,dep=0,og=1,plh=1275650687,tim=2072587808872
STAT #140583680301176 id=1 cnt=1 pid=0 pos=1 obj=0 op='PARTITION LIST ALL PARTITION: 1 2 (cr=15 pr=0 pw=0 str=1 time=8355 us cost=2 size=270 card=2)'
STAT #140583680301176 id=2 cnt=1 pid=1 pos=1 obj=0 op='EXTENDED DATA LINK FULL INT$DBA_VIEWS (cr=15 pr=0 pw=0 str=2 time=8324 us cost=2 size=270 card=2)'
CLOSE #140583680301176:c=13,e=14,dep=0,type=0,tim=2072587896398
So we can see that actually 3 recursive queries were run:
- One trivial query, which appears to have been run twice:
select 'q''"' || SYS_CONTEXT('USERENV', 'CURRENT_USER') || '"''' from sys.dual
- And two distinct queries against INT$DBA_VIEWS, the first of which was run in the CDB$ROOT container, and reads as follows (edited):
select /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) OPT_PARAM('_ENABLE_VIEW_PDB', 'FALSE') */
"OWNER",
"VIEW_NAME",
null
from NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS"
where "INT$DBA_VIEWS".SHARING = 1
and ("INT$DBA_VIEWS"."VIEW_NAME" = 'USER_VIEWS'
and "INT$DBA_VIEWS"."OWNER" = q'"SYS"')
The second query, run from within the PDB, reads as follows:
select /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) OPT_PARAM('_ENABLE_VIEW_PDB', 'FALSE') */
"OWNER",
"VIEW_NAME",
"TEXT"
from NO_CROSS_CONTAINER("SYS"."INT$DBA_VIEWS") "INT$DBA_VIEWS"
where "INT$DBA_VIEWS"."VIEW_NAME" = 'USER_VIEWS'
and "INT$DBA_VIEWS"."OWNER" = q'"SYS"'
Of course, in this particular case, because USER_VIEWS is a common view from the SYS schema, expectedly its metadata will be found in the root container; so it was expected that the first query returned exactly 1 row, whereas the second query did not return any row.
But the surprise—and that's very unfortunate—is that in the first query, the TEXT column that we wanted to retrieve in the first place appears to have been changed into a literal NULL value, and of course that all we get as a result. :-(
It's also worth noting that the TEXT column is of type LONG, and this might have something to do with it. Looking for similar cases, I found one with the USER_CONSTRAINTS view and the SEARCH_CONDITION column—but there wasn't any nasty consequence in that case, as the returned data came from the "right" query, the one against the PDB "partition".
Bottom line: apparently EXTENDED DATA LINK FULL works by making recursive queries on relevant containers (in this case, the root container and the PDB), and somehow it may incorrectly turn columns of data type LONG into NULL values when querying from the root container. This may prevent from retrieving the TEXT column of USER_VIEWS / ALL_VIEWS / DBA_VIEWS, in the case of views in the SYS schema which metadata is stored in the root container.
(And this is causing me an extended-data-link-full frustration , because I like to retrieve the defining queries of views—including SYS views—directly from within a PDB, without having to switch to the root container...)
Regards,