TABLESPACE QUOTA DETAILS OF ALL THE USERS:

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
  max_bytes!=0 
    or 
  bytes!=0 
order by 
  1,2 
/ 

TABLESPAE QUOTA DETAILS FOR A PARTICULAR USER:

set pagesize 200
set lines 200
col ownr format a20         justify c heading 'Owner' 
col name format a20         justify c heading 'Tablespace' trunc 
col qota format a12         justify c heading 'Quota (KB)' 
col used format 999,999,990 justify c heading 'Used (KB)' 
set colsep '|'
select 
  username          ownr, 
  tablespace_name   name, 
  decode(greatest(max_bytes, -1), 
    -1, 'UNLIMITED', 
    to_char(max_bytes/1024, '999,999,990') 
  )                 qota, 
  bytes/1024        used 
from 
  dba_ts_quotas 
where 
 ( max_bytes!=0 
    or 
  bytes!=0) and username='&USERNAME'
order by 
  1,2 
/ 

OUTPUT:

Enter value for username: AKHEHIFA
old  14:   bytes!=0) and username='&USERNAME'
new  14:   bytes!=0) and username='AKHEHIFA'

       Owner        |     Tablespace     | Quota (KB) | Used (KB)
--------------------|--------------------|------------|------------
AKHEHIFA            |USERS               |   1,048,576|           0

The post How to get tablespace quota details of an user in oracle appeared first on DBACLASS.

col username for a9
col sid for a8
set lines 299
select
      s.inst_id,
      decode(px.qcinst_id,NULL,s.username,
            ' - '||lower(substr(s.program,length(s.program)-4,4) ) ) "Username",
      decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
      to_char( px.server_set) "Slave Set",
      to_char(s.sid) "SID",
      decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
      px.req_degree "Requested DOP",
     px.degree "Actual DOP", p.spid
   from
     gv$px_session px,
     gv$session s, gv$process p
   where
     px.sid=s.sid (+) and
     px.serial#=s.serial# and
     px.inst_id = s.inst_id
     and p.inst_id = s.inst_id
     and p.addr=s.paddr
  order by 5 , 1 desc
/

The post How to monitor parallel queries in oracle db appeared first on DBACLASS.

COL local_tran_id FORMAT a13
COL in_out FORMAT a6
COL database FORMAT a25
COL dbuser_owner FORMAT a15
COL interface FORMAT a3
SELECT local_tran_id, in_out, database, dbuser_owner, interface
FROM dba_2pc_neighbors
/

The post Find pending distributed pending transactions in oracle appeared first on DBACLASS.

select a.instance_number inst_id, a.snap_id,a.plan_hash_value, to_char(begin_interval_time,'dd-mon-yy hh24:mi') btime, abs(extract(minute from (end_interval_time-begin_interval_time)) + extract(hour from (end_interval_time-begin_interval_time))*60 + extract(day from (end_interval_time-begin_interval_time))*24*60) minutes,
executions_delta executions, round(ELAPSED_TIME_delta/1000000/greatest(executions_delta,1),4) "avg duration (sec)" from dba_hist_SQLSTAT a, dba_hist_snapshot b
where sql_id='&sql_id' and a.snap_id=b.snap_id
and a.instance_number=b.instance_number
order by snap_id desc, a.instance_number;

The post How to find execution history of an sql_id appeared first on DBACLASS.

set lines 288
col sample_time for a14
col CONFIGURATION head "CONFIG" for 99.99
col ADMINISTRATIVE head "ADMIN" for 99.99
col OTHER for 99.99

SELECT TO_CHAR(SAMPLE_TIME, 'HH24:MI ') AS SAMPLE_TIME,
       ROUND(OTHER / 60, 3) AS OTHER,
       ROUND(CLUST / 60, 3) AS CLUST,
       ROUND(QUEUEING / 60, 3) AS QUEUEING,
       ROUND(NETWORK / 60, 3) AS NETWORK,
       ROUND(ADMINISTRATIVE / 60, 3) AS ADMINISTRATIVE,
       ROUND(CONFIGURATION / 60, 3) AS CONFIGURATION,
       ROUND(COMMIT / 60, 3) AS COMMIT,
       ROUND(APPLICATION / 60, 3) AS APPLICATION,
       ROUND(CONCURRENCY / 60, 3) AS CONCURRENCY,
       ROUND(SIO / 60, 3) AS SYSTEM_IO,
       ROUND(UIO / 60, 3) AS USER_IO,
       ROUND(SCHEDULER / 60, 3) AS SCHEDULER,
       ROUND(CPU / 60, 3) AS CPU,
       ROUND(BCPU / 60, 3) AS BACKGROUND_CPU
  FROM (SELECT TRUNC(SAMPLE_TIME, 'MI') AS SAMPLE_TIME,
               DECODE(SESSION_STATE,
                      'ON CPU',
                      DECODE(SESSION_TYPE, 'BACKGROUND', 'BCPU', 'ON CPU'),
                      WAIT_CLASS) AS WAIT_CLASS
          FROM V$ACTIVE_SESSION_HISTORY
         WHERE SAMPLE_TIME > SYSDATE - INTERVAL '2'
         HOUR
           AND SAMPLE_TIME <= TRUNC(SYSDATE, 'MI')) ASH PIVOT(COUNT(*)
  FOR WAIT_CLASS IN('ON CPU' AS CPU,'BCPU' AS BCPU,
'Scheduler' AS SCHEDULER,
'User I/O' AS UIO,
'System I/O' AS SIO,
'Concurrency' AS CONCURRENCY,
'Application' AS  APPLICATION,
'Commit' AS  COMMIT,
'Configuration' AS CONFIGURATION,
'Administrative' AS   ADMINISTRATIVE,
'Network' AS  NETWORK,
'Queueing' AS   QUEUEING,
'Cluster' AS   CLUST,
'Other' AS  OTHER))

OUTPUT WILL LOOK AS BELOW:

SAMPLE_TIME     OTHER      CLUST   QUEUEING    NETWORK  ADMIN CONFIG     COMMIT APPLICATION CONCURRENCY  SYSTEM_IO    USER_IO  SCHEDULER        CPU BACKGROUND_CPU
-------------- ------ ---------- ---------- ---------- ------ ------ ---------- ----------- ----------- ---------- ---------- ---------- ---------- --------------
10:13            1.60       .033          0          0    .00    .00        1.2           0        .017       .617       .017          0      2.567           .317
10:14            2.08       .033          0          0    .00    .00        1.9        .067           0       .783       .217          0      3.433           .417
10:15            2.83          0          0          0    .00    .00        1.6        .017        .033         .9         .5          0       4.05           .767
10:16            3.15        .05          0       .033    .00    .00      2.033           0        .033         .9       .617          0        4.7            .75
10:17            3.18          0          0       .017    .00    .00      1.883           0           0       .717       .467          0       4.65           .683
10:18            2.78          0          0          0    .00    .00      1.417           0           0        .65       .067          0      3.867            .65
10:19            3.30          0          0          0    .00    .00      1.233           0        .083       .533       .033          0      4.533             .9
10:20            4.18       .033          0       .033    .00    .00        1.3           0        .017       .933       .117          0        5.5           .817
10:21            2.85       .033          0          0    .00    .00       1.15           0           0       .783        .05          0        4.1            .75
10:22            3.32       .017          0       .017    .00    .00      4.133           0        .017         .9       .033          0      5.317          1.033

The post Script to get cpu usage and wait event information in oracle database appeared first on DBACLASS.

SCRIPT:

set pagesize 299
set lines 299
select STAT_NAME,to_char(VALUE) as VALUE  ,COMMENTS from v$osstat where stat_name  IN ('NUM_CPUS','NUM_CPU_CORES','NUM_CPU_SOCKETS')
union
select STAT_NAME,VALUE/1024/1024/1024 || ' GB'  ,COMMENTS from v$osstat where stat_name  IN ('PHYSICAL_MEMORY_BYTES')

OUTPUT:

STAT_NAME                        VALUE                                       COMMENTS
-------------------------------- ------------------------------------------- --------------------------------------------------------
NUM_CPUS                         256                                         Number of active CPUs
NUM_CPU_CORES                    32                                          Number of CPU cores
NUM_CPU_SOCKETS                  4                                           Number of physical CPU sockets
PHYSICAL_MEMORY_BYTES            255.5 GB                                    Physical memory size in bytes

The post How to find cpu and memory information of oracle database server appeared first on DBACLASS.

col STAT_NAME for a20
col VALUE_DIFF for 9999,999,999
col STAT_PER_MIN for 9999,999,999
set lines 200 pages 1500 long 99999999
col BEGIN_INTERVAL_TIME for a30
col END_INTERVAL_TIME for a30
set pagesize 40
set pause on


select hsys.SNAP_ID,
       hsnap.BEGIN_INTERVAL_TIME,
       hsnap.END_INTERVAL_TIME,
           hsys.STAT_NAME,
           hsys.VALUE,
           hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID) AS "VALUE_DIFF",
           round((hsys.VALUE - LAG(hsys.VALUE,1,0) OVER (ORDER BY hsys.SNAP_ID)) /
           round(abs(extract(hour from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))*60 +
           extract(minute from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME)) +
           extract(second from (hsnap.END_INTERVAL_TIME - hsnap.BEGIN_INTERVAL_TIME))/60),1)) "STAT_PER_MIN"
from dba_hist_sysstat hsys, dba_hist_snapshot hsnap
 where hsys.snap_id = hsnap.snap_id
 and hsnap.instance_number in (select instance_number from v$instance)
 and hsnap.instance_number = hsys.instance_number
 and hsys.STAT_NAME='user commits'
 order by 1;

OUTPUT:

   SNAP_ID BEGIN_INTERVAL_TIME            END_INTERVAL_TIME              STAT_NAME                 VALUE    VALUE_DIFF  STAT_PER_MIN
---------- ------------------------------ ------------------------------ -------------------- ---------- ------------- -------------
      6626 11-NOV-17 05.00.13.272 PM      11-NOV-17 06.00.29.527 PM      user commits          350001525     1,147,017        19,022
      6627 11-NOV-17 06.00.29.527 PM      11-NOV-17 07.00.14.759 PM      user commits          351130223     1,128,698        18,875
      6628 11-NOV-17 07.00.14.759 PM      11-NOV-17 08.00.02.845 PM      user commits          351987886       857,663        14,342
      6629 11-NOV-17 08.00.02.845 PM      11-NOV-17 09.00.22.109 PM      user commits          352829839       841,953        13,963
      6630 11-NOV-17 09.00.22.109 PM      11-NOV-17 10.00.07.076 PM      user commits          353478483       648,644        10,865
      6631 11-NOV-17 10.00.07.076 PM      11-NOV-17 11.00.24.303 PM      user commits          353939928       461,445         7,652
      6632 11-NOV-17 11.00.24.303 PM      12-NOV-17 12.00.11.904 AM      user commits          354335275       395,347         6,611
      6633 12-NOV-17 12.00.11.904 AM      12-NOV-17 01.00.29.406 AM      user commits          354604745       269,470         4,469
      6634 12-NOV-17 01.00.29.406 AM      12-NOV-17 02.00.17.332 AM      user commits          354955934       351,189         5,873
      6635 12-NOV-17 02.00.17.332 AM      12-NOV-17 03.00.03.228 AM      user commits          356918293     1,962,359        32,815
      6636 12-NOV-17 03.00.03.228 AM      12-NOV-17 04.00.20.577 AM      user commits          357821672       903,379        14,981
      6637 12-NOV-17 04.00.20.577 AM      12-NOV-17 05.00.09.204 AM      user commits          358154880       333,208         5,572
      6638 12-NOV-17 05.00.09.204 AM      12-NOV-17 06.00.25.507 AM      user commits          358296694       141,814         2,352
      6639 12-NOV-17 06.00.25.507 AM      12-NOV-17 07.00.09.734 AM      user commits          358692156       395,462         6,624
      6640 12-NOV-17 07.00.09.734 AM      12-NOV-17 08.00.01.047 AM      user commits          359373748       681,592        11,379
      6641 12-NOV-17 08.00.01.047 AM      12-NOV-17 09.00.17.981 AM      user commits          360418586     1,044,838        17,327
      6642 12-NOV-17 09.00.17.981 AM      12-NOV-17 10.00.04.542 AM      user commits          362476024     2,057,438        34,405
      6643 12-NOV-17 10.00.04.542 AM      12-NOV-17 11.00.22.732 AM      user commits          364469092     1,993,068        33,053
      6644 12-NOV-17 11.00.22.732 AM      12-NOV-17 12.00.09.693 PM      user commits          365611444     1,142,352        19,103
      6645 12-NOV-17 12.00.09.693 PM      12-NOV-17 01.00.27.672 PM      user commits          366866479     1,255,035        20,813
      6646 12-NOV-17 01.00.27.672 PM      12-NOV-17 02.00.14.537 PM      user commits          368466462     1,599,983        26,756

STAT_PER_MIN -Number of commits per minutes, during that snap time

The post Find user commits per minute in oracle database appeared first on DBACLASS.

col name format a10 
col username format a8 
col osuser format a8 
col start_time format a17 
col status format a12 
tti 'Active transactions' 

select s.sid,username,t.start_time, r.name, t.used_ublk "USED BLKS", 
decode(t.space, 'YES', 'SPACE TX', 
decode(t.recursive, 'YES', 'RECURSIVE TX', 
decode(t.noundo, 'YES', 'NO UNDO TX', t.status) 
)) status 
from sys.v_$transaction t, sys.v_$rollname r, sys.v_$session s 
where t.xidusn = r.usn 
and t.ses_addr = s.saddr 
/

The post Find the active transactions in oracle database appeared first on DBACLASS.

col program form a30 heading "Program" 
col CPUMins form 99990 heading "CPU in Mins" 
select rownum as rank, a.* 
from ( 
SELECT v.sid, program, v.value / (100 * 60) CPUMins 
FROM v$statname s , v$sesstat v, v$session sess 
WHERE s.name = 'CPU used by this session' 
and sess.sid = v.sid 
and v.statistic#=s.statistic# 
and v.value>0 
ORDER BY v.value DESC) a 
where rownum < 11;

The post Find sessions consuming lot of CPU appeared first on DBACLASS.

col sid format 999999 
col username format a20 
col osuser format a15 
select b.spid,a.sid, a.serial#,a.username, a.osuser 
from v$session a, v$process b 
where a.paddr= b.addr 
and b.spid='&spid' 
order by b.spid;

SEE ALSO – Get ospid from sid

The post Get sid from ospid appeared first on DBACLASS.