26

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.

Community
  • 1
  • 1
Vipin
  • 4,851
  • 3
  • 35
  • 65
  • 5
    `select user from dual`; –  Mar 10 '14 at 10:48
  • @a_horse_with_no_name it gives my user id , i need schema user. – Vipin Mar 10 '14 at 10:58
  • I don't think I understand what you mean with "schema useer". If you are talking about the owner of the tables you have access to, run `select distinct owner from all_tables` –  Mar 10 '14 at 11:15
  • @a_horse_with_no_name In Oracle Database i have a schema and i am connected to it with read only user. And by schema user I mean user id with schema name which is owner for all tables. – Vipin Mar 10 '14 at 12:55
  • @Vipin That doesn't make much sense. In Oracle, schema == user. And there's no such thing as "owner for all tables" - each user may or may not own tables. Run the query provided by a_horse_with_no_name - this should give you an idea of the tables you have access to. You might also want to try `select owner, table_name from all_tables`. – Frank Schmitt Mar 10 '14 at 15:18
  • @FrankSchmitt , I agree schema == user but DBA creates some users with some specific roles ( like select only ). In my case i had read only user and wanted to to know what is my schema name. I got partial answer in your sql , as it gave me owner ( which is schema in my case ). – Vipin Mar 10 '14 at 15:27
  • @Vipin I'm still not 100% sure what exactly your problem is - please add some additional information. Meanwhile, I'm voting for putting this on hold. – Frank Schmitt Mar 12 '14 at 07:21
  • 6
    @FrankSchmitt: but user is not necessarily the same as the "current schema", you *can* change the current schema in Oracle and in that case: user <> schema. –  Mar 12 '14 at 07:21
  • @Vipin What ever may be the case, if you login as `vipin` your default schema is `vipin` For previleges, Check USER_SYS_PRIVS, USER_TAB_PRIVS, USER_ROLE_PRIVS tables. – Srini V Mar 12 '14 at 07:22
  • @realspirituals I have logged in using vipin( My read only user) yes default schema is vipin(Oracle generated) but all objects are created by different user (schema) which is owner for all objects. vipin is read only user provided by dba which has only select permission for all tables. – Vipin Mar 12 '14 at 08:02
  • @FrankSchmitt after seeing your comment i was searching difference between user and schema, I found schema is also a user which is owner for all DB objects. In that case your sql is perfect. But I am confused now , when i give db connection properties it asks for SID and i enter my schema ( which is a user) , so how SID is related to a user? – Vipin Mar 12 '14 at 08:10
  • 2
    @realspirituals: the default schema is not necessarily `vipin` if you run an `alter session set current_schema=foobar` (which could e.g. happen in a logon trigger). –  Mar 12 '14 at 10:52
  • See also https://stackoverflow.com/questions/23417522/plsql-sqlplus-get-current-username – Vadzim Apr 09 '18 at 10:16

3 Answers3

43

Call SYS_CONTEXT to get the current schema. From Ask Tom "How to get current schema:

select sys_context( 'userenv', 'current_schema' ) from dual;
Danilo Piazzalunga
  • 7,590
  • 5
  • 49
  • 75
  • 3
    It gives same result as "select user from dual" , which is my read only user. Because when DBA created read only user schema is also created automatically And it will not change untill we run "alter session set current_schema=foobar" – Vipin Mar 12 '14 at 11:55
  • 1
    Yes, you are supposed to call `alter session set current_schema=foobar` beforehand. – Danilo Piazzalunga Mar 12 '14 at 12:07
  • 1
    Vipin user will give the person logged in but not the actual schema name. If you read the question, that will tell you logging with RO user but get the schema name. Even executing this before `alter session set current_schema=foobar` will not give the correct schema name for `select user from dual` – changeme Jan 16 '18 at 21:30
25

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:

  1. Set the current schema in your session:
ALTER SESSION SET CURRENT_SCHEMA=XYZ
  1. 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:

  1. Query the current schema setting:
SELECT SYS_CONTEXT('USERENV','CURRENT_SCHEMA') FROM DUAL
  1. List your synonyms:
SELECT * FROM ALL_SYNONYMS WHERE OWNER = USER
  1. 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');
Chewbacca17
  • 61
  • 5
Codo
  • 75,595
  • 17
  • 168
  • 206
  • 3rd option using all_tables worked for me as u can see in Edit part also , i was just waiting to accept some answer which tells same :) , and u have given very good details also. – Vipin Mar 12 '14 at 13:48
1

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.

hmmh
  • 57
  • 2
  • 10