Re: CDB_USERS

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_at_freelists.org> 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_at_gmail.com> 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
>
>
>
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l