/*TO KNOW WHICH THE PERCENTAGE OF PARTICIPATION OF EACH USER ON THE DATABASE AT THE ACCURATE MOMENT WHERE IF IT EXECUTES THIS SCRIPT. GENERATING AN ARCHIVE WITH A FIXED NAME MORE THE DATE AND HOUR OF CREATION ALSO BEING PART OF THE NAME OF THE ARCHIVE SO THAT IT IS PREVENTED THAT THE SAME HE IS EXTINGUISHED. THUS GENERATING A LOG OF INFORMATION ON THE USERS WHO MORE CONSUME FEATURES OF THE ORACLE.
AS EXAMPLE A BILLING SYSTEM WAS USED THAT USES TUXEDO TO TWIRL PART OF THE
APPLICATORY ONE. WE MUST IGNORE THE TUXEDO USER AND THEN TO ONLY ENTER THE EXCESSIVELY USING ONES.
*/



set linesize 160 pages 76

col user format a20
col consistent_changes format 99999999999 heading 'consistent|changes'
col user_os format a20 heading 'User|Operational|Sistem'
col hora format a28
col process format 99,999,999.99 heading 'Process'
col participation format 999.99 heading '% | Participation'

col dthori noprint new_value dthore
rem it catches the date and current hour
select to_char(sysdate,'mmddhh24mi') dthori
from dual;
rem generates of log FILE having as part of the name the date and hour
spool c:\hiran\temp\monispo&dthore..log
select to_char(sysdate,'dd-mon-rrrr - hh24:mi:ss') hora from dual;
select instance from v$thread;
rem Definition of variable of substitution
col totproi noprint new_value totproe;
rem Storage of the total in the using variable of substitution where < > tux (tuxedo)
rem
select sum
((b.block_gets+b.physical_reads+b.block_changes+b.consistent_changes)/4) totproi
from v$session a, v$sess_io b
where a.sid=b.sid
and a.osuser not like 'tux%';
rem Upgrade of the column participation that the percentage calculates
REM and does not make it for the user ' tux ' (tuxedo)
rem
ttitle 'report of consumption'
select
a.osuser user_os,
a.username,
a.terminal,
sum(b.physical_reads) physical_reads,
sum(b.block_gets) block_gets,
sum(b.block_changes) block_changes,
sum(b.consistent_changes) consistent_changes,
sum((b.block_gets+b.physical_reads+b.block_changes+b.consistent_changes)/4)
process,
(sum((b.block_gets+b.physical_reads+b.block_changes+b.consistent_changes)/4)/
decode(substr(a.osuser,1,3),'tux',null,&totproe))*100 participation
from v$session a, v$sess_io b
where a.sid=b.sid
Group by a.osuser,
a.username,
a.terminal
order by 8 desc;
spool off;