oracle 如何在oracle中创建新模式并列出所有模式名称
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16054389/
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 create new schema and list all schema name in oracle
提问by Ravi
I want to create one new schema in oracle and I used sample code, which is available here
我想在 oracle 中创建一个新模式，我使用了示例代码，可在此处获得
CREATE SCHEMA AUTHORIZATION oe
CREATE TABLE new_product
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view
AS SELECT color, quantity FROM new_product WHERE color = 'RED'
GRANT select ON new_product_view TO scott
/
But, getting error
但是，得到错误
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier
Also, Please help me how to list name of all available schema. I am using
另外，请帮助我如何列出所有可用架构的名称。我在用
select username from dba_users;
to list schema, but i think, its not a right approach, because, user and schema has many-to-many relation,which means I can't get all schema name here.
列出架构，但我认为，这不是一种正确的方法，因为用户和架构具有多对多关系，这意味着我无法在此处获取所有架构名称。
Please help me !!
请帮我 ！！
回答by A.B.Cade
From oracle documentation:
从 oracle文档：
This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user
该语句实际上并不创建模式。创建用户时，Oracle 数据库会自动创建模式
So you first need to create a Userwith the schema name
因此，您首先需要使用架构名称创建一个用户
As for your query it's fine, since username list is equal to schema names unavailable
至于您的查询，这很好，因为用户名列表等于模式名称不可用
UPDATE:I can't really test it now, but should be something like this:
更新：我现在无法真正测试它，但应该是这样的：
CREATE USER oe IDENTIFIED BY oePSWRD;
CREATE SCHEMA AUTHORIZATION oe
CREATE TABLE new_product
(color VARCHAR2(10) PRIMARY KEY, quantity NUMBER)
CREATE VIEW new_product_view
AS SELECT color, quantity FROM new_product WHERE color = 'RED'
GRANT select ON new_product_view TO scott;
回答by David Aldridge
From the docs: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6014.htm
来自文档：http: //docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6014.htm
The schema name must be the same as your Oracle Database username.
架构名称必须与您的 Oracle 数据库用户名相同。
Do you want to find all users, or all users for which a table (for example) exists? If the latter then ...
您要查找所有用户，还是查找表（例如）存在的所有用户？如果是后者，那么...
select distinct
owner
from
dba_tables
where
owner not in ('SYS','SYSTEM')
Add in other usernames that you're not interested in listing as required.
根据需要添加您不想列出的其他用户名。
