Search This Blog

Total Pageviews

Thursday 5 January 2012

Oracle Memory Allocation Per User / Process






-- 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
/

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



 


 

Oracle DBA

anuj blog Archive