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