How to create new schema and list all schema name in oracle

Question

I want to create one new schema in oracle and I used sample code, which is available here

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 !!

Answer 1

From oracle documentation:

This statement does not actually create a schema. Oracle Database automatically creates a schema when you create a user

So you first need to create a User with 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;

Answer 2

From the docs: 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.

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.