Create and Drop Database Schemas

Unlike other DB queries, schema object creation look straight forward but in reality it is not. It took sometime for me to figure out and come up with right SQL statements.

Create DB schema Greeting and a table test_table in it:

CREATE user Greeting Identified by Greeting DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users

GRANT CREATE SESSION, CREATE RULE, CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER, CREATE VIEW, CREATE PROCEDURE, CREATE OPERATOR, CREATE TYPE TO Greeting

CREATE TABLE Greeting.test_table (ID VARCHAR2(30) PRIMARY KEY,name VARCHAR2(30) NOT NULL, type VARCHAR2(15) NOT NULL);


Now a table test_table is created in the schema Greeting. Try 'desc Greeting.test_table' to verify

Drop DB schema:

Drop user Greeting cascade;


These queries are for Oracle DB;