Some Useful Scripts on Session

Find Active Sessions in Oracle Database

SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time

FROM V$Session

WHERE

Status=‘ACTIVE’ AND

UserName IS NOT NULL;

How to check the maximum number of allowed connections to an Oracle database?

The number of sessions the database was configured to allow

SELECT name, value

 FROM v$parameter

 WHERE name = 'sessions'

The number of sessions currently active

SELECT COUNT(*)

 FROM v$session

Identify the session which is locking the object

select a.sid, a.serial#

from v$session a, v$locked_object b, dba_objects c

where b.object_id = c.object_id

and a.sid = b.session_id

and OBJECT_NAME='EMP';

Oracle: Total Size of The Database

An oracle database consists of data files, redo log files, control files, temporary files.

The size of the database actually means the total size of all these files.

select

( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +

( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +

( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +

( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"

from

dual ;