Charlotte,
This behavior has been known to happen when you connect to a database with
a version of sqlplus from a previous version. That's why I was asking you
to confirm the version of sqlplus.
If you happen to have another version installed on the server where you
originally made that query, you might be able to reproduce the issue.
Seth Miller
On Fri, Feb 19, 2016 at 11:40 AM, Charlotte Hammond <
dmarc-noreply@xxxxxxxxxxxxx> wrote:
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
