How to crate a schma of your own

It's very easy.

We can not create the SCHEMA by executing the query 'create schema schema_name' .Absolutely wrong. Yor are in a wrong path. Don't worry it happens when we get excited but tell you ,this is  also a good activities to make your brain and hand active.

OK!! for creating a schema of your own ,you have to follow certain steps and below are the steps:

1.  Create a new user .
2.  Assign Grant permission to the user.
3.  Create the objects  and you are done .

Step1. Create a new user

As the definition of  schema says 'The logical structure of the database where the objects are resided' . Whenever we are created a Schema ,then it will directly created in the oracle database.

 Example : Scott is a schema which contains the object like emp, dept and many more.

We can create an user by executing the command 'Create user username' .

CREATE user debashis 
IDENTIFIED by passworddebshis123
DEFAULT TABLESPACE tbs_perm_01      (you can give any name ..no issue)
TEMPORARY TABLESPACE tbs_temp_01 (same as i mentioned above)
quota 30M on tbs_perm_01;

Explanation:

In the above query we have create an user debashis in the oracle database whose password is passworddebshis123 ,the default table space be tbs_perm_01 with a quota 30mb and the temporary table space is tbs_temp_01.

Step2. Assign privileges to new user in Oracle

Now time to setting up the the schema is to assign " system privileges" to the new user debashis.
the system privileges such as create tables,view,triggers, procedure,sequence and synonyms in the new schema.

Time to exercise your hands .

GRANT create session to debshis;
GRANT create synonyms to debashis;
GRANT create sequence to debashis;
GRANT create any trigger to debashis;
GRANT create any procedure to debashis;
GRANT create table to debashis;
GRANT create view to debashis;

Now an user can do the above activities without finding any error.

Step3. Create objects(Tables,objects,functions and operators.also segments  in the schemas)

Now this is the most important part . So let me explain it .

The syntex we have to follow to write a schema is :

CREATE SCHEMA schema_name [ AUTHORIZATION user_name ] [ schema_element [ ... ] ]
CREATE SCHEMA AUTHORIZATION user_name [ schema_element [ ... ] ]

When we write create schema ,it will make a new schema in the database.But aware the thing that the name of the Schema should be different from the existing schema name in the database.

Schema_name:
It's just the name of the schema what you want to give for identification.

user_name: 
The name of the user who owns the schema. If omitted, defaults to the user executing the command.

Schema_element:

An SQL statement defining an object to be created within the schema. Currently, only CREATE TABLE, CREATE VIEW, CREATE INDEX, CREATE SEQUENCE, CREATE TRIGGER and GRANT are accepted as clauses within CREATE SCHEMA. Other kinds of objects may be created in separate commands after the schema is created.

Examples for creating the different faces of the schema.

1. Creation of the simple schema
                                          CREATE SCHEMA FACEBOOK;
2.Creation of a schema for user debashis;
                                           CREATE SCHEMA FACEBOOK Authorization debashis;
3. Create a schema and create a table and view on this
                                             CREATE SCHEMA FACEBOOK
                                                        CREATE TABLE posts( title text, posting date, comment text[])
                                              CREATE VIEW FBCOMMENTS  AS
                                                        SELECT TITLE ,POSTING FROM POSTS WHERE COMMENT IS NOT NULL;
    
The equivalent syntex of the above step3 is

If we are taking schema into account and table under it ,then the you have to follow the below syntex.

CREATE SCHEMA FACEBOOK
                                    CREATE TABLE FACEBOOK.posts( title text, posting date, comment text[])
                                              CREATE VIEW FACEBOOK. FBCOMMENTS  AS
                                                        SELECT TITLE ,POSTING FROM POSTS WHERE COMMENT IS NOT NULL;


HOW TO ALTER THE SCHEMA

The statement is very similar to the ALTER staement that we used in sql ,oracle. so i don't think we will find any difficult in this regard.

The Syntex be:
                       ALTER SCHEMA name RENAME to new_schema_name;

HOW TO DROP A SCHEMA

Remove a schema from the database.

The Condition:
While doing so be careful about the fact that,  schema should not contain any kind of object under it otherwise dropping a schema can't be done.

The query must be:   DROP SCHEMA schema_name;

schema_name

Is the name by which the schema is known within the database.

Where you can find out the schema?

As i mentioned above the user should have the system privileges so we can reach out the schema in the sys.schema .

 Thanks.....