How to find schema name in Oracle when you are connected in sql session using read only user

Question

I am connected to a oracle database with a read only user and i used service name while Setting up connection in sql developer hence i dont know SID   schema     How can i find out schema name which i am connected to    I am looking for this because i want to generate ER diagram and in that process at one step it asks to select schema  When i tried to select my user name   i dint get any tables as i guess all tables are mapped with schema user   Edit  I got my answer partially by the below sql Frank provided in comment   it gave me owner name which is schema in my case  But I am not sure if it is generic solution applicable for all cases   select owner  table name from all tables    Edit  I think above sql is correct solution in all cases because schema is owner of all db objects  So either i get schema or owner both are same  Earlier my understanding about schema was not correct and i gone through another question and found schema is also a user   Frank a horse with no name Put this in answer so that i can accept it

User · Answer

Call SYS CONTEXT to get the current schema  From Ask Tom  How to get current schema   select sys context   userenv    current schema    from dual

User · Answer

To create a read-only user  you have to setup a different user than the one owning the tables you want to access   If you just create the user and grant SELECT permission to the read-only user  you ll need to prepend the schema name to each table name  To avoid this  you have basically two options    Set the current schema in your session    ALTER SESSION SET CURRENT SCHEMA XYZ    Create synonyms for all tables    CREATE SYNONYM READER USER TABLE1 FOR XYZ TABLE1   So if you haven t been told the name of the owner schema  you basically have three options  The last one should always work    Query the current schema setting    SELECT SYS CONTEXT  USERENV   CURRENT SCHEMA   FROM DUAL    List your synonyms    SELECT   FROM ALL SYNONYMS WHERE OWNER   USER    Investigate all tables  with the exception of the some well-known standard schemas     SELECT   FROM ALL TABLES WHERE OWNER NOT IN   SYS    SYSTEM    CTXSYS    MDSYS

User · Answer

How about the following 3 statements  -- change to your schema  ALTER SESSION SET CURRENT SCHEMA yourSchemaName   -- check current schema SELECT SYS CONTEXT  USERENV   CURRENT SCHEMA   FROM DUAL   -- generate drop table statements SELECT  drop table    table name   cascade constraints   FROM ALL TABLES WHERE OWNER    yourSchemaName    COPY the RESULT and PASTE and RUN

[sql] How to find schema name in Oracle ? when you are connected in sql session using read only user

Examples related to sql

Examples related to oracle11g