How to See the Name of Your Database

This example was tested on Oracle 11.2. It shows how a user can see his database name with only the CREATE SESSION privilege. First I created a user:

SQL> create user fred identified by bloggs

  2  /

User created.

SQL> grant create session to fred

  2  /

Grant succeeded.

SQL>

Then I tried to create the user again. This failed. So if the user had existed, perhaps with extra privileges, before I started this example, I would have seen an error when I ran the preceding step:

SQL> create user fred identified by bloggs

  2  /

create user fred identified by bloggs

            *

ERROR at line 1:

ORA-01920: user name 'FRED' conflicts with another

user or role name

SQL>

Finally, I connected as the new user and displayed the name of the database I was connected to:

SQL> conn fred/bloggs

Connected.

SQL> select sys_context('USERENV', 'DB_NAME')

  2   DATABASE from dual

  3  /

DATABASE

----------

ANDREW01

SQL>