Oracle 11g Express Edition – missing or invalid schema authorization identifier

oracleoracle-11gschema

I have a new fresh instance of Oracle Express 11g.
I'm trying to create a new Schema with a Table and a View in the default db (xe).

My script is:

create user XAuthority identified by mysecret;

alter session set current_schema = XAuthority;

create schema authorization XAuthority
create table CUSTOMER (  ID int, CUSTOMER text)
create view CUSTOMER_VIEW as select * from CUSTOMER
grant select on CUSTOMER_VIEW to xareadonly;

but I get an error:

SQL Error: ORA-02421: missing or invalid schema authorization identifier
02421. 00000 – "missing or invalid schema authorization identifier"

*Cause: the schema name is missing or is incorrect in an authorization
clause of a create schema statement.

*Action: If the name is present, it must be the same as the current
schema.

I'm familiar with Postgres and MySql but it seems Oracle is quite different. Can anyone tell me what's wrong here?

Best Answer

CREATE SCHEMA

Purpose

Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction.

Well you have multiple issues. First of all you need to have CREATE SESSION system privilege granted to the newly created user. Secondly your data types are incompatible in Oracle(int should be converted to number and text to varchar2).

In order to use CREATE SCHEMA statement you need to be connected to database as the user which you have specified on the statement.

The problem is you are not connected to database as xauthority by altering the session.

SQL> conn sys/password
Connected.

SQL> create user XAuthority identified by mysecret;

User created.

SQL> alter session set current_schema = XAuthority;

Session altered.

SQL> create schema authorization XAuthority
create table CUSTOMER(ID number, CUSTOMER_name varchar2(20))
create view CUSTOMER_VIEW as select * from CUSTOMER
grant select on CUSTOMER_VIEW to xareadonly;  2    3    4  
create schema authorization XAuthority
*
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier

SQL> show user
USER is "SYS"

The user should be xauthority but it is still SYS.

You need create session system privilege to connect to the database.

SQL> grant create session to xauthority;

Grant succeeded.

You need quota on tablespace to create schema objects such as table.

SQL> alter user xauthority quota unlimited on orapdb1_tbs1;

User altered.

SQL> conn xauthority/mysecret
Connected.

SQL> conn xauthority/mysecret
Connected.
SQL> create schema authorization XAuthority
create table CUSTOMER(ID number, CUSTOMER_name varchar2(20))
create view CUSTOMER_VIEW as select * from CUSTOMER;

Schema created.
Related Question