Oracle : Script which shows who owns which objects and where

select
  substrb(decode(grouping(owner),1,'{all}',owner),1,10) OWNER,
  substrb(decode(grouping(tablespace_name),1,'{all}',tablespace_name),1,13) TS_NAME,
  substrb(decode(floor(sum(bytes)/1073741824),0,
    decode(floor(sum(bytes)/1048576),0,
      to_char(sum(bytes)/1024,'9999')||'K',
      to_char(sum(bytes)/1048576,'9999')||'M'),
      to_char(sum(bytes)/1073741824,'99.9')||'G'),2,6) "SIZE",
  to_char(sum(decode(segment_type,'TABLE',1,0)),'999999') TAB,
  to_char(sum(decode(segment_type,'INDEX',1,0)),'999999') IDX,
  substrb(to_char(sum(decode(segment_type,'ROLLBACK',1,0)),'999'),2,3) ROL,
  substrb(to_char(sum(decode(segment_type,'TEMPORARY',1,0)),'999'),2,3) TMP,
  substrb(to_char(sum(decode(segment_type,'CACHE',1,0)),'999'),2,3) CAC,
  substrb(to_char(sum(decode(segment_type,'CLUSTER',1,0)),'999'),2,3) CLU,
  substrb(to_char(sum(decode(substrb(segment_type,1,3),'LOB',1,0)),'999'),2,3) LOB,
  to_char(count(*),'9999999') TOTAL
from dba_segments
group by rollup( owner, tablespace_name )
order by 1,2,3 desc,4 desc
/

Oracle : Script shows object locks currently held in the database

select /*+ rule */
substrb(vlo.oracle_username,1,10) db_user,
substrb(vlo.os_user_name,1,10) os_user,
substrb(to_char(vlo.session_id),1,4) sid,
decode(vs.lockwait,null,substrb(vs.status,1,3),'WAI') "STA",
substrb(dbo.owner,1,10) owner,
substrb(dbo.object_name,1,25) obj_name,
substrb(dbo.object_type,1,3) typ,
substrb(decode(locked_mode,0,'NONE',1,'NULL',2,'ROWS',3,'ROWX',
  4,'SHAR',5,'SRWX',6,'EXCL',to_char(vlo.locked_mode)),1,4) "MODE"
from v$locked_object vlo, v$session vs, dba_objects dbo
where vlo.session_id = vs.sid
and vs.type != 'BACKGROUND'
and vlo.object_id = dbo.object_id
order by decode(vs.lockwait,null,decode(vs.status,'ACTIVE',2,3),1),3,1
/

Oracle : Script shows all latches held in the database

select
substrb(vs.username,1,10) db_user,
substrb(vs.osuser,1,10) os_user,
substrb(vs.terminal,1,8) terminal,
decode(vs.lockwait,null,substrb(vs.status,1,3),'WAI') "STA",
substrb(vs.server,1,1)||substrb(to_char(vs.sid),1,4) sid,
substrb(to_char(vs.serial#),1,4) srl,
substrb(vp.spid,1,8) spid,
substrb(vl.name,1,21) latch
from v$session vs, v$process vp, v$latchholder vl
where vs.sid = vl.sid
and vs.type = 'USER'
and vs.paddr = vp.addr(+)
/

Script to look at session wait to see who is waiting on latches
=======================================================================

select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(vs.username,1,12) db_user,
  decode(vs.lockwait,null,
    decode(vp.latchwait,null,
      decode(vp.latchspin, null,
        substrb(vs.status,1,3),
        'SPN'),
      'LAT'),
    'LCK') STA,
  substrb(to_char(vs.sid),1,4) sid,
  substrb(vp.spid,1,8) spid,
  vsw.p1raw LATCH_ADDR,
  vs.sql_address SQL_ADDR,
  vs.sql_hash_value SQL_HASH_VALUE,
  to_char(vsw.seconds_in_wait,'9999') TIME,
  substrb(vlc.name,1,30) LATCH_NAME,
  substrb(vlc.child#,1,5) CHILD,
  to_char(vlc.gets,'99999999') "GETS",
--  to_char(vlc.misses,'999999') "MISSES",
  to_char(vlc.sleeps,'999999') "SLEEPS"
from v$session_wait vsw, v$latch_children vlc, v$session vs, v$process vp
where vsw.p1raw = vlc.addr
and vsw.sid = vs.sid
and vs.paddr = vp.addr
and vsw.event like 'latch%'
union
select /*+ RULE */
  substrb(vs.username,1,12),
  decode(vs.lockwait,null,
    decode(vp.latchwait,null,
      decode(vp.latchspin, null,
        substrb(vs.status,1,3),
        'SPN'),
      'LAT'),
    'LCK') STA,
  substrb(to_char(vs.sid),1,4),
  substrb(vp.spid,1,8),
  vsw.p1raw,
  vs.sql_address,
  vs.sql_hash_value SQL_HASH_VALUE,
  to_char(vsw.seconds_in_wait,'9999'),
  substrb(vl.name,1,30),
  'N/A',
  to_char(vl.gets,'99999999'),
--  to_char(vl.misses,'999999'),
  to_char(vl.sleeps,'999999')
from v$session_wait vsw, v$latch vl, v$session vs, v$process vp
where vsw.p1raw = vl.addr
and vsw.sid = vs.sid
and vs.paddr = vp.addr
and vsw.event like 'latch%'
order by 5,6
/

Oracle : Script shows the last call for each session

select /*+ RULE CURSOR_SHARING_EXACT */
  substrb(to_char(vss.sid),1,4) sid,
  decode(vss.lockwait,null,substrb(vss.status,1,3),'WAI') "STA",
  lpad(substrb(decode(trunc(vss.last_call_et/86400),0,
  to_char(to_date(vss.last_call_et,'SSSSS'),'HH24:MI:SS'),
  to_char(trunc(vss.last_call_et/86400),'B999999')),1,10),8) "LC-D/HMS",
  vsa.address sql_addr,
  substrb(vsa.sql_text,1,45) sql_text
from v$session vss, v$sqlarea vsa
where vss.type != 'BACKGROUND'
and vss.sql_address = vsa.address(+)
--and to_char(vss.sid) like '%$1'
union all
select
  substrb(to_char(vss.sid),1,4) sid,
  decode(vss.lockwait,null,substrb(vss.status,1,3),'WAI') "STA",
  lpad(substrb(decode(trunc(vss.last_call_et/86400),0,
  to_char(to_date(vss.last_call_et,'SSSSS'),'HH24:MI:SS'),
  to_char(trunc(vss.last_call_et/86400),'B999999')),1,10),8) "LC-D/HMS",
  vsa.address sql_addr,
  substrb(vsa.sql_text,1,45) sql_text
from v$session vss, v$sqlarea vsa
where vss.prev_sql_addr != '00'
and vss.type != 'BACKGROUND'
and vss.prev_sql_addr = vsa.address(+)
--and to_char(vss.sid) like '%$1'
order by 2, 1
/

oracle : Script to show the database hot spots

select
substrb(decode(ascii(substrb(vdf.name,21,1)),NULL,vdf.name,
  substrb(vdf.name,1,6)||'..'||
  substrb(vdf.name,greatest(length(vdf.name)-12,0),13)),1,21) "DB_FILE",
to_char(xbh.dbablk,'999999') "SGABLK",
substrb(dbo.owner,1,10) "OWNER",
substrb(dbo.object_name,1,20) "OBJ_NAME",
substrb(dbo.object_type,1,3) "TYP",
to_char((vlc.gets/greatest(vlc.gets+vlc.misses,1))*100,'99.99')||'%' "HIT_RT"
--,to_char(xbh.tch,'99999') "TOUCH"
from sys.x$bh xbh, v$datafile vdf, dba_objects dbo, v$latch_children vlc
where xbh.file# = vdf.file#
and xbh.obj = dbo.object_id
and xbh.hladdr = vlc.addr
and vlc.name = 'cache buffers chains'
and (vlc.misses/(greatest(vlc.gets+vlc.misses,1))>0.005)
order by 6, 3, 4
/

Oracle : Script shows objects that were read by a full table scan

select /*+ RULE CURSOR_SHARING_EXACT */
  to_char(count(1),'999,999') "BLOCKS",
  substrb(ds.buffer_pool,1,8) "POOL",
  substrb(u.name,1,10) "OWNER",
  substrb(o.name,1,30) "OBJ_NAME",
  decode(o.type#,1,'IND',2,'TAB','OTH') "TYP",
  substr(ds.tablespace_name,1,15) "TS_NAME"
from obj$ o,x$bh x, user$ u, dba_objects dbo, dba_segments ds
where x.obj = o.obj#
--and o.type# = 2
and bitand(x.flag,524288) > 0
and o.owner# = u.user#
and u.name <> 'SYS'
and o.obj# = dbo.object_id
and dbo.owner = ds.owner
and dbo.object_name = ds.segment_name
and dbo.object_type = ds.segment_type
having count(*) > 5
group by u.name, o.name, o.type#, ds.tablespace_name, ds.buffer_pool
order by 1 desc
/

Oracle : Script to check whats executing in the database right now

select /*+ RULE CUSROR_SHARING_EXACT */
  substrb(vs.username,1,10) db_user,
  substrb(to_char(vs.sid),1,4) sid,
  decode(bitand(vt.flag,4194304),0,'LOCAL','REMOTE') "LOCAL",
  decode(bitand(vt.flag,268435456),0,'RDC','SRL') "ISO",
  substrb(to_char(vt.log_io+vt.phy_io,'999,999,999'),2,7) totl_io,
  substrb(to_char(vt.log_io/greatest((vt.log_io+vt.phy_io),1)*100,'999.99')  ||'%',2,7) hit_rt,
  to_char(nvl(vt.used_ublk,0),'999,999') undo,
  lpad(substrb(decode(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),0,
    to_char(to_date(trunc(86400*abs(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS'))),'SSSSS'),'HH24:MI:SS'),
    to_char(trunc(sysdate-to_date(vt.start_time,'MM/DD/YY HH24:MI:SS')),'B999999')),1,10),8) "ST-D/HMS",
  vs.sql_address sql_addr
from v$session vs, v$transaction vt
where vs.type != 'BACKGROUND'
and vs.taddr = vt.addr
order by 5 desc, 3 desc
/

Oracle : Waitevents

select
substrb(vse.event,1,40) EVENT,
to_char(average_wait,'999,999,999.99') AVG_WAIT_SECS,
to_char(time_waited,'999,999,999') TIME_WAITED
from v$system_event vse
where vse.event != 'Null event'
and vse.event not like '%timer%'
and vse.event not like 'SQL*Net%'
and 15 > (
  select count(*) from v$system_event vse2
  where vse2.time_waited > vse.time_waited
  and vse2.event != 'Null event'
  and vse2.event not like '%timer%'
  and vse2.event not like 'SQL*Net%' )
order by vse.time_waited desc
/

Oracle : Script shows the enqueue waits in the database

select /*+ RULE CURSOR_SHARING_EXACT */
  to_char(inst_id,'999') "INS",
  ksqsttyp "TYP",
  decode(
    ksqsttyp,
    'CI','Cross Instance - initiation of processes on other instances',
    'CF','Controlfile - contention for controlfile',
    'CU','Cursor Binding',
    'HW','High Watermark - manually add extents',
    'RO','Releasable Object - contention during delete/truncate',
    'ST','Space Management - use LMTs or incr. next extent',
    'TM','Trans Mgmt - check FK idx',
    'TX','Trans Enq - incr. init/max trans or more commits',
    null) "ACTION",
  to_char(sum(ksqstwat),'999,999') "WAITS"
--  , to_char(sum(ksqstget),'999,999,999') "GETS"
from X$KSQST
where ksqstwat > 0
group by inst_id, ksqsttyp
order by 4 desc
/

Oracle : Script to give a quick overveiw of the Instance

select /*+ RULE CURSOR_SHARING_EXACT */
   substrb(max(name),1,6) NAME,
   substrb(max(version),1,9) VERSION,
   substrb(to_char(max(startup_time),'DD-MON-YY'),1,9) STARTED,
   substrb(decode(max(log_mode),'NOARCHIVELOG','NOARCH','ARCH'),1,5) LOGMD,
   decode(max(archiver),'STOPPED','NO','YES') ARC,
   decode(max(logins),'ALLOWED','NO','YES') RM,
   substrb(max(parallel),1,3) OPS,
   to_char(max(E4031),'9999') "4031",
   substrb(decode(floor(max(pool_mb)/1073741824),0,
     decode(floor(max(pool_mb)/1048576),0,
       to_char(max(pool_mb)/1024,'9999')||'K',
       to_char(max(pool_mb)/1048576,'9999')||'M'),
     to_char(max(pool_mb)/1073741824,'99.9')||'G'),2,6) POOL,
   substrb(decode(floor(max(dbf_mb)/1073741824),0,
     decode(floor(max(dbf_mb)/1048576),0,
       to_char(max(dbf_mb)/1024,'9999')||'K',
       to_char(max(dbf_mb)/1048576,'9999')||'M'),
     to_char(max(dbf_mb)/1073741824,'99.9')||'G'),2,6) CACH,
   substrb(decode(floor(max(log_kb)/1073741824),0,
     decode(floor(max(log_kb)/1048576),0,
       to_char(max(log_kb)/1024,'9999')||'K',
       to_char(max(log_kb)/1048576,'9999')||'M'),
     to_char(max(log_kb)/1073741824,'99.9')||'G'),2,6) LOG,
   substrb(decode(floor(max(pga_sz)/1073741824),0,
     decode(floor(max(pga_sz)/1048576),0,
       to_char(max(pga_sz)/1024,'9999')||'K',
       to_char(max(pga_sz)/1048576,'9999')||'M'),
     to_char(max(pga_sz)/1073741824,'99.9')||'G'),2,6) PGA,
   substrb(decode(floor(max(jav_sz)/1073741824),0,
     decode(floor(max(jav_sz)/1048576),0,
       to_char(max(jav_sz)/1024,'9999')||'K',
       to_char(max(jav_sz)/1048576,'9999')||'M'),
     to_char(max(jav_sz)/1073741824,'99.9')||'G'),2,6) JAVA from (
   select
     vd.name name,
     vi.version version,
     vi.startup_time startup_time,
     vd.log_mode log_mode,
     vi.archiver archiver,
     vi.logins logins,
     vi.parallel parallel,
     xk.kghlunfu e4031,
     vp1.value POOL_MB,
     to_number(vp2.value*vp3.value) DBF_MB,
     vp4.value LOG_KB,
     vp5.value PGA_SZ,
     '0' JAV_SZ
   from v$database vd, v$instance vi, sys.x$kghlu xk, v$parameter vp1,
     v$parameter vp2, v$parameter vp3, v$parameter vp4, v$parameter vp5
   where vp1.name = 'shared_pool_size'
   and vp2.name = 'db_block_buffers'
   and vp2.value != '0'
   and vp3.name = 'db_block_size'
   and vp4.name = 'log_buffer'
   and vp5.name = 'sort_area_size'
   union all
   select
     vd.name,
     vi.version,
     vi.startup_time,
     vd.log_mode,
     vi.archiver archiver,
     vi.logins logins,
     vi.parallel parallel,
     xk.kghlunfu e4031,
     vp1.value POOL_MB,
     to_number(vp2.value) DBF_MB,
     vp4.value LOG_KB,
     vp5.value PGA_SZ,
     vp6.value JAV_SZ
   from v$database vd, v$instance vi, sys.x$kghlu xk, v$parameter vp1,
     v$parameter vp2, v$parameter vp4, v$parameter vp5, v$parameter vp6
   where vp1.name = 'shared_pool_size'
   and vp2.name = 'db_cache_size'
   and vp4.name = 'log_buffer'
   and vp5.name = 'pga_aggregate_target'
   and vp6.name = 'java_pool_size' )
 /

Oracle : Script which gives the CPU Performance Information

select 'recursive cpu:'||chr(9)||trunc(rcpu.value/tcpu.value*100,2)||'%' "METRIC"
from v$sysstat rcpu, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'recursive cpu usage' and vn1.statistic# = rcpu.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'parse cpu:'||chr(9)||trunc(pcpu.value/tcpu.value*100,2)||'%'
from v$sysstat pcpu, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'parse time cpu' and vn1.statistic# = pcpu.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'other cpu:'||chr(9)||trunc((tcpu.value-rcpu.value-pcpu.value)/tcpu.value*100,2)||'%'
from v$sysstat rcpu, v$statname vn1, v$sysstat pcpu, v$statname vn2, v$sysstat tcpu, v$statname vn3
where vn1.name = 'recursive cpu usage' and vn1.statistic# = rcpu.statistic#
and vn2.name = 'parse time cpu' and vn2.statistic# = pcpu.statistic#
and vn3.name = 'CPU used by this session' and vn3.statistic# = tcpu.statistic#
union all
select 'cpu/execute:'||chr(9)||trunc(tcpu.value/exct.value,2)||' hsecs'
from v$sysstat exct, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'execute count' and vn1.statistic# = exct.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'cpu/call:'||chr(9)||trunc(tcpu.value/ucals.value,2)||' hsecs'
from v$sysstat ucals, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'user calls' and vn1.statistic# = ucals.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
union all
select 'cpu/commit:'||chr(9)||trunc(tcpu.value/ucmts.value,2)||' hsecs'
from v$sysstat ucmts, v$statname vn1, v$sysstat tcpu, v$statname vn2
where vn1.name = 'user commits' and vn1.statistic# = ucmts.statistic#
and vn2.name = 'CPU used by this session' and vn2.statistic# = tcpu.statistic#
/

Oracle : Script which shows vital statistics for whole database

select /*+ RULE CURSOR_SHARING_EXACT */
   'DB BLOCK BUFFER (TOAD)' DB_RESOURCE,
   to_char(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)),'999,999,999,999') HIT,
   to_char(sum(decode(name,'physical reads',value,0)),'999,999,999,999') MISS,
   to_char((sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)))/(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0))+sum(decode(name,'physical reads',value,0)))*100,'999.99')||'%' RATIO
 from v$sysstat
 union all
 select
   'LIBRARY GETS',
   to_char(sum(gets),'999,999,999,999'),
   to_char(sum(gets)-sum(gethits),'999,999,999,999'),
   to_char(sum(gets)/(sum(gets)+(sum(gets)-sum(gethits)))*100,'999.99')||'%'
 from v$librarycache
 union all
 select
   'LIBRARY PINS',
   to_char(sum(pinhits),'999,999,999,999'),
   to_char(sum(pins)-sum(pinhits),'999,999,999,999'),
   to_char(sum(pinhits)/(sum(pinhits)+(sum(pins)-sum(pinhits)))*100,'999.99')||'%'
 from v$librarycache
 union all
 select
   'DICTIONARY',
   to_char(sum(gets)-sum(getmisses),'999,999,999,999'),
   to_char(sum(getmisses),'999,999,999,999'),to_char(sum(gets)/(sum(gets)+sum(getmisses))*100,'999.99')||'%'
 from v$rowcache
 union all
 select
   'INDEX UTILIZATION',
   to_char(sum(decode(name,'table fetch by rowid',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'table scan rows gotten',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'table fetch by rowid',value,0))/(sum(decode(name,'table fetch by rowid',value,0))+sum(decode(name,'table scan rows gotten',value,0)))*100,'999.99')||'%'
 from v$sysstat
 union all
   select
   'REDOLOG WRITES',
   to_char(sum(decode(name,'redo writes',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'redo log space requests',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'redo writes',value,0))
   /(greatest(1,sum(decode(name,'redo writes',value,0))+sum(decode(name,'redo log space requests',value,0))))*100,'999.99')||'%'
 from v$sysstat
 union all
 select
   'MEMORY SORTS',
   to_char(sum(decode(name,'sorts (memory)',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'sorts (disk)',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'sorts (memory)',value,0))/(sum(decode(name,'sorts (memory)',value,0))+sum(decode(name,'sorts (disk)',value,0)))*100,'999.99')||'%'
 from v$sysstat
 union all
 select
   'CURSOR REUSE',
   to_char(count(*),'999,999,999,999'),
   to_char(sum(decode(executions,0,0,1,0,1)),'999,999,999,999'),
   to_char((sum(decode(executions,0,0,1,0,1))/count(*))*100,'999.99')||'%'
 from v$sqlarea
 union all
 select
   'PARSE/EXEC',
   to_char(vss1.value,'999,999,999,999'),
   to_char(vss2.value,'999,999,999,999'),
   to_char(100*(1-vss1.value/vss2.value),'999.99')||'%'
 from v$sysstat vss1, v$sysstat vss2
 where vss1.name = 'parse count (total)'
 and vss2.name = 'execute count'
 union all
 select
   'PARSE HARD/TOT',
   to_char(vss1.value,'999,999,999,999'),
   to_char(vss2.value,'999,999,999,999'),
   to_char((vss1.value/vss2.value)*100,'999.99')||'%'
 from v$sysstat vss1, v$sysstat vss2
 where vss1.name = 'parse count (hard)'
 and vss2.name = 'parse count (total)'
 union all
 select
   'DB BLOCK MISS RATIO',
   to_char(sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0)),'999,999,999,999'),
   to_char(sum(decode(name,'physical reads',value,0)),'999,999,999,999'),
   to_char(
     (sum(decode(name,'physical reads',value,0))/
     (sum(decode(name,'db block gets',value,0))+sum(decode(name,'consistent gets',value,0))))*100
   ,'999.99')||'%' RATIO
 from v$sysstat
 union all
 select
   'DB BLOCK BUFFER',
   to_char(vss2.value,'999,999,999,999'),
   to_char(vss1.value,'999,999,999,999'),
   to_char(100*(1-vss1.value/vss2.value),'999.99')||'%'
 from v$sysstat vss1, v$sysstat vss2
 where vss1.name = 'physical reads'
 and vss2.name = 'consistent gets'
 order by 4 asc
 /

Oracle : Script which shows blocking sessions and waiters

select /*+ RULE CURSOR_SHARING_EXACT */
   substrb(vs1.username,1,10) "DB_USER_BL",
   decode(vs1.lockwait,null,substrb(vs1.status,1,3),'WAI') "STA",
   substrb(ltrim(to_char(vs1.inst_id,'9')||substrb(vs1.server,1,1)||substrb(to_char(vs1.sid),1,4)),1,6) "I-SID",
   substrb(to_char(vs1.serial#),1,4) "SRL",
   substrb(vp1.spid,1,8) spid,
   substrb(vs2.username,1,10) "DB_USER_WA",
   decode(vs2.lockwait,null,substrb(vs2.status,1,3),'WAI') "STA",
   substrb(ltrim(to_char(vs2.inst_id,'9')||substrb(vs2.server,1,1)||substrb(to_char(vs2.sid),1,4)),1,6) "I-SID",
   substrb(to_char(vs2.serial#),1,4) "SRL",
   substrb(vp2.spid,1,8) spid,
   lpad(substrb(decode(trunc(vl2.ctime/86400),0,
     to_char(to_date(vl2.ctime,'SSSSS'),'HH24:MI:SS'),
     to_char(trunc(vl2.ctime/86400),'B999999')),1,10),8) "WT-D/HMS"
 from gv$session vs1, gv$session vs2, gv$process vp1, gv$lock vl1, gv$lock vl2, gv$process vp2
 where vl1.block != 0
 and vl1.sid = vs1.sid
 and vl1.inst_id = vs1.inst_id
 and vl1.id1 = vl2.id1
 and vs1.paddr = vp1.addr(+)
 and vs1.inst_id = vp1.inst_id(+)
 and vl2.block = 0
 and vl2.sid = vs2.sid
 and vl2.inst_id = vs2.inst_id
 and vs2.paddr = vp2.addr(+)
 and vs2.inst_id = vp2.inst_id(+)
 /

Oracle : Script show which objects have auditing turned on

select
     substrb(owner,1,9) OWNER,
     substrb(object_name,1,14) OBJ_NAME,
     substrb(object_type,1,3) TYP,
     alt,aud,com,del,gra,ind,ins,loc,ren,sel,upd,ref,exe
     from dba_obj_audit_opts
     where
     greatest(alt,greatest(aud,greatest(com,greatest(del,greatest(gra,greatest(
     ind,greatest(ins,greatest(loc,greatest(ren,greatest(sel,greatest(
     upd,greatest(ref,exe)))))))))))) > '-/-'
     order by 1,3,2
     /

Oracle : Script to report the storage parameters of the tablespaces in a database

COL   stat   FORM A7         HEAD   'Status'      JUST c
COL   name   FORM A15        HEAD 'Tablespace'    JUST c
COL   init   FORM 999,990    HEAD 'Initial|(KB)'  JUST c
COL   next   FORM 999,990    HEAD 'Next|(KB)'     JUST c
COL   pcti   FORM 990        HEAD 'Pct|incr'      JUST c
COL   minx   FORM 990        HEAD 'Min|exts'      JUST c
COL   maxe   FORM 9999999990 HEAD 'Max|exts'      JUST c

BREAK ON stat SKIP 1

SELECT status    stat, tablespace_name     name,initial_extent/1024   init,
 next_extent/1024      next, pct_increase       pcti,min_extents  minx,
 max_extents           maxe
FROM dba_tablespaces WHERE status!='INVALID'
ORDER BY 1,2
/ 

Oracle : Script to Report On Space Usage in Tablespaces

set pagesize 300
 set linesize 120
 column sumb format 9,999,999,999,999
 column extents format 999999
 column bytes format 9,999,999,999,999
 column largest format 9,999,999,999,999
 column Tot_Size format 9,999,999,999,999
 column Tot_Free format 9,999,999,999,999
 column Pct_Free format 9,999,999,999,999
 column Chunks_Free format 9,999,999,999,999  
 column Max_Free format 9,999,999,999,999
 set echo off
 spool TFSTSNFO.SQL
       
 PROMPT  SPACE AVAILABLE IN TABLESPACES  
       
 select a.tablespace_name,sum(a.tots) Tot_Size,  
 sum(a.sumb) Tot_Free,
 sum(a.sumb)*100/sum(a.tots) Pct_Free,  
 sum(a.largest) Max_Free,sum(a.chunks) Chunks_Free  
 from  
 (
 select tablespace_name,0 tots,sum(bytes) sumb,  
 max(bytes) largest,count(*) chunks
 from dba_free_space a
 group by tablespace_name
 union
 select tablespace_name,sum(bytes) tots,0,0,0 from
 dba_data_files
 group by tablespace_name) a
 group by a.tablespace_name;
       
 column owner format a15
 column segment_name format a30
       
       
 PROMPT   SEGMENTS WITH MORE THAN 20 EXTENTS  
       
 select owner,segment_name,extents,bytes ,  
 max_extents,next_extent  
 from  dba_segments  
 where segment_type in ('TABLE','INDEX') and extents>20  
 order by owner,segment_name;
       
       
       
 PROMPT SEGMENTS WHERE THERE'S NOT ENOUGH ROOM FOR THE NEXT EXTENT  

 select  a.owner, a.segment_name, b.tablespace_name,
      decode(ext.extents,1,b.next_extent,
      a.bytes*(1+b.pct_increase/100)) nextext,  
      freesp.largest
 from    dba_extents a,
      dba_segments b,
      (select owner, segment_name, max(extent_id) extent_id,
      count(*) extents  
      from dba_extents  
      group by owner, segment_name
      ) ext,
      (select tablespace_name, max(bytes) largest
      from dba_free_space  
      group by tablespace_name
      ) freesp
 where   a.owner=b.owner and
      a.segment_name=b.segment_name and
      a.owner=ext.owner and  
      a.segment_name=ext.segment_name and
      a.extent_id=ext.extent_id and
      b.tablespace_name = freesp.tablespace_name and  
      decode(ext.extents,1,b.next_extent,
      a.bytes*(1+b.pct_increase/100)) > freesp.largest
 /
spool off  

Oracle : Script will report tablespace use by segment type and user

ttitle - center  'Space Usage per Segment Type per Tablespace per User'  skip 2

 col username        formata20 justify c heading 'Username'
 col tablespace_name format            a20 justify c heading 'Tablespace Name'
 col segment_type    format            a17 justify c heading 'Segment Type'
 col mbytes          format 999,999,990.99 justify c heading 'Mb Used'
 
 break -
   on username skip 1
 
 select
   owner                  username,
   segment_type           segment_type,
   sum(bytes)/1048576     mbytes,
   tablespace_name        tablespace_name
 from
   dba_segments
 group by
   owner,
   tablespace_name,
   segment_type
 order by
   owner,
   segment_type,
   tablespace_name
/ 

Oracle : Script will report tablespace free space and fragmentation

Oracle : Script to evaluate space used in a tablespace

set pause off
set feed off
set verify off
set pagesize 55
set linesize 255
clear screen
col tn   format a10 heading 'Name' trunc
col fn   format a50 heading 'Located in file_ID + File Name'
col bts  format a10 heading 'Size'
col used format a10 heading 'Used'

col ex   format        9999 heading 'NrExt'
col rs   format 999,999,999 heading 'RBSsize'
col init format     999,999 heading 'Init'
col next format     999,999 heading 'Next'
col mi   format         999 heading 'Min'
col ma   format  9999999999 heading 'Max'
col pct  format         990 heading '%Inc'
col st   format          a4 heading 'Stat'
col sn   format         a15 heading 'Segm Name'
col ts   format         a15 heading 'In Table Space'

create or replace view free_view
as
select file_id, sum(bytes) free_bytes
from sys.dba_free_space
group by file_id;

clear screen

prompt Tablespace Datafiles
select d.tablespace_name tn,
       f.file_id||' '||file_name fn,
       to_char(f.bytes/1024,'999,999')||'K' bts,
       to_char( (f.bytes - s.free_bytes)/1024,'999,999')||'K' used
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id(+)
order by d.tablespace_name;

prompt
prompt                  Tablespace definitions

define part1="rpad('|',29*(f.bytes-s.free_bytes)/f.bytes,'*')"
col gr format a30 heading 'Percent full'
select d.tablespace_name tn,
       d.initial_extent init,
       d.next_extent next,
       d.pct_increase pct,
       d.min_extents mi, max_extents ma,
       decode(d.status,'ONLINE','OnL','OFFLINE','OffL') st,
       rpad(&part1,29,' ')||'|' gr
from sys.dba_tablespaces d, sys.dba_data_files f, free_view s
where d.tablespace_name = f.tablespace_name
and   f.file_id = s.file_id
order by d.tablespace_name;
drop view free_view;
set feed on

Oracle : Script lists the details of database growth per month

select to_char(creation_time, 'RRRR Month') "Month",
    sum(bytes)/1024/1024 "Growth in Meg"
    from sys.v_$datafile
    where creation_time > SYSDATE-365
    group by to_char(creation_time, 'RRRR Month');

Oracle : Script to Determine Objects Per Tablespace

 

 accept tableSpaceName prompt 'Enter tablespace: '
 
 set linesize 132
 set pagesize 72
 col type format a17 heading "object type"
 col object format a111 heading "schema.Object"

 break on type skip 1 on type
 ttitle left 'Tablespace: ' &tableSpaceName - skip 1 ' '
 
 select segment_type type,
      rtrim(owner)||'.'||rtrim(segment_name) object
 from sys.dba_extents
 where file_id in (
      select file_id
      from sys.dba_data_files
      where tablespace_name = upper('&tableSpaceName'))
 group by owner, segment_name, segment_type
 order by segment_type;

Oracle : Space Management and Object Storage

This script shows the space usage for a database.  This script performs the following functions:
               
  o Show the tablespaces, size, and percent used.            
  o The total free space still available in each tablespace.
  o The free space blocks in each of the tablespaces.      
  o The total free space still available in each tablespace.
  o The usage by each user segment, the inital extent size,
      and the size of the next extents - ordered by segment name.
  o The usage by each user segment, the inital extent size,
      and the size of the next extents - ordered by tablespace name.
  o The usage by each user segment, the inital extent size,
      and the size of the next extents - ordered by segment size.                                        
  o Show the size of the entire database.                   

 
 
 - - - - - - - - - - - - - - - - Code begins here - - - - - - - - - - - - - - - -

  set linesize 132;
  set pagesize 1000;
  set long 50;
  set echo on;
 
  break on "TBL SPACE" on "TOTAL BYTES";
 
  /* *************************************************** */
  /* block-size                                          */
  /* *************************************************** */
  column db_block_size new_value BLOCK_SIZE
  select to_char(value, '9999') db_block_size
         from v$parameter
         where name = 'db_block_size';
 
 
  /* *************************************************** */
  /* date & user_id                                      */
  /* *************************************************** */
  column today new_value dba_date
  select to_char(sysdate, 'mm/dd/yy hh:miam') today
         from dual;
 
  break on instance
  column instance new_value instance_name
  select substr(name,1,4) instance
        from v$database;
 
  clear breaks
  set termout on
 
  set pagesize 60 linesize 132 verify off
  set space 2
  ttitle left 'Date: ' format a18 dba_date -
         center 'Space Report - ' format a4 instance_name -
         right 'Page: ' format 999 sql.pno skip 2
 
  set echo off;
 
  set echo on;
 
 
  /* *************************************************** */
  /* tablespace usage via the dba_data_files             */
  /* *************************************************** */
  set echo off;
 
  select  substr(D.tablespace_name, 1, 12) "TBL SPACE",
     substr(to_char(sum(D.bytes), '999,999,999,999'), 1, 16) "TOTAL BYTES",
     substr(to_char(sum(D.bytes)/1024/1024, '999,999.9'), 1, 10) "TOT MBYTES",
     substr(to_char(sum(D.bytes)/(Z.VALUE), '99,999,999'), 1, 11) "BLOCKS"
  from    sys.dba_data_files D,
          v$parameter        Z
  where Z.name = 'db_block_size'
  group by D.tablespace_name, Z.value;
               
  set echo on;  
 
 
  /* *************************************************** */
  /*  show tablespace usage                              */
  /* *************************************************** */
  set echo off;
 
  select  distinct substr(tablespace_name, 1, 12)      "TBL SPACE",
    substr(to_char(sum(blocks), '999,999,999'), 1, 12)              "TOT BLKS",
       substr(to_char(sum(bytes), '999,999,999,999'), 1, 16)           "TOT BYTES"
  from    sys.dba_free_space
  group by
   tablespace_name;
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace usage                              */
  /* *************************************************** */
  set echo off;
 
  break on "TBL SPACE";
 
  select  substr(tablespace_name,1,12)                           "TBL SPACE",
        substr(to_char(file_id, '99'), 1, 4)                  "ID",
      substr(to_char(count(*), '9,999'), 1, 6)              "PCS",
     substr(to_char(max(blocks), '9,999,999'), 1, 10)      "MAX BLKS",
        substr(to_char(min(blocks), '9,999,999'), 1, 10)      "MIN BLKS",
        substr(to_char(avg(blocks), '9,999,999.9'), 1, 12)    "AVG BLKS",
        substr(to_char(sum(blocks), '9,999,999'), 1,10)       "SUM BLKS",
        substr(to_char(sum(bytes), '99,999,999,999'), 1, 15)  "SUM BYTES"
  from
    sys.dba_free_space
  group by
      tablespace_name,file_id
  order by 1, 4;
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace usage                              */
  /* *************************************************** */
  set echo off;
 
  select
       distinct substr(tablespace_name,1,12)   "TBL SPACE",
      substr(to_char(sum(blocks), '999,999,999'), 1, 12)         "SUM BLKS",
   substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 16) "SUM MBYTES"
  from
       sys.dba_free_space
  group by
       tablespace_name;
 
  set echo on;
  /* *************************************************** */
  /*  show tablespace segments - order by segment        */
  /* *************************************************** */
  set echo off;
 
  break on "SEGMENT" on "TYPE";
 
  select substr(segment_name, 1, 20)                          "SEGMENT",
         substr(segment_type,1,7)                             "TYPE",
         substr(tablespace_name,1,8)                          "TBL SPACE",
         substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 11)
                                                              "SUM Mb",
        /* substr(to_char(sum(blocks), '999,999'), 1, 8)         "SUM BLKS", */
         substr(to_char(extents, '999'), 1, 4)                 "EXTENTS",
         substr(to_char((initial_extent/(1024*1024)), '99,999.999'), 1, 10)
                                                               "INI (Mb)",
         substr(to_char((next_extent/(1024*1024)), '999.999'), 1, 8) "NXT (Mb)"
  from    user_segments
  group by segment_name,
           segment_type,
           tablespace_name,
           bytes,
           blocks,
           extents,
           initial_extent,
           next_extent
  order by 1, 2;
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace segments - order by tablespace     */
  /* *************************************************** */
  set echo off;
 
  break on "TBL SPACE" on "SEGMENT" on "TYPE";
 
  select substr(tablespace_name,1,12)                           "TBL SPACE",
         substr(segment_name, 1, 20)                            "SEGMENT",
      /* substr(segment_type,1,7)                               "TYPE",  */
         substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 12)
                                                                "SUM Mb",      
      /* substr(to_char(sum(blocks), '9,999,999'), 1, 10)       "SUM BLKS", */
         substr(to_char(extents, '999'), 1, 4)                  "EXTS",
         substr(to_char(initial_extent/(1024*1024), '999.999'), 1, 8)    
                                                                "INI Mb",
         substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)
                                                                "NXT Mb"
  from    user_segments
  group by segment_name,
           segment_type,
           tablespace_name,
           bytes,
           blocks,
           extents,
           initial_extent,
           next_extent
  order by 1, 2;
       
  set echo on;
 
 
  /* *************************************************** */
  /*  show tablespace segments - order by bytes          */
  /* *************************************************** */
  set echo off;
 
  break on "SUM BYTES" on "SEGMENT" on "TYPE";
 
  select substr(to_char(sum(bytes)/(1024*1024), '999,999.999'), 1, 11)
                                                                "SUM Mb",
         substr(segment_name, 1, 20)                            "SEGMENT",
         substr(tablespace_name,1,12)                           "TBL SPACE",
         /*substr(to_char(sum(blocks), '9,999,999'), 1, 10)     "SUM BLKS",*/
         substr(to_char(extents, '999'), 1, 4)                  "EXTS",
         substr(to_char(initial_extent/(1024*1024), '9,999.999'), 1, 10)  
                                                                "INI Mb",
         substr(to_char(next_extent/(1024*1024), '999.999'), 1, 8)          
                                                                "NXT Mb"
  from    user_segments
  group by segment_name,
           segment_type,
           tablespace_name,
           bytes,
           blocks,
           extents,
           initial_extent,
           next_extent
  order by 1, 2;        
 
  set echo on;
 
 
  /* *************************************************** */
  /*  show size of database                              */
  /* *************************************************** */
  set echo off;
 
  select  substr(to_char(sum(bytes)/1024/1024, '999,999,999.99'), 1, 15)  "TOT MBYTES",
          substr(to_char(sum(bytes)/1024/1024/1024, '999,999.99'), 1, 11) "TOT GBYTES"
  from    sys.dba_data_files;
       
  undefine BLOCK_SIZE;
  set echo off;
 
  set long 80;

 - - - - - - - - - - - - - - - -  Code ends here  - - - - - - - - - - - - - - - -

Oracle : Script lists all jobs that are currently running in the local database

set pagesize 80 
title -  center  'List Running Jobs'  skip 2 

col sess format 99   heading 'Ses' 
col jid  format 999  heading 'Id' 
col subu format a10  heading 'Submitter'     trunc 
col secd format a10  heading 'Security'      trunc 
col proc format a20  heading 'Job'           word_wrapped 
col lsd  format a5   heading 'Last|Ok|Date'  
col lst  format a5   heading 'Last|Ok|Time' 
col nrd  format a5   heading 'This|Run|Date' 
col nrt  format a5   heading 'This|Run|Time' 
col fail format 99 heading 'Err' 

select 
  djr.sid                        sess, 
  djr.job                        jid, 
  dj.log_user                    subu, 
  dj.priv_user                   secd, 
  dj.what                        proc, 
  to_char(djr.last_date,'MM/DD') lsd, 
  substr(djr.last_sec,1,5)       lst, 
  to_char(djr.this_date,'MM/DD') nrd, 
  substr(djr.this_sec,1,5)       nrt, 
  djr.failures                   fail 
from 
  sys.dba_jobs dj, 
  sys.dba_jobs_running djr 
where 
  djr.job = dj.job 
/ 

Oracle : Script provides a report showing the type and count of objects by user

SET ECHO OFF 
set newpage 0 
ttitle center 'Object counts by User' - 
right 'Page:' format 999 sql.pno skip skip 
col ow format a18 heading 'Owner' 
col ta format 999,999 heading 'Tables' 
col ind format 999,999 heading 'Indexes' 
col sy format 999,999 heading 'Synonyms' 
col se format 999,999 heading 'Sequences' 
col ve format 999,999 heading 'Views' 
col clu format 999,999 heading 'Clusters' 
set verify off 
compute sum of ta on report 
compute sum of ow on report 
compute sum of sy on report 
compute sum of se on report 
compute sum of ind on report 
compute sum of ve on report 
compute sum of clu on report 
break on report 
spool tfsobnum.lst 
set feedback off 
start time 
set feedback 6 
set heading on 
select owner ow, 
       sum(decode(object_type,'TABLE',1,0)) ta , 
       sum(decode(object_type,'INDEX',1,0)) ind , 
       sum(decode(object_type,'SYNONYM',1,0)) sy , 
       sum(decode(object_type,'SEQUENCE',1,0)) se , 
       sum(decode(object_type,'VIEW',1,0)) ve , 
       sum(decode(object_type,'CLUSTER',1,0)) clu 
from dba_objects 
group by owner 
/ 
col dbl format 999,999 heading 'Database|Links' 
col pkg format 999,999 heading 'Packages' 
col pkb format 999,999 heading 'Package|Bodies' 
col pro format 999,999 heading 'Procedures' 
col ve format 999,999 heading 'Views' 
col clu format 999,999 heading 'Clusters' 
set verify off 
compute sum of dbl on report 
compute sum of ow on report 
compute sum of pkg on report 
compute sum of pkb on report 
compute sum of pro on report 
compute sum of ve on report 
compute sum of clu on report 
break on report 
select owner ow, 
       sum(decode(object_type,'DATABASE LINK',1,0)) dbl , 
       sum(decode(object_type,'PACKAGE',1,0)) pkg , 
       sum(decode(object_type,'PACKAGE BODY',1,0)) pkb , 
       sum(decode(object_type,'PROCEDURE',1,0)) pro  
from dba_objects 
group by owner 
/ 
Prompt 
prompt End of Report 
spool off 
btitle off 
ttitle off 
clear breaks 
clear columns 
clear computes 
set verify on 

Oracle : Table's constraints and their associated tables and constraints

def owner = &&1 
def tab = &&2 

ttitle -  center  'Table &owner..&tab Constraints'  skip 2 

col name format a22 heading 'Name' justify c 
col type format a5  heading 'Type' justify c 
col stat format a4  heading 'Stat' justify c 
col ref_tab format a20 heading 'Reference|Object' justify c 
col ref_con format a22 heading 'Reference|Constraint' justify c 

select 
  a.constraint_name name, 
  decode(a.constraint_type,'C','Check','R','FK','P','PK','U','Uniq', 
    'C','Check','*') type, 
  decode(a.status,'ENABLED','Y','DISABLED','N','*') stat, 
  b.owner||'.'||b.table_name ref_tab, 
  a.r_constraint_name ref_con 
from 
  dba_constraints a, 
  dba_constraints b 
where 
  a.owner = upper('&owner') and 
  a.table_name = upper('&tab') and 
  a.r_constraint_name = b.constraint_name (+) 
order by 
  1 
/ 

undef owner 
undef tab 

Oracle : Script reports the names of all ORACLE usernames that own database objects

col owner format a33 heading 'Owner Name' justify c

select
  distinct owner
from
  dba_objects
where
  owner not in ('SYS')
order by
  1 asc
/ 

Oracle : Script generates a report of the current values of the database initialization parameters and statistics related to the SGA

SET ECHO OFF 
spool tfssgapr.lst 
ttitle cen 'Current SGA Parameters'  - 
right 'Page:' format 999 sql.pno skip skip 
set feedback off 


btitle off 
column nline newline 
set pagesize 54 
set linesize 78 
set heading off 
set embedded off 
set verify off 
accept report_comment char prompt 'Enter a comment to identify system: ' 
select 'Date -  '||to_char(sysdate,'Day Ddth Month YYYY     HH24:MI:SS'), 
'At            -  '||'&&report_comment' nline, 
'Username      -  '||USER  nline 
from sys.dual 
/ 
prompt 
set embedded on 
set heading on 


set feedback 6 
column name format a36 heading 'Parameter Name' wrap 
column val format a36 heading 'Value' wrap  

select name 
,lpad(decode(type,1,'Boolean',2,'Character',3,'Integer',4,'File',null),9) 
 ||' '||value val 
from   v$parameter 
order by 1; 
set embedded off 
set newpage 2 pagesize 16 lines 78 
ttitle cen 'Current SGA Storage Summary' - 
right 'Page:' format 999 sql.pno skip skip 
set newpage 2 pagesize 16  
column name format a20 heading 'SGA Segment' 
column value format 9,999,999,990 heading 'Size|(Bytes)' 
column kbval format 9,999,990.9 heading 'Size|(Kb)' 
break on report 
compute sum of value kbval on report 
set newpage 0 

select name 
      ,value 
      ,round(value/1024,1) kbval 
from   v$sga 
/ 
ttitle cen 'Current SGA Library Summary'  
set newpage 3 
set pagesize 60 
column library format A15 heading 'Library|Name' 
column gets format 9,999,999 heading 'Gets' 
column gethitratio format 990.99 heading 'Get Hit|Ratio' 
column pins format 9,999,999 heading 'Pins' 
column pinhitratio format 990.99 heading 'Pin Hit|Ratio' 
column reloads format 99,999 heading 'Reloads' 
column invalidations format 99,999 heading 'Invalid' 
select initcap(namespace) library, 
       gets, 
       gethitratio, 
       pins, 
       pinhitratio, 
       reloads, invalidations 
  from v$librarycache 
/ 
prompt 
prompt The pin hit rate should be high (close to 1) 
prompt 
prompt End of Report 
spool off; 
set termout off; 
clear break column sql 
ttitle off 
btitle off 
set newpage 0 pagesize 56 lines 78 
set termout on feedback 6 

Oracle : Script lists the control files, redo log files, and data files associated with a given database

col file_type_sort  noprint 
col file_type       format a7  heading 'Type'       justify c 
col file_name       format a48 heading 'File'       justify c trunc 
col file_size       format a7  heading 'Size|in Mb' justify c 
col tablespace_name format a14 heading 'Tablespace' justify c trunc 

break - 
  on file_type duplicates skip 1 

select 
  1           file_type_sort, 
  'CONTROL'   file_type, 
  value       file_name, 
  ''          file_size, 
  ''          tablespace_name 
from 
  v$parameter 
where 
  lower(name) = 'control_files' 
union 
select 
  2   file_type_sort, 
  'REDO'   file_type, 
  group#||':'||member file_name, 
  ''     file_size, 
  ''       tablespace_name 
from 
  v$logfile 
union 
select 
  3                                      file_type_sort, 
  'DATA'                   file_type, 
  file_name                                  file_name, 
  rpad(to_char(bytes/1048576,'99,990'),7)    file_size, 
  tablespace_name                            tablespace_name 
from 
  dba_data_files 
order by 
  1,5,3 
/ 

Oracle : Index Fragmentation

This script provides information critical in determining whether an  index is a candidate for rebuilding.  An index is a candidate for 
rebuilding when a relatively high number of index leaf row deletes have  occured.  

set verify off  
def ownr  = &&1  
def name  = &&2  
  
ttitle - center 'Index Fragmentation Statistic'   skip 2 
  
set heading off  
  
col name                 newline  
col lf_blk_rows          newline  
col del_lf_rows          newline  
col ibadness newline   
  
validate index &ownr..&name;  
  
select  
  'index name        '||name,  
  'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,  
  'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,  
  'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness  
from  
  index_stats  
/  
  
undef ownr  
undef name  
set verify on

Oracle : Script to Display detailed information about the current user's session

def aps_prog    = 'mysess.sql'
 def aps_title   = 'Session  Info'
 col "Session Info" form A80

 select 'Sid, Serial#, Aud sid : '|| s.sid||' , '||s.serial#||' , '||
        s.audsid||chr(10)|| 'DB User / OS User : '||s.username||
        '   /   '||s.osuser||chr(10)|| 'Machine - Terminal : '||
        s.machine||'  -  '|| s.terminal||chr(10)||
  'OS Process Ids : '||
        s.process||' (Client)  '||p.spid||' (Server)'|| chr(10)||
        'Client Program Name : '||s.program "Session Info"
   from v$process p,v$session s
  where p.addr = s.paddr
   and s.audsid = userenv('SESSIONID')
/

Oracle : Locks in rollback segments

column rr heading 'RB Segment' format a18 
column us heading 'Username' format a15 
column os heading 'OS User' format a10 
column te heading 'Terminal' format a10 


SELECT     r.name rr,  
           nvl(s.username,'no transaction') us, 
           s.osuser os,  
           s.terminal te 
FROM       v$lock  l, v$session  s,v$rollname  r 
WHERE      l.sid = s.sid(+) AND  
           trunc(l.id1/65536) = r.usn AND 
           l.type = 'TX' AND  
    l.lmode = 6 
ORDER BY   r.name 
/ 

Oracle : Script to Monitor Current User Activity in the Database II

set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
  from v$sqltext a, v$session b, v$process c
   where a.address    = b.sql_address
--   and b.status     = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
--                                  ACTVE TRANSACTION ON THAT MOMENT */
   and b.paddr      = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid,a.hash_value,a.piece
/      

Oracle : Script to list missing and INVALID Objects in the database

select A.Owner Oown, 
       A.Object_Name Oname, 
       A.Object_Type Otype, 
       'Miss Pkg Body' Prob 
  from DBA_OBJECTS A 
 where A.Object_Type = 'PACKAGE' 
   and A.Owner not in ('SYS','SYSTEM')
   and not exists 
        (select 'x' 
           from DBA_OBJECTS B 
          where B.Object_Name = A.Object_Name 
            and B.Owner = A.Owner 
            and B.Object_Type = 'PACKAGE BODY') 
union 
select Owner Oown, 
       Object_Name Oname, 
       Object_Type Otype, 
       'Invalid Obj' Prob 
  from DBA_OBJECTS 
 where Owner not in ('SYS','SYSTEM') 
   and Status != 'VALID' 
 order by 1,4,3,2
/

Oracle GoldenGate Configuring DDL Synchronization

In addition to providing replication support for all DML statements, we can also configure the GoldenGate environment to provide DDL support as well.


A number of prerequisite setup tasks need to be performed which we willl highlight here.

Run the following scripts from the directory where the GoldenGate software was installed.

The assumption here is that the database user GGS_OWNER has already been created and granted the required roles and privileges as discussed in our earlier tutorial.


Note - run the scripts as SYSDBA


SQL> @marker_setup


Marker setup script


You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name:GGS_OWNER



Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:


Setting schema name to GGS_OWNER


MARKER TABLE

-------------------------------

OK


MARKER SEQUENCE

-------------------------------

OK


Script complete.




SQL> alter session set recyclebin=OFF;

Session altered.



SQL> @ddl_setup

GoldenGate DDL Replication setup script


Verifying that current user has privileges to install DDL Replication...


You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: On Oracle 10g and up, system recycle bin must be disabled.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name: GGS_OWNER


You will be prompted for the mode of installation.

To install or reinstall DDL replication, enter INITIALSETUP

To upgrade DDL replication, enter NORMAL

Enter mode of installation:INITIALSETUP


Working, please wait ...

Spooling to file ddl_setup_spool.txt



Using GGS_OWNER as a GoldenGate schema name, INITIALSETUP as a mode of installation.


Working, please wait ...


RECYCLEBIN must be empty.

This installation will purge RECYCLEBIN for all users.

To proceed, enter yes. To stop installation, enter no.


Enter yes or no:yes



DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS_OWNER


DDLORA_GETTABLESPACESIZE STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


CLEAR_TRACE STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


CREATE_TRACE STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


TRACE_PUT_LINE STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


INITIAL_SETUP STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


DDLVERSIONSPECIFIC PACKAGE STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


DDLREPLICATION PACKAGE STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


DDLREPLICATION PACKAGE BODY STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


DDL HISTORY TABLE

-----------------------------------

OK


DDL HISTORY TABLE(1)

-----------------------------------

OK


DDL DUMP TABLES

-----------------------------------

OK


DDL DUMP COLUMNS

-----------------------------------

OK


DDL DUMP LOG GROUPS

-----------------------------------

OK


DDL DUMP PARTITIONS

-----------------------------------

OK


DDL DUMP PRIMARY KEYS

-----------------------------------

OK


DDL SEQUENCE

-----------------------------------

OK


GGS_TEMP_COLS

-----------------------------------

OK


GGS_TEMP_UK

-----------------------------------

OK


DDL TRIGGER CODE STATUS:


Line/pos Error

---------- -----------------------------------------------------------------

No errors No errors


DDL TRIGGER INSTALL STATUS

-----------------------------------

OK


DDL TRIGGER RUNNING STATUS

-----------------------------------

ENABLED


STAYMETADATA IN TRIGGER

-----------------------------------

OFF


DDL TRIGGER SQL TRACING

-----------------------------------

0


DDL TRIGGER TRACE LEVEL

-----------------------------------

0


LOCATION OF DDL TRACE FILE

--------------------------------------------------------------------------------

/opt/app/oracle/diag/rdbms/db1/db1/trace/ggs_ddl_trace.log


Analyzing installation status...



STATUS OF DDL REPLICATION

--------------------------------------------------------------------------------

SUCCESSFUL installation of DDL Replication software components


Script complete.

SQL>





SQL> @role_setup


GGS Role setup script


This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change

the gg_role parameter to the preferred name. (Do not run the script.)


You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.


Enter GoldenGate schema name:GGS_OWNER

Wrote file role_setup_set.txt


PL/SQL procedure successfully completed.



Role setup script complete


Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:


GRANT GGS_GGSUSER_ROLE TO


where is the user assigned to the GoldenGate processes.



SQL> grant ggs_ggsuser_role to ggs_owner;


Grant succeeded.



SQL> @ddl_enable


Trigger altered.




SQL> @ddl_pin GGS_OWNER


PL/SQL procedure successfully completed.



PL/SQL procedure successfully completed.



PL/SQL procedure successfully completed.


Turn Recyclebin OFF


We need to set the parameter recyclebin to OFF via the

ALTER SYSTEM SET RECYCLEBIN=OFF command in order to prevent this error which we will see if we try and configure DDL support and then start the Extract process.


2010-02-19 11:13:30 GGS ERROR 2003 RECYCLEBIN must be turned off. For 10gr2 and up, set RECYCLEBIN in parameter file to OFF. For 10gr1, set _RECYCLEBIN in parameter file to FALSE. Then restart database and extract.


2010-02-19 11:13:30 GGS ERROR 190 PROCESS ABENDING.

Enable additional logging at the table level


Note- We had earlier enabled additional supplemental logging at the database level. Using the ADD TRANDATA command we now enable it at even the table level as this is required by GoldenGate for DDL support.


GGSCI (redhat346.localdomain) 5> DBLOGIN USERID ggs_owner, PASSWORD ggs_owner

Successfully logged into database.


GGSCI (redhat346.localdomain) 6> ADD TRANDATA scott.emp

Logging of supplemental redo data enabled for table SCOTT.EMP.
Edit the parameter file for the Extract process to enable DDL synchronization

We had earlier created a parameter file for an Extract process ext1. We now edit that parameter file and add the entry


DDL INCLUDE MAPPED


This means that DDL support is now enabled for all tables which have been mapped and in this case it will only apply to the SCOTT.EMP table as that is the only table which is being processed here. We can also use the INCLUDE ALL or EXCLUDE ALL or wildcard characters to specify which tables to enable the DDL support for.


GGSCI (redhat.localdomain) 1> EDIT PARAM EXT1

EXTRACT ext1

USERID ggs_owner, PASSWORD ggs_owner

RMTHOST 10.53.100.100, MGRPORT 7809

RMTTRAIL /opt/oracle/software/goldengate/dirdat/rt

DDL INCLUDE MAPPED

TABLE scott.emp;


Test the same

We will now alter the structure of the EMP table by adding a column and we can see that this new table structure is also reflected on the target system.


On Source

SQL> ALTER TABLE EMP ADD NEW_COL VARCHAR2(10);

Table altered.

On Target

SQL> desc emp

Name Null? Type

—————————————– ——– —————————-

EMPNO NOT NULL NUMBER(4)

ENAME VARCHAR2(10)

JOB VARCHAR2(20)

MGR NUMBER(4)

HIREDATE DATE

SAL NUMBER(7,2)

COMM NUMBER(7,2)

DEPTNO NUMBER(2)

MYCOL VARCHAR2(10)

NEW_COL VARCHAR2(10)

Oracle : Script which tells free space left in tablespaces and datafiles in terms of percentage

SET PAGESIZE 100 LINES 132 ECHO OFF VERIFY OFF FEEDB OFF SPACE 1 TRIMSP ON
COMPUTE SUM OF a_byt t_byt f_byt ON REPORT
BREAK ON REPORT ON tablespace_name ON pf
COL tablespace_name FOR A17   TRU HEAD 'Tablespace|Name'
COL file_name       FOR A40   TRU HEAD 'Filename'
COL a_byt           FOR 9,990.999 HEAD 'Allocated|GB'
COL t_byt           FOR 9,990.999 HEAD 'Current|Used GB'
COL f_byt           FOR 9,990.999 HEAD 'Current|Free GB'
COL pct_free        FOR 990.0     HEAD 'File %|Free'
COL pf              FOR 990.0     HEAD 'Tbsp %|Free'
COL seq NOPRINT
DEFINE b_div=1073741824
--
SELECT 1 seq, b.tablespace_name, nvl(x.fs,0)/y.ap*100 pf, b.file_name file_name,
  b.bytes/&&b_div a_byt, NVL((b.bytes-SUM(f.bytes))/&&b_div,b.bytes/&&b_div) t_byt,
  NVL(SUM(f.bytes)/&&b_div,0) f_byt, NVL(SUM(f.bytes)/b.bytes*100,0) pct_free
FROM dba_free_space f, dba_data_files b
 ,(SELECT y.tablespace_name, SUM(y.bytes) fs
   FROM dba_free_space y GROUP BY y.tablespace_name) x
    ,(SELECT x.tablespace_name, SUM(x.bytes) ap
      FROM dba_data_files x GROUP BY x.tablespace_name) y
   WHERE f.file_id(+) = b.file_id
   AND   x.tablespace_name(+) = y.tablespace_name
   and   y.tablespace_name =  b.tablespace_name
   AND   f.tablespace_name(+) = b.tablespace_name
   GROUP BY b.tablespace_name, nvl(x.fs,0)/y.ap*100, b.file_name, b.bytes
   UNION
   SELECT 2 seq, tablespace_name,
     j.bf/k.bb*100 pf, b.name file_name, b.bytes/&&b_div a_byt,
     a.bytes_used/&&b_div t_byt, a.bytes_free/&&b_div f_byt,
     a.bytes_free/b.bytes*100 pct_free
   FROM v$temp_space_header a, v$tempfile b
     ,(SELECT SUM(bytes_free) bf FROM v$temp_space_header) j
     ,(SELECT SUM(bytes) bb FROM v$tempfile) k
   WHERE a.file_id = b.file#
   ORDER BY 1,2,4,3;

Oracle : Script that shows tablespace free space

select /*+ CURSOR_SHARING_EXACT */
  substrb(dtb.tablespace_name,1,16) TS_NAME,
  substrb(dtb.contents,1,3) TYP,
  substrb(dtb.status,1,2) ST,
  substrb(decode(dtb.logging,'LOGGING','Y','N'),1,1) "L",
  substrb(dtb.extent_management,1,1) "D",
  substrb(decode(floor(dtb.initial_extent/1073741824),0,
    decode(floor(dtb.initial_extent/1048576),0,
      to_char(dtb.initial_extent/1024,'9999')||'K',
      to_char(dtb.initial_extent/1048576,'9999')||'M'),
    to_char(dtb.initial_extent/1073741824,'99.9')||'G'),2,6) "INIT",
  substrb(decode(dtb.allocation_type,'UNIFORM',' UEM','SYSTEM',' SYS',
    decode(floor(dtb.next_extent/1073741824),0,
      decode(floor(dtb.next_extent/1048576),0,
        to_char(dtb.next_extent/1024,'9999')||'K',
        to_char(dtb.next_extent/1048576,'9999')||'M'),
      to_char(dtb.next_extent/1073741824,'99.9')||'G')),2,6) "NEXT",
  substrb(to_char(my_dba_data_files.file_count,'999'),2,4) FC,
  substrb(to_char(my_dba_data_files.auto_extend,'999'),2,4) AX,
  substrb(decode(floor(my_dba_data_files.total_size/1073741824),0,
    decode(floor(my_dba_data_files.total_size/1048576),0,
      to_char(my_dba_data_files.total_size/1024,'9999')||'K',
      to_char(my_dba_data_files.total_size/1048576,'9999')||'M'),
    to_char(my_dba_data_files.total_size/1073741824,'99.9')||'G'),2,6) "SIZE",
  substrb(decode(floor(my_free_space.total_free_space/1073741824),0,
    decode(floor(my_free_space.total_free_space/1048576),0,
      to_char(my_free_space.total_free_space/1024,'9999')||'K',
      to_char(my_free_space.total_free_space/1048576,'9999')||'M'),
    to_char(my_free_space.total_free_space/1073741824,'99.9')||'G'),2,6) "FREE",
  substrb(decode(floor(my_dba_data_files.max_size/1073741824),0,
    decode(floor(my_dba_data_files.max_size/1048576),0,
      to_char(my_dba_data_files.max_size/1024,'99999')||'K',
      to_char(my_dba_data_files.max_size/1048576,'99999')||'M'),
    to_char(my_dba_data_files.max_size/1073741824,'999.9')||'G'),2,7) "MAX",
  substrb(decode(floor(my_free_space.largest_free_segment/1073741824),0,
    decode(floor(my_free_space.largest_free_segment/1048576),0,
      to_char(my_free_space.largest_free_segment/1024,'9999')||'K',
      to_char(my_free_space.largest_free_segment/1048576,'9999')||'M'),
    to_char(my_free_space.largest_free_segment/1073741824,'99.9')||'G'),2,6) LFS,
  lpad(decode(my_dba_data_files.auto_extend,0,' ','(')||
    ltrim(to_char(least(nvl(((my_free_space.total_free_space+(my_dba_data_files.max_size-my_dba_data_files.total_size))/my_dba_data_files.max_size)*100,0),99.9),'99.9'))||
'%'||decode(my_dba_data_files.auto_extend,0,' ',')'),7) PC_FREE
from dba_tablespaces dtb, (
  select
    ddf.tablespace_name,
    count(ddf.file_id) file_count,
    sum(ddf.bytes) total_size,
    sum(greatest(ddf.bytes,ddf.maxbytes)) old_max_size,
    sum(decode(ddf.autoextensible,'YES',greatest(ddf.bytes,ddf.maxbytes),ddf.bytes)) max_size,
    sum(decode(ddf.autoextensible,'YES',1,0)) auto_extend
  from dba_data_files ddf
  group by ddf.tablespace_name ) my_dba_data_files, (
    select
    dfs.tablespace_name tablespace_name,
    max(dfs.bytes) largest_free_segment,
    sum(dfs.bytes) total_free_space
  from dba_free_space dfs
  group by tablespace_name
  union all
  select
    dtb.tablespace_name,0,0
  from dba_tablespaces dtb
  where not exists ( select 1 from dba_free_space dfs where dfs.tablespace_name = dtb.tablespace_name )) my_free_space
where dtb.tablespace_name = my_dba_data_files.tablespace_name(+)
and dtb.tablespace_name = my_free_space.tablespace_name(+)
order by
  ((my_free_space.total_free_space+(my_dba_data_files.max_size-my_dba_data_files.total_size))/my_dba_data_files.max_size) desc,
  typ,
  ts_name
/

Oracle : Script to Monitor User Activity in database

set pagesize 66
col c1 for a9
col c1 heading "OS User"
col c2 for a9
col c2 heading "Oracle User"
col b1 for a9
col b1 heading "Unix PID"
col b2 for 9999 justify left
col b2 heading "SID"
col b3 for 99999 justify left
col b3 heading "SERIAL#"
col sql_text for a35
break on b1 nodup on c1 nodup on c2 nodup on b2 nodup on b3 skip 3
select c.spid b1, b.osuser c1, b.username c2, b.sid b2, b.serial# b3,
a.sql_text
  from v$sqltext a, v$session b, v$process c
   where a.address    = b.sql_address
--   and b.status     = 'ACTIVE' /* YOU CAN CHOOSE THIS OPTION ONLY TO SEE
--                                  ACTVE TRANSACTION ON THAT MOMENT */
   and b.paddr      = c.addr
   and a.hash_value = b.sql_hash_value
 order by c.spid,a.hash_value,a.piece
/