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

In oracle user and schema *don't* have many-to-many relation (actually the difference is more semantic - they are quite the same) — A.B.Cade, Apr 17 '13 at 08:07
Why ? one user can have multiple schema and a schema can be associated with multiple users. Isn't ? — Ravi, Apr 17 '13 at 08:09
In oracle `user` and `schema` are pretty much the same. See [this post](http://stackoverflow.com/questions/880230/difference-between-a-user-and-a-schema-in-oracle) — A.B.Cade, Apr 17 '13 at 08:12
@A.B.Cade - that thread you link contains a deleted post which links to a blog article I wrote on this topic (I didn't post that reponse). A mod deleted it because SO disapproves of answers which are just links but I think the article is pertinent as it explains why USER and SCHEMA aren't the same thing: http://radiofreetooting.blogspot.com/2007/02/user-schema.html — APC, Apr 17 '13 at 08:53
@APC, Thanks for the link, it's a great post. I know they're not the same (this is why I used words like `quite`, `pretty much` and `think as` in my comments) and I'm guilty as well in *using them interchangeably* as you nicely written... — A.B.Cade, Apr 17 '13 at 09:04
@A.B.Cade - of course I'm guilty of using the terms interchangeably myself. Most of the time it doesn't matter but I think in this context the distinction is crucial to any proper understanding of what is happening and why. — APC, Apr 17 '13 at 09:36

A.B.Cade · Accepted Answer · 2013-04-17T19:25:00.103

13

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;

edited Apr 17 '13 at 19:25

answered Apr 17 '13 at 08:19

A.B.Cade

16,735
1
37
53

@jWeavers, I updated my answer. I also noticed that I forgot to add the link to the `create user` documentation... – A.B.Cade Apr 17 '13 at 19:25
i should use `oe` account to create `oe` schema ? – Ravi Apr 17 '13 at 19:30
@jWeavers, according to the documentation as cited by DavidAldridge - yes – A.B.Cade Apr 17 '13 at 19:33
So, i have one question, when I will create new user `oe`, by default, Oracle will create new schema with the same name `oe`. So, when I will try to execute above query, won't it through any exception, since same schema is already exist ? – Ravi Apr 17 '13 at 19:41
@jWeavers, see the documentation (it's on the first line!!): `Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction`. More than that - it staes clearly: `This statement does not actually create a schema` – A.B.Cade Apr 17 '13 at 19:56
which means, its just creating multiple object simultaneously. isn't ?\ – Ravi Apr 17 '13 at 19:58
Right, the real "create schema" is within the "create user" – A.B.Cade Apr 17 '13 at 20:00

score 2 · Answer 2 · answered Apr 17 '13 at 08:18

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.

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

2 Answers2