Forum Stats

  • 3,874,195 Users
  • 2,266,681 Discussions
  • 7,911,766 Comments

Discussions

Oracle 12.1.0.2; select * from containers(GLOBAL_NAME); Requires SYSDBA?

Marek Läll
Marek Läll Member Posts: 23 Blue Ribbon
edited May 3, 2018 9:20AM in Multitenant

Hi,

What is the privilege (other than SYSDBA) that enables usage of function CONTAINERS() ?

QUERY USING USER SYSTEM ON 12.1 FAILS:

$ sqlplus system@l0db007

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 9 11:32:26 2018

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,

Real Application Testing and Unified Auditing options

SQL> select * from containers(GLOBAL_NAME);

select * from containers(GLOBAL_NAME)

                         *

ERROR at line 1:

ORA-00942: table or view does not exist

QUERY USING USER SYS AS SYSDBA ON 12.1 SUCCEEDS:

$ sqlplus sys@l0db007 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 9 11:33:26 2018

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,

Real Application Testing and Unified Auditing options

SQL> select * from containers(GLOBAL_NAME);

GLOBAL_NAME       CON_ID

----------------- ------

LAB_EMREPO.XXX.YY      3

L0DB007.XXX.YY         1

2 rows selected.

QUERY USING USER SYSTEM ON 12.2 SUCCEEDS:

$ sqlplus system@l0db008

SQL*Plus: Release 12.1.0.2.0 Production on Mon Apr 9 11:35:44 2018

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select * from containers(GLOBAL_NAME);

GLOBAL_NAME     CON_ID

--------------- ------

L0DB008.XXX.YY   1

L0PDB008.XXX.YY  3

2 rows selected.

Regards,

Marek

Answers

  • Unknown
    edited Apr 9, 2018 12:20PM

    I think you've answered your own question. The fact that it works now is likely due to a bug fix (enhancement?).

    Not only were there were several issues with the older CDB$VIEW way of doing things but 12.2 introduced application containers which make the need for standard users accessing data in multiple PDBs even more necessary.

    Also see the 12.2 new feature list about a new hint for that function.

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/new-features.html#GUID-3A8E2A84-4ACB-4354-9DB1-53E…

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,938 Red Diamond
    edited Apr 10, 2018 8:38AM

    No, it doesn't require any privileges (besides create session of course) :

    SQL> create user u1 identified by u1

      2  default tablespace users;

    User created.

    SQL> grant create session to u1;

    Grant succeeded.

    SQL> connect [email protected]

    Enter password: **

    Connected.

    SQL> select banner from v$version

      2  /

    BANNER

    --------------------------------------------------------------------------------

    Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

    PL/SQL Release 12.2.0.1.0 - Production

    CORE    12.2.0.1.0      Production

    TNS for 64-bit Windows: Version 12.2.0.1.0 - Production

    NLSRTL Version 12.2.0.1.0 - Production

    SQL> column global_name format a30

    SQL> select * from containers(GLOBAL_NAME);

    GLOBAL_NAME                        CON_ID CON$NAME   CDB$NA

    ------------------------------ ---------- ---------- ------

    PDB1SOL122.XXX.PROD                     3 PDB1SOL122 sol122

    SQL>

    SY.

  • Marek Läll
    Marek Läll Member Posts: 23 Blue Ribbon
    edited Apr 11, 2018 4:27AM

    Thanx for your effort.

    Please note that this topic/issue is in version 12.1.

    Your example is provided using version 12.2

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,938 Red Diamond
    edited Apr 11, 2018 7:37AM
    Marek Läll wrote:Thanx for your effort.Please note that this topic/issue is in version 12.1.Your example is provided using version 12.2

    Oops, but it makes no diff (at least in patchset I am on):

    C:\>set ORACLE_HOME=C:\app\oracle12c\product\12.1.0\dbhome_1

    C:\>%ORACLE_HOME%\OPatch\opatch lspatches

    20885778;Interim patch for Base bug 20885778

    24345625;WINDOWS DB BUNDLE PATCH 12.1.0.2.160831(64bit):24345625

    C:\>

    SQL> create user u1 identified by u1

      2  default tablespace users;

    User created.

    SQL> grant create session to u1;

    Grant succeeded.

    SQL> connect [email protected]

    Enter password: **

    Connected.

    SQL> select banner from v$version

      2  /

    BANNER

    --------------------------------------------------------------------------------

    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

    PL/SQL Release 12.1.0.2.0 - Production

    CORE    12.1.0.2.0      Production

    TNS for 64-bit Windows: Version 12.1.0.2.0 - Production

    NLSRTL Version 12.1.0.2.0 - Production

    SQL> column global_name format a30

    SQL> select * from containers(GLOBAL_NAME);

    LOBAL_NAME                        CON_ID

    ------------------------------ ----------

    PDB1SOL12.XXX.PROD                      3

    SQL>

    SY.

  • Unknown
    edited Apr 11, 2018 2:26PM

    I already told you the likely reason.

    With ANY new functionality only Oracle can give you a definitive answer about WHAT functionality is supported in each version and patch set.

    As you have already shown it works in 12.2 so if you don't want to upgrade to 12.2 you should submit a request to Oracle using your MOS account and ask if a patch is available for 12.1.

  • Marek Läll
    Marek Läll Member Posts: 23 Blue Ribbon
    edited May 3, 2018 6:54AM

    Thanx for your effort.

    Please note that I log in to container database. Point of the query is to get rows from container database and from all pluggable database using one query.

    Your example is provided by logging in to pluggable database.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,938 Red Diamond
    edited May 3, 2018 9:20AM

    Well, based on Querying User-Created Tables and Views Across All PDBs:

    Note:

    You can also use the CONTAINERS clause to query Oracle-supplied tables and views. When running the query, ensure that the current user is the owner of the table or view, or create a view using the CONTAINERS clause and grant SELECT privilege on the view to the appropriate users.

    So it sounds like (since GLOBAL_NAME is owned by SYS and you are connecting as SYSTEM) you'd have to create a view in SYSTEM:

    SQL> connect [email protected]

    Enter password: *********

    Connected.

    SQL> select sys_context('userenv','con_name') from dual;

    SYS_CONTEXT('USERENV','CON_NAME')

    --------------------------------------------------------------------------------

    CDB$ROOT

    SQL> column global_name format a30

    SQL> select * from containers(GLOBAL_NAME);

    select * from containers(GLOBAL_NAME)

                            *

    ERROR at line 1:

    ORA-00942: table or view does not exist

    Same result as you got. Now I'll create GLOBAL_NAME view in CDB user SYSTEM schema:

    SQL> create view system.global_name as select * from sys.global_name;

    View created.

    SQL> select * from containers(GLOBAL_NAME);

    select * from containers(GLOBAL_NAME)

    *

    ERROR at line 1:

    ORA-12801: error signaled in parallel query server P002

    ORA-00942: table or view does not exist

    SQL>

    So now I got ORA-00942 because CONTAINERS couldn't resolve GLOBAL_NAME in PDB user SYSTEM schema, so I'll create it:

    SQL> connect [email protected]

    Enter password: *********

    Connected.

    SQL> create view system.global_name as select * from sys.global_name;

    View created.

    SQL>

    Now' I'll connect to CDB as SYSTEM:

    SQL> connect [email protected]

    Enter password: *********

    Connected.

    SQL> select * from containers(GLOBAL_NAME);

    GLOBAL_NAME                        CON_ID

    ------------------------------ ----------

    SOL12.AD1.PROD                          1

    PDB1SOL12.AD1.PROD                      3

    SQL>

    SY.

This discussion has been closed.