Can we create multiple schemas for a particular user? I am currently logged in as X/Y user and when I tried creating a schema using
create schema authorization sample_schema, I got the error
the schema name is missing or is incorrect in an authorization clause of a create schema statement. I do know that a default schema
X would have been created.
CREATE SCHEMA in Oracle does - contrary to its name - not create a new schema.
It is merely a shorthand to create several tables in a single statement.
Quote from the manual:
Use the CREATE SCHEMA statement to create multiple tables and views and perform multiple grants in your own schema in a single transaction
and further down the explanation on what the "schema" name parameter is:
The schema name must be the same as your Oracle Database username.