Oracle Queries for Java Developers

1) Connection count for all users 

select sum(case when status = 'ACTIVE' then 1 else 0 end) as ACTIVE_COUNT

     , sum(case when status = 'INACTIVE' then 1 else 0 end) as INACTIVE_COUNT

     , count(*) as TOTAL_COUNT

     , username, machine

from   v$session

where type <> 'BACKGROUND'

group by username, machine

order by username, machine

2) Session and Process count 

  select resource_name, current_utilization, max_utilization, limit_value

   from v$resource_limit

   where resource_name in ('sessions', 'processes');

3) Alter Session and Process Value

alter system set processes=500 scope=spfile;

alter system set sessions=1248 scope=spfile;

4) How to make changes in oracle DB 

Connect to SQL+ common prompt  

1) Command: connect sys as sysdba

you will be asked for password , now your logged in to the system shows prompt connected.

2) Grant privileges to user 

grant sysdba to system;   (system is the user)

3)   Alter user password or change password 

Alter user sys identified by new_password;   (sys is user and new_password is the password value)

4) Unlock a user account 

 Alter user system account unlock;    

5). Revoke rights from a user 

Revoke sysdba from system;