Session monitoring

Active sessions usage by CPU

select
   ss.username,
   se.SID,
   VALUE/100 cpu_usage_seconds,
   ss.machine
from
   v$session ss,
   v$sesstat se,
   v$statname sn
where
   se.STATISTIC# = sn.STATISTIC#
and
   NAME like '%CPU used by this session%'
and
   se.SID = ss.SID
and
   ss.status='ACTIVE'
and
   ss.username is not null
order by VALUE desc

SELECT
   s.username,
   t.sid,
   s.serial#,
   SUM(VALUE/100) as "cpu usage (seconds)"
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND
   t.SID = s.SID
AND
   s.status='ACTIVE'
AND
   s.username is not null
GROUP BY username,t.sid,s.serial#

Top10 Active sessions

/* top10active.sql
shows the top 10 longest-active user sessions
*/
col osuser format a10 trunc
col LastCallET format 99,999
col sid format 9999
col spid formar 999999
col username format a10 trunc
col uprogram format a25 trunc
col machine format a10 trunc
set linesize 132
set verify off
select * from (
select to_char(s.logon_time, 'mm/dd hh:mi:ssAM') loggedon,
s.sid, s.status,
floor(last_call_et/60) "LastCallET",
s.username, s.osuser,
p.spid, s.module || ' - ' || s.program uprogram,
s.machine, s.sql_hash_value
from v$session s, v$process p
where p.addr = s.paddr
and s.type = 'USER'
and module is not null
and s.status = 'ACTIVE'
order by 4 desc)
where rownum < 11;

Session/SQL monitoring

SELECT SESION.SID,
       SESION.SERIAL#,
       SESION.USERNAME,
       OPTIMIZER_MODE,
       HASH_VALUE,
       ADDRESS,
       CPU_TIME/1000000000,
       ELAPSED_TIME/1000000000,
       DISK_READS,
       --DIRECT_WRITES,
       SQL_TEXT
  FROM V$SQLAREA SQLAREA, V$SESSION SESION
 WHERE SESION.SQL_HASH_VALUE = SQLAREA.HASH_VALUE
   AND SESION.SQL_ADDRESS    = SQLAREA.ADDRESS
   AND SESION.USERNAME IS NOT NULL
   AND ROWNUM < 100
   ORDER BY CPU_TIME, DISK_READS DESC,ELAPSED_TIME DESC;

Create Samba Share

## Install and configure samba shared folder ##

# install samba

yum install samba.x86_64

# Create samba user

smbpasswd -a username

# User that was used is from OS

# add shared folder to smb.cfg

vi /etc/samba/smb.conf

[folder]
path = /var/tmp/folder
available = yes
valid users = prodmgr
read only = no
browsable = yes
public = yes
writable = yes

# restart samba

service smb restart

Manipulate DB Links

create public database link mylink connect to remote_username identified by mypassword using 'tns_service_name';

create public database link mylink connect to remote_username identified by mypassword using 'myserver:1521/MYSID';

select * from dba_db_links;

drop database link <db_link>

FRA location/size query

SELECT
NAME,
TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,
TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE,
'999,999,999,999') AS SPACE_AVAILABLE,
ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)
AS PERCENT_FULL
FROM V$RECOVERY_FILE_DEST;

Copy existing VDI to specific SR

Show the disk you want to copy:

xe vdi-list name-label="VDI Label"
uuid ( RO) : 3f1c8982-78e8-4004-82de-7c8a961200e9
name-label ( RW): redologs
name-description ( RW):
sr-uuid ( RO): 632e5cac-45b6-0707-8d13-9bb47ab4f480
virtual-size ( RO): 10737418240
sharable ( RO): false
read-only ( RO): false

Copy disk to another SR

xe vdi-copy uuid=3f1c8982-78e8-4004-82de-7c8a961200e9 sr-uuid=<other SR UUID>