-- Check Memory Allocation Per User/Process
Memory Allocation Per Process
Memory Allocation Per User
session wise memory
session memory
set linesize 150
SET PAGESIZE 9999
COLUMN sid_serial format a12 HEADING 'sid_serial#'
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN SERIAL# FORMAT 99999 HEADING 'SERIAL#'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY left
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a12 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
set linesize 150
SELECT
-- s.sid
s.sid||','||SERIAL# sid_serial
, lpad(s.username,12) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,12) session_machine
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select round(sum(ss.value/1024/1024)) from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
sid_serial# Oracle User O/S User Session Program Machine PGA Memory PGA Memory Max UGA Memory UGA Memory MAX
------------ ------------ --------- ------------------ ------------ -------------- -------------- -------------- --------------
21,2 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
23,1 oracle oracle@apt-amd-02 apt-amd-02 28 28 0 0
19,5 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
22,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
11,1 oracle oracle@apt-amd-02 apt-amd-02 11 11 0 0
10,1 oracle oracle@apt-amd-02 apt-amd-02 7 32 0 0
15,1 oracle oracle@apt-amd-02 apt-amd-02 3 4 1 2
17,10 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 2 0 1
13,1 oracle oracle@apt-amd-02 apt-amd-02 2 4 1 3
1,9 SYS oracle rman@apt-amd-02 (T apt-amd-02 2 24 1 3
56,622 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 2 0 0
62,47 SYS oracle sqlplus@apt-amd-02 apt-amd-02 2 14 1 6
32,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
34,3 oracle oracle@apt-amd-02 apt-amd-02 1 5 1 2
40,15 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
60,443 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
30,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 1 1
27,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
16,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
14,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 1
12,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
9,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
8,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
7,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
6,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
5,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
4,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
3,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
2,1 oracle oracle@apt-amd-02 apt-amd-02 1 1 0 0
29 rows selected.
SET LINESIZE 300 PAGESIZE 9999
COLUMN sid FORMAT 99999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a35 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT /*+ PARALLEL(8) */
s.sid sid
, s.sql_id
, PREV_SQL_ID
,SQL_EXEC_ID
, lpad(s.username,20) oracle_username
, lpad(s.osuser,9) os_username
, s.program session_program
, lpad(s.machine,20) session_machine
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory
, (select ss.value from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
Thursday, 5 January 2012
Oracle Memory Allocation Per User / Process
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value
Oracle Date How to Add Day , Hour, Minute, Second to a Date Value in Oracle
Oracle add Days
Oracle add Hour
Oracle add Minute
Oracle add second
-- # Add a day
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1,'dd-mm-yyyy hh24:mi:ss') One_day from dual ;
Todays-date ONE_DAY
------------------- -------------------
05-01-2012 12:55:52 06-01-2012 12:55:52
-- # Add an hour
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/24,'dd-mm-yyyy hh24:mi:ss') One_hour from dual ;
Todays-date ONE_HOUR
------------------- -------------------
05-01-2012 12:56:06 05-01-2012 13:56:06
-- # Add an Minute
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60),'dd-mm-yyyy hh24:mi:ss') One_minute from dual ;
Todays-date ONE_MINUTE
------------------- -------------------
05-01-2012 12:56:18 05-01-2012 12:57:18
-- # Add Second
select to_char(sysdate,'dd-mm-yyyy hh24:mi:ss') "Todays-date" , to_char(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss') One_second from dual ;
Todays-date ONE_SECOND
------------------- -------------------
05-01-2012 12:56:27 05-01-2012 12:56:28
if we want to insert into the table then
to_date(sysdate +1/(24*60*60),'dd-mm-yyyy hh24:mi:ss')
or
sysdate +1/(24*60*60)
or
SQL> select to_char(sysdate,'DD-MM-YYYY HH:MI:SS'), to_char(sysdate+interval '15' minute,'DD-MM-YYYY HH:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD TO_CHAR(SYSDATE+INT
------------------- -------------------
05-01-2012 04:36:39 05-01-2012 04:51:39
Subscribe to:
Posts (Atom)
