Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 967
Vipin Avatar asked Mar 10 '14 10:03

Vipin


People also ask

How do I find the schema name in Oracle?

select owner, table_name from all_tables.

How do I find the database schema in Oracle SQL Developer?

The option is available under File -> Data Modeler -> Import -> Data Dictionary. After choosing the connection to log in, the list of schemas can be selected. Following that would be a screen to select tables, views, and any other objects. After choosing those, hit finish.


1 Answers

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

select sys_context( 'userenv', 'current_schema' ) from dual; 
like image 181
Danilo Piazzalunga Avatar answered Oct 04 '22 05:10

Danilo Piazzalunga