[Previous entry: "Schema difference tool"] [Next entry: "Becoming another user"]
CREATE SCHEMA - does it do what it says on the tin?
January 8th, 2005 by Pete
Post to del.icio.us
Post to Furl
I saw an interesting post, I think last week on the oracle-l list and also the same original post was made on comp.databases.oracle.server if I remember. The thread on Oracle-l was titled "[Q] How to create second schema under a user account?" and the poster asked, or rather suggested that it is possible under Oracle to have multiple schemas under a user account and he asked if anyone could give examples of how to do it. He asked how to create a second schema under an account, how to create objects under that schema and then how to switch schemas.
Dick replied to say that there is no way to create a second schema that he knows of and he suggested that the poster is confused as users and schemas are one and the same under Oracle. He also suggested proxy accounts although the actual problem was never stated.
Then came the bit that is interesting for me in the last post in the thread by Lex de Haan. He says that Oracle supports the ANSI/ISO standard CREATE SCHEMA command, but that it fails unless the schema name corresponds to an existing database user.
I did a quick search for a useful explanation of the CREATE SCHEMA command and came across a good post on Experts Exchange titled "Solution Title: How to Create a Schema". This post does a good job of explaining the difference between a user and a schema. Basically a schema is created automatically when a user is created. The last entry on the page gives a good example of the CREATE SCHEMA command. If a user account is created objects such as TABLES or VIEWS can be added with explicit CREATE TABLE or CREATE VIEW commands. Each object would then be added to the schema with separate DDL commands. With the CREATE SCHEMA command its possible to add multiple tables, views and grant privileges on those objects in one statement. The poster gives a good example command like the following:
CREATE SCHEMA AUTHORIZATION pete
CREATE TABLE employee
(empno NUMBER(10) PRIMARY KEY, first_name VARCHAR2(30),last_name VARCHAR2(30))
CREATE VIEW empview
AS SELECT empno, first_name, last_name FROM employee WHERE empno>30
GRANT select ON empview TO zulia;
This looks like a useful command but as Lex says it can only be used against an existing user. It also does not create a second schema under an existing user account as the original poster on oracle-l hoped.
