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:

Description	Query
View Memory Use for Each User Session	SELECT username, value || 'bytes' "Current UGA memory" FROM v_$session sess, v_$sesstat sstat, v_$statname sname WHERE sess.sid = sstat.sid AND sstat.statistic# = sname.statistic# AND sname.name = 'session uga memory';
Active Connected Users	SELECT COUNT(*) "ACTIVE USERS" FROM v_$session WHERE username IS NOT NULL;
Currently Connected Users	SELECT SUBSTR(s.username,1,15) USERNAME, SUBSTR(s.status,1,8) STATUS, SUBSTR(s.server,1,10) SERVER, SUBSTR(s.type,1,10) TYPE, SUBSTR(s.event,1,20) "WAIT EVENT", DECODE(s.command,        1,'Create Table',        2,'Insert',        3,'Select',        6,'Update',        7,'Delete',        8,'Drop',        9,'Create Index',       10,'Drop Index',       12,'Drop Table',       17,'Grant',       26,'Lock Table',       42,'Alter Session',       43,'Alter User',       44,'Commit',       45,'Rollback',       s.command) COMMAND FROM v_$session s, v_$session_wait w WHERE (s.sid = w.sid) AND s.username != 'SYS' ORDER BY s.username;
User Information	set linesize 121 col username format a10 col profile format a10 col "tmp tbs" format a10   SELECT u.username, u.default_tablespace, u.temporary_tablespace "TMP TBS", u.profile, r.granted_role, r.admin_option, r.default_role FROM sys.dba_users u, sys.dba_role_privs r WHERE u.username = r.grantee (+) GROUP BY u.username, u.default_tablespace, u.temporary_tablespace, u.profile, r.granted_role, r.admin_option, r.default_role;
Identify Current Session	SELECT user, osuser FROM gv_$session WHERE sid = (   SELECT sid   FROM gv$mystat   WHERE rownum = 1);
Idle Time	col SID format 999 col IDLE format a20 col PROGRAM format a20 col USERNAME format a20 SELECT sid, osuser, username, status, TO_CHAR(logon_time, 'DAY HH24:MI:SS') LOGON_TIME, FLOOR(last_call_et/3600)||':'|| FLOOR(MOD(last_call_et,3600)/60)||':'|| MOD(MOD(last_call_et,3600),60) IDLE, program FROM v_$session WHERE username IS NOT NULL ORDER BY last_call_et;
User Information	col program format a17 SELECT sid, serial#, SUBSTR(username,1,10) NAME, SUBSTR(machine,1,10) COMPUTER, command, status, SUBSTR(osuser,1,8) OSUSER, process, program FROM v_$session ORDER BY name;