CREATE USER sachi
    IDENTIFIED BY password
    DEFAULT TABLESPACE data_ts
    QUOTA 100M ON test_ts
    QUOTA 500K ON data_ts
    TEMPORARY TABLESPACE temp_ts
    PROFILE auditor;
GRANT create session TO sachi;

Alter User : Users can change their own passwords. However, to change any other option of a user security domain, you must have the ALTER USER system privilege.

 

Security administrators are typically the only users that have this system privilege, as it allows a modification of any user security domain. This privilege includes the ability to set tablespace quotas for a user on any tablespace in the database, even if the user performing the modification does not have a quota for a specified tablespace.

ALTER USER sachi
    IDENTIFIED EXTERNALLY
    DEFAULT TABLESPACE data_ts
    TEMPORARY TABLESPACE temp_ts
    QUOTA 100M ON data_ts
    QUOTA 0 ON test_ts
    PROFILE operator;

---

DROP USER sachi CASCADE;

Here's how to lock or unlock Oracle database user accounts.

SQL> ALTER USER username ACCOUNT LOCK;

SQL> ALTER USER username ACCOUNT UNLOCK;