Cacher is the code snippet organizer for pro developers

set lines 200
col username format a40
SELECT SID, SERIAL#, USERNAME, STATUS, SQL_ID, MACHINE, PROCESS FROM V$SESSION WHERE username IS NOT NULL;

ALTER SYSTEM DISCONNECT SESSION  '(SID),(SERIAL#)'  IMMEDIATE ;
ALTER SYSTEM KILL SESSION '<sid>,<serial#>' IMMEDIATE;

select 
   current_utilization, limit_value 
from 
   v$resource_limit 
where 
   resource_name='sessions';

select username, count(1) as connectionCount from V$SESSION where username is not null group by username order by connectionCount;

select username, count(1) as connectionCount from V$SESSION where username is not null group by username having count(1) > 250 AND count(1) <280;

select username, count(1) as connectionCount from V$SESSION where username is not null group by username having count(1) > 280;

set lines 200
col resource_name format a20
select resource_name, current_utilization, max_utilization, limit_value 
from v$resource_limit 
where resource_name in ('sessions', 'processes');

set lines 200
col username format a20
SELECT se.username, ss.sid, ROUND (value/100) "CPU Usage"
FROM v$session se, v$sesstat ss, v$statname st
WHERE ss.statistic# = st.statistic#
AND name LIKE  '%CPU used by this session%'
AND se.sid = ss.SID 
AND se.username IS NOT NULL
ORDER BY value asc;

SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN username FORMAT A30
COLUMN sid FORMAT 999,999,999
COLUMN serial# FORMAT 999,999,999
COLUMN "cpu usage (seconds)"  FORMAT 999,999,999.0000
 
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#
/

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' 
FROM v$session where username not like '%SYS%';

1/Droit de lister les sessions actives/inactives/locks
2/Droit de killer les locks
3/Droit d'avoir la taille du schéma/table
Est-il possible de créer un utilisateur équivalent avec le même droit sur les DB DSACNT0 (DEV), TSACNT0 (TST), MSACNT0 (PREPROD) ? Mot de passe à m'envoyer par sms (0609479792)

1/ 
GRANT SELECT ON "SYS"."V_$SESSION" TO "TSN_ACN";
GRANT SELECT ON "SYS"."V_$LOCK" TO "TSN_ACN";

2/
CREATE OR REPLACE PROCEDURE sys.sp_kill_session_TSND(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user LIKE ('TSND_%') THEN
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_session_TSND;
/

GRANT EXECUTE ON sys.sp_kill_session_TSND TO TSN_ACN;

# Pour l'utiliser : 
exec sys.sp_kill_session_TSND(SID,SERIAL#)

3/
GRANT SELECT ON sys.dba_segments TO TSN_ACN;

 SET LINES 500;
SELECT parsing_schema_name,
       inst_id,
       sql_id,
       plan_hash_value,
       FIRST_LOAD_TIME,
       TO_CHAR (last_active_time, 'DD/MM/YY HH24:MI:SS') AS last_active_time,
       executions,
       elapsed_time / executions / 1000 / 1000,
       rows_processed,
	   LOCKED_TOTAL
  FROM gv$sql
 WHERE sql_id IN ('b1kuh1ptmmx7k');

SELECT s.username, COUNT(p.spid) AS process_count
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
GROUP BY s.username
ORDER BY process_count DESC;