如何在 Oracle 中查找模式名称？当您使用只读用户在 sql 会话中连接时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22298005/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to find schema name in Oracle ? when you are connected in sql session using read only user
提问by Vipin
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 ).
我使用只读用户连接到 oracle 数据库，并且在 sql developer 中设置连接时使用了服务名称，因此我不知道 SID（架构）。
How can i find out schema name which i am connected to ?
如何找出我连接到的模式名称？
I am looking for this because i want to generate ER diagramand 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.
我正在寻找这个，因为我想生成 ER 图，并且在该过程中的一个步骤中它要求选择模式。当我尝试选择我的用户名时，我没有得到任何表，因为我猜所有表都与模式用户映射。
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.
编辑：我通过评论中提供的以下 sql Frank 部分得到了我的答案，它给了我所有者名称，在我的情况下是架构。但我不确定它是否适用于所有情况的通用解决方案。
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 questionand found schema is also a user.
编辑：我认为上面的 sql 在所有情况下都是正确的解决方案，因为架构是所有 db 对象的所有者。所以要么我得到架构要么所有者都是相同的。早些时候我对架构的理解不正确，我通过另一个问题发现架构也是一个用户。
Frank/a_horse_with_no_namePut this in answer so that i can accept it.
Frank/ a_horse_with_no_name回答这个问题，这样我就可以接受了。
回答by Danilo Piazzalunga
Call SYS_CONTEXTto get the current schema. From Ask Tom "How to get current schema:
调用SYS_CONTEXT以获取当前架构。来自Ask Tom“如何获取当前架构：
select sys_context( 'userenv', 'current_schema' ) from dual;
回答by Codo
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:
如果您只是创建用户并向只读用户授予 SELECT 权限，则需要在每个表名前添加架构名称。为避免这种情况，您基本上有两种选择：
- Set the current schemain 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');
