some syntax to create user in Oracle Database

***********************************************************************************************************************

--To find the default tablespace in DB:

SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME = 'DEFAULT_PERMANENT_TABLESPACE';

-- this gives the output "USERS" in my case

--create user: here i am using user name "sdb" password "password"

CREATE USER sdb IDENTIFIED BY password

DEFAULT TABLESPACE USERS       

TEMPORARY TABLESPACE temp ;     

--Default object tablespace

-- Assign space for table and index segments

--If a user creates an object (such as a table or an index) without explicitely specifying the tablespace in which it goes, Oracle will place the object in the default object tablespace.

--Default temporary tablespace

-- Assign sort space

--If a user needs a temporary segment for an operation (such as a sort), Oracle will place the data in his default temporary tablespace.

--to give the user some space quota on its tablespaces:

ALTER USER sdb QUOTA UNLIMITED ON Users;

--grant CREATE SESSION privilege necessary to login, this is the right to connect to the database

GRANT CONNECT, RESOURCE TO sdb;

-- Make user a DB Administrator

GRANT DBA TO sdb;

-- lock a user account

ALTER USER sdb ACCOUNT LOCK   

-- unlocks a locked users account

ALTER USER sdb ACCOUNT UNLOCK;

-- Force user to choose a new password

ALTER USER sdb PASSWORD EXPIRE; 

-- choose new password

ALTER USER sdb IDENTIFIED BY password1

-- Remove user

DROP USER sdb CASCADE;

***********************************************************************************************************************

some concepts:

Users in Oracle                 

In Oracle terminology, a user is someone who can connect to a database (if granted enough privileges) and optionally (again, if granted the appropriate privileges) can own objects (such as tables) in the database.

The objects a user owns are collectively called >schema. A schema, on its part, is always bound to exactly one user. Because there is obviously a 1 to 1 relationship between a user and a schema, these two terms are often used interchangeable.

In order to find out what users are created on the database, one can use dba_users

A user's schema consists of all objects that belong to this user.

Oracle database objects                              

Oracle distinguishes between the following «object»:

    Tables

    Views

    Indexes

    Clusters

    Synonyms

    Sequences

    Procedures

    Functions

    Packages

    Triggers

    ...

All objects that belong to the same user are said to be this user's schema.

***********************************************************************************************************************