Oracle Database - User Management

User is basically used to connect to database. All db objects like table,index,view etc can be created under that user.In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

1. Create a user : 
    SQL> create user tesdb identified by tesdb profile default  default tablespace users
    temporary tablespace temp;
Note : Minimum privilege required to connect, to a database is create session.
    SQL> grant create session to tesdb;
    Grant succeeded.
2. Connect as tesdb user : 
    SQL> conn tesdb/tesdb
    Connected.

    SQL> select * from tab;
    no rows selected
3. Changing password of a user : 
    SQL> conn / as sysdba
    Connected.

    SQL> alter user tesdb identified by tesdb123;
    User altered.

    SQL>
    SQL> conn tesdb/tesdb123

    Connected.
    SQL>
4. Lock User Account : 
    SQL> conn / as sysdba
    Connected.

    SQL> alter user tesdb account lock;
    user altered

    select username,profile,account_status,lock_date from dba_users
    where username='TESDB';

    USERNAME	     PROFILE		  ACCOUNT_STATUS		   LOCK_DATE
    -------------------- -------------------- -------------------------------- ---------
    TESDB		     DEFAULT		  LOCKED			   27-OCT-23
5. Unlock a user : 
    SQL> alter user tesdb account unlock;
    User altered.

    SQL> select username,profile,account_status,lock_date from dba_users
    where username='TESDB';

    USERNAME	     PROFILE		  ACCOUNT_STATUS		   LOCK_DATE
    -------------------- -------------------- -------------------------- ---------
    TESDB		     DEFAULT		  OPEN
6. Changing default tablespace of a user : 
    SQL> select username,default_tablespace from dba_users
    where username='TESDB';

    USERNAME	     DEFAULT_TABLESPACE
    -------------------- ------------------------------
    TESDB		     USERS

    SQL> alter user tesdb default tablespace tesdb;
    User altered.

    SQL> select username,default_tablespace from dba_users
    where username='TESDB';

    USERNAME	     DEFAULT_TABLESPACE
    -------------------- ------------------------------
    TESDB		     TESDB


(Oracle Database - Job Scheduler)