Managing Users

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;


Comments