Thanks everyone for your suggestions.
This morning I ran exactly the same script again and saw 44 rows returned for
the last query instead of "no rows selected". This is what I'd expect to see.
The database is just a personal learning sandpit so nobody else would have made
any changes since yesterday. So I'm at a bit of a loss why it is working now.
I'll try to see if I can figure out a way to reproduce the issue.
Charlotte
On Thursday, February 18, 2016 10:37 PM, Hans Forbrich
<fuzzy.graybeard@xxxxxxxxx> wrote:
Please try "SELECT CON_ID from CDB_USERS;" then try "SELECT USERNAME FROM
(SELECT * FROM CDB_USERS) WHERE CON_ID=4;"
As an aside, the views will usually only retrieve rows from PDBs that are
open. That is the case here, and I suspect that you are facing one of the
'opportunities for improvement' that are to be found in the CONTAINERS table
function as it seems to work correctly in 12.1.0.2 ...
/Hans
On 18/02/2016 11:35 AM, Charlotte Hammond (Redacted sender
charlottejanehammond for DMARC) wrote:
Hi All,
I'm just doing a bit of self study on CDBs (no current plans to use this for
real) and can't understand why, when connected to CDB$ROOT as SYS I can count
the users in CDB_USERS but can't select them. I'm not sure if this is a bug
or, being new to all this, I'm just not understanding the concept properly.
I'd appreciate any enlightenment! (Please see my sqlplus spool below)
Many thanks, Charlotte
SQL> select version from v$instance; VERSION -----------------
12.1.0.1.0 SQL> show user USER is "SYS" SQL> show con_id CON_ID
------------------------------ 1 SQL> select con_id,open_mode from
v$pdbs; CON_ID OPEN_MODE ---------- ---------- 2 READ
ONLY 3 READ WRITE 4 READ WRITE 5 READ WRITE
6 READ WRITE SQL> select count(*) from cdb_users where con_id=4;
COUNT(*) ---------- 44 SQL> select * from cdb_users
where con_id=4; no rows selected
