Home » RDBMS Server » Server Administration » Unable to create a new schema
Unable to create a new schema [message #51948] Sun, 23 June 2002 05:00 Go to next message
Raj
Messages: 411
Registered: November 1998
Senior Member
I have been trying to create a new schema (Oracle 9i) to no avail.

Some "hand holding" from "guru's" would be much appreciated.

After all my "RTFM's" I gathered that to create a new schema, essentially, I need first to create a new user.

So I did the following:

create user PS identified as PS
grant create session to PS
grant IMP_FULL_DATABASE to PS
grant JAVASYSPRIV to PS
grant JAVAUSERPRIV to PS
grant all privileges to PS

All of the above went through successfully (though I have a sneaking suspicion that some of that "Java" stuff is not required).

Anyway, when I then execute the following command:

create schema authorization PS

I get the following error:

ORA-02421: missing or invalid schema authorization identifier.

By now it must be blatantly obvious that I am new to Oracle (used IBM's rdbms's where all I had to do was to fire "create schema PS" as part of the DDL).

Any help would be gratefully received. Muchas gracias.
Re: Unable to create a new schema [message #51950 is a reply to message #51948] Sun, 23 June 2002 14:55 Go to previous message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Hi~.
to start with, literally i would say
a schema is equal to an user area ( where the user objects are stored).
So once you create a user, a schema is created (essentailly..a logical 
workarea is created). Then you grant the required priveleges or roles
to the user.
But on the contrary u can use 
CREATE SCHEMA to create multiple tables and views and perform multiple 
grants in a single transaction. CREATE SCHEMA is seldom used.

quoting from docs
The following statement creates a schema named oe for the sample order-entry 
user oe, creates the table new_product, creates the view new_product_view, 
and grants SELECT privilege on new_product_view to the sample human resources 
user hr.

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 hr; 


if, you are still looking into CREATE SCHEMA, you should login as the concerned user and
execute the statement.

SQL> SHOW USER 
USER is "MAG"
SQL> CREATE SCHEMA AUTHORIZATION CRSCH
  2     CREATE TABLE new_product
  3        (color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER)
  4     CREATE VIEW new_product_view
  5        AS SELECT color, quantity FROM new_product WHERE color = 'RED';
CREATE SCHEMA AUTHORIZATION CRSCH
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier

SQL> CONNECT CRSCH/CRSCH
Connected.
SQL> SHOW USER
USER is "CRSCH"
SQL> CREATE SCHEMA AUTHORIZATION CRSCH
  2     CREATE TABLE new_product
  3        (color VARCHAR2(10)  PRIMARY KEY, quantity NUMBER)
  4     CREATE VIEW new_product_view
  5        AS SELECT color, quantity FROM new_product WHERE color = 'RED';

Schema created.
SQL> SELECT * FROM CAT;

TABLE_NAME                     TABLE_TYPE
------------------------------ -----------
NEW_PRODUCT                    TABLE
NEW_PRODUCT_VIEW               VIEW

Previous Topic: how oracle decide which datafile to be used for new extent
Next Topic: Re: Why can see table @ another DB ?
Goto Forum:
  


Current Time: Wed Dec 25 21:32:39 CST 2024