Creating a User: CREATE USER arif IDENTIFIED BY arif#2;
Change the password of already created user: ALTER USER arif IDENTIFIED BY arif#1;
View Created User Information: SELECT username, password, created FROM dba_users WHERE username='ARIF';
Give user a Default access to particular tablespace during user creation time: CREATE USER liton identified BY liton#1 DEFAULT TABLESPACE users;
View the Default Tablespace to a particular user: SELECT username, default_tablespace FROM dba_users WHERE username='LITON';
Give a default tablespace already created user: ALTER USER arif DEFAULT TABLESPACE users;
Include Access to a Temporary Tablespace during user creation time: CREATE USER arefin IDENTIFIED BY arefin#1 TEMPORARY TABLESPACE temp;
Include Access to a Temporary Tablespace already created user: ALTER USER arif TEMPORARY TABLESPACE temp;
Include Quota On Tablespaces during user creation time: CREATE USER asif IDENTIFIED by asif#1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 2M ON users;
CREATE USER utpal IDENTIFIED by utpal#1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON users;
View Access in a particular Tablespace information for a particular user: SELECT username, tablespace_name, max_bytes, max_blocks FROM dba_ts_quotas WHERE username='ASIF';
Give Tablespace Quota to a already created user: ALTER USER arif QUOTA 3M ON users; Assign profile to a user during creation time: CREATE USER rabbi IDENTIFIED BY rabbi#1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 2M ON users PROFILE DEFAULT;
Provide Profile to a already created user: ALTER USER arif PROFILE monitoring_profile;
View Assign Profile name of a particular user: SELECT username, profile FROM dba_users WHERE username='ARIF';
Use of EXPIRE keyword: CREATE USER rahi IDENTIFIED BY rahi#1 DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp QUOTA 2M on users PROFILE DEFAULT PASSWORD EXPIRE;
PASSWORD EXPIRE: forces the user to reset the password in the first time login. View Account Status and Expiry date of users: SELECT username, expiry_date, account_status FROM dba_users WHERE username='RAHI';
Drop User: DROP USER rahi; Drop User with Objects: DROP USER rahi CASCADE;
Note: Current Connected User Can Not be dropped
LOCK a User Account: ALTER USER arif ACCOUNT lock;
UNLOCK a User Account: ALTER USER arif ACCOUNT unlock;
Some Special Queries for Monitoring:
|