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: