. I create two users: USER1 and USER2<\/p>\n USER1 owns a table which has a constraint:<\/p>\n USER2 can access to the table either by prefixing it with USER1 or by setting the current_schema to USER1<\/p>\n Ok, now imagine you want to read constraint metadata for the current schema you have set:<\/p>\n No rows selected is a wrong result here because my current_schema is USER1 and USER1 has constraints:<\/p>\n So, where’s the problem? Let’s have a look at the execution plan:<\/p>\n I am in 12.2 and DBA_CONSTRAINTS reads from INT$DBA_CONSTRAINTS which reads from INT$INT$DBA_CONSTRAINTS and in multitenant this view being an extended data view will read from CDB$ROOT and from the current container. This is why we see EXTENDED DATA LINK FULL in the execution plan and up to this point the predicates are correct: “OWNER”=SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)<\/p>\n The execution through data link is run on each container with parallel processes: they switch to the container and run the underlying query on the view. But when I look at the sql trace of the parallel process running the query on my PDB I can see that the predicate on OWNER has replaced the SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’) with the hardcoded value:<\/p>\n And unfortunately, this value is not the right one: USER2 is my connected user, but not the CURRENT_SCHEMA that I have set. In the same trace, I can see where this value comes from:<\/p>\n but it seems that the current_schema was lost through the call to the parallel process and the PDB switch to my container.<\/p>\n The problem is easy to workaround. This works:<\/p>\n And anyway, better to get the current schema before and pass it as a bind variable. The bind variables are passed correctly through data link queries:<\/p>\n The multitenant architecture is a real challenge for dictionary views. The dictionary is separated: system metadata in CDB$ROOT and user metadata in PDB. But, because of compatibility with non-CDB architecture, the dictionary views must show both of them, and this is where it becomes complex: what was separated on purpose has now to be merged. And complexity is subject to bugs. If you want to get an idea, have a look at dcore.sql in ORACLE_HOME\/rdbms\/admin and compare 11g<\/i> version with 12c<\/i> ones, with all the evolution in 12.1.0.1, 12.1.0.2 and 12.2.0.1<\/p>\n
\nMultitenant architecture is not such a big change and this is why I recommend it when you start a project in 12c<\/em> or if you upgrade to 12.2 – of course after thoroughly testing your application. However, there is a point where you may encounter problems on dictionary queries, because it is really a big change internally. The dictionary separation has several side effects. You should test carefully the queries you do on the dictionary views to get metadata. Here is an example of a bug I recently encountered.
\n
\nThis happened with a combination of things you should not do very often, and not in a critical use case: query dictionary for constraints owned by your current schema, when different than the user you connect with.<\/p>\nSQL> connect sys\/oracle@\/\/localhost\/PDB1 as sysdba\nConnected.\nSQL> grant dba to USER1 identified by USER1 container=current;\nGrant succeeded.\nSQL> grant dba to USER2 identified by USER2 container=current;\nGrant succeeded.\n<\/code><\/pre>\n\nSQL> connect USER1\/USER1@\/\/localhost\/PDB1\nConnected.\nSQL> create table DEMO(dummy constraint pk primary key) as select * from dual;\nTable DEMO created.\n<\/code><\/pre>\n\nSQL> connect USER2\/USER2@\/\/localhost\/PDB1\nConnected.\nSQL> alter session set current_schema=USER1;\nSession altered.\n<\/code><\/pre>\nBug<\/h3>\n
\nSQL> select sys_context('USERENV','CURRENT_SCHEMA'), a.*\n 2 from sys.dba_constraints a\n 3 where owner = sys_context('USERENV','CURRENT_SCHEMA')\n 4 \/\n \nno rows selected\n<\/code><\/pre>\n\nSQL> select owner,constraint_name\n 2 from sys.dba_constraints a\n 3 where owner = 'USER1'\n 4 \/\nOWNER CONSTRAINT_NAME\n----- ---------------\nUSER1 PK\n<\/code><\/pre>\n\nSQL_ID 2fghqwz1cktyf, child number 0\n-------------------------------------\nselect sys_context('USERENV','CURRENT_SCHEMA'), a.* from\nsys.dba_constraints a where owner =\nsys_context('USERENV','CURRENT_SCHEMA')\n \nPlan hash value: 1258862619\n \n--------------------------------------------------------------------------------------------------------------\n| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |\n--------------------------------------------------------------------------------------------------------------\n| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.32 | 2656 |\n| 1 | PARTITION LIST ALL | | 1 | 2 | 0 |00:00:00.32 | 2656 |\n|* 2 | EXTENDED DATA LINK FULL| INT$INT$DBA_CONSTRAINTS | 2 | 2 | 0 |00:00:00.32 | 2656 |\n--------------------------------------------------------------------------------------------------------------\n \nPredicate Information (identified by operation id):\n---------------------------------------------------\n \n 2 - filter(((\"INT$INT$DBA_CONSTRAINTS\".\"OBJECT_TYPE#\"=4 OR\n (\"INT$INT$DBA_CONSTRAINTS\".\"OBJECT_TYPE#\"=2 AND \"INT$INT$DBA_CONSTRAINTS\".\"ORIGIN_CON_ID\"=TO_NUMBER(SY\n S_CONTEXT('USERENV','CON_ID')))) AND \"OWNER\"=SYS_CONTEXT('USERENV','CURRENT_SCHEMA')))\n<\/code><\/pre>\n\nSELECT \/*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) OPT_PARAM('_ENABLE_VIEW_PDB', 'FALSE') *\/ OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,OBJECT_TYPE#,SEARCH_CONDITION,SEARCH_CONDITION_VC,R_OWNER,R_CONSTRAINT_NAME,DELETE_RULE,STATUS,DEFERRABLE,DEFERRED,VALIDATED,GENERATED,BAD,RELY,LAST_CHANGE,INDEX_OWNER,INDEX_NAME,INVALID,VIEW_RELATED,ORIGIN_CON_ID FROM NO_COMMON_DATA(SYS.\"INT$INT$DBA_CONSTRAINTS\") \"INT$INT$DBA_CONSTRAINTS\" WHERE (\"INT$INT$DBA_CONSTRAINTS\".\"OBJECT_TYPE#\"=4 OR \"INT$INT$DBA_CONSTRAINTS\".\"OBJECT_TYPE#\"=2 AND \"INT$INT$DBA_CONSTRAINTS\".\"ORIGIN_CON_ID\"=TO_NUMBER('3')) AND \"INT$INT$DBA_CONSTRAINTS\".\"OWNER\"=q'\"USER2\"'\n<\/code><\/pre>\n\nselect 'q''\"' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '\"''' from sys.dual\n<\/code><\/pre>\nWorkaround<\/h3>\n
\nSQL> select owner,constraint_name\n 2 from sys.dba_constraints a\n 3 where owner = ( select sys_context('USERENV','CURRENT_SCHEMA') from dual )\n 4 \/\n \nOWNER CONSTRAINT_NAME\n----- ---------------\nUSER1 PK\n<\/code><\/pre>\n\nSQL> variable v varchar2(30)\nSQL> exec select sys_context('USERENV','CURRENT_SCHEMA') into :v from dual;\n \nPL\/SQL procedure successfully completed.\n \nSQL> select sys_context('USERENV','CURRENT_SCHEMA'), a.*\n 2 from sys.dba_constraints a\n 3 --where owner = sys_context('USERENV','CURRENT_SCHEMA')\n 4 where owner = :v\n 5 \/\n<\/code><\/pre>\nSo what?<\/h3>\n
Added OCT-17<\/h3>\n