DBA REGULAR SCRIPTS

Queries : Memory, Free Space, Size, Temp Files, Control Files etc..

1)Database block size:

select to_number(value) "Block size in bytes"

from   sys.v_$parameter

where  name = 'db_block_size';

2)Max number of possible extents:

select to_number(value)/16-7 "MaxExtents"

from   sys.v_$parameter

where  name = 'db_block_size';

3)Min extent size:

select to_number(a.value) * to_number(b.value) / 1024 "Min extent size in K"

from   sys.v_$parameter a, sys.v_$parameter b

where  a.name = 'db_block_size'

and  b.name = 'db_file_multiblock_read_count';

4)List objects in the SYSTEM tablespace that doesn't belong to SYS or SYSTEM

select * from sys.dba_segments

where owner not in ('PUBLIC', 'SYS', 'SYSTEM')

and tablespace_name = 'SYSTEM';

5)Reports information about your current database context

select 'User: '|| user || ' on database ' || global_name,

       '  (term='||USERENV('TERMINAL')||

       ', audsid='||USERENV('SESSIONID')||')' as MYCONTEXT

from   global_name;

6)Reports free memory available in the SGA

select name,

       sgasize/1024/1024 "Allocated (M)",

       bytes/1024 "Free (K)",

       round(bytes/sgasize*100, 2) "% Free"

from   (select sum(bytes) sgasize from sys.v_$sgastat) s,

sys.v_$sgastat f

where  f.name = 'free memory';

7)Measure the Buffer Cache Hit Ratio

Get initial Buffer Hit Ratio reading

SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) "Cache Hit Ratio"

FROM v$sysstat cur, v$sysstat con, v$sysstat phy

WHERE cur.name = 'db block gets'

AND con.name = 'consistent gets' AND phy.name = 'physical reads';

8)Database users with deadly roles assigned to them.

select grantee, granted_role, admin_option

from   sys.dba_role_privs

where  granted_role in ('DBA', 'AQ_ADMINISTRATOR_ROLE',

                       'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE',

                       'OEM_MONITOR')

  and  grantee not in ('SYS', 'SYSTEM', 'OUTLN',

'AQ_ADMINISTRATOR_ROLE',

                       'DBA', 'EXP_FULL_DATABASE',

'IMP_FULL_DATABASE',

                       'OEM_MONITOR',

'CTXSYS', 'DBSNMP', 'IFSSYS',

                       'IFSSYS$CM', 'MDSYS', 'ORDPLUGINS',

'ORDSYS',

                       'TIMESERIES_DBA');

9)Database users with deadly system privilages assigned to them.

select grantee, privilege, admin_option

from   sys.dba_sys_privs

where  (privilege like '% ANY %'

  or   privilege in ('BECOME USER', 'UNLIMITED TABLESPACE')

  or   admin_option = 'YES')

 and   grantee not in ('SYS', 'SYSTEM', 'OUTLN',

'AQ_ADMINISTRATOR_ROLE',

'DBA', 'EXP_FULL_DATABASE',

'IMP_FULL_DATABASE',

'OEM_MONITOR', 'CTXSYS',

'DBSNMP', 'IFSSYS',

'IFSSYS$CM', 'MDSYS',

'ORDPLUGINS', 'ORDSYS',

'TIMESERIES_DBA');

10)Allocated, Used & Free space within datafiles

SELECT   SUBSTR (df.NAME, 1, 40) file_name,

df.bytes / 1024 / 1024 allocated_mb,

((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,

NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

FROM v$datafile df, dba_free_space dfs

WHERE df.file# = dfs.file_id(+)

GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes

ORDER BY file_name;

11)Show Used/free space by tablespace name

SELECT Total.name "Tablespace Name",

       nvl(Free_space, 0) Free_space,

       nvl(total_space-Free_space, 0) Used_space,

       total_space

FROM

  (select tablespace_name, sum(bytes/1024/1024) Free_Space

     from sys.dba_free_space

    group by tablespace_name

  ) Free,

  (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE

     from sys.v_$datafile a, sys.v_$tablespace B

    where a.ts# = b.ts#

    group by b.name

  ) Total

WHERE Free.Tablespace_name(+) = Total.name

ORDER BY Total.name;

12)INvestigating undo-tablespace in EBS

SELECT version FROM v$timezone_file;

select count(status) from dba_undo_extents where status = 'ACTIVE';

select count(status) from dba_undo_extents where status = 'UNEXPIRED';

select (sum(bytes)/(1024*1024)) "SIZE in MB",tablespace_name from dba_free_space group by tablespace_name;

13)Find type of objects available in a tablespace

select ds.segment_name,do.object_type,do.status,ds.tablespace_name from dba_segments ds,dba_objects do where ds.tablespace_name='TOOLS'

Temp tablespace usage information

SELECT A.tablespace_name tablespace, D.mb_total,

SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free

FROM v$sort_segment A,

(

SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total

FROM v$tablespace B, v$tempfile C

WHERE B.ts#= C.ts#

GROUP BY B.name, C.block_size

) D

WHERE A.tablespace_name = D.name

GROUP by A.tablespace_name, D.mb_total;

14)To count no. of segments in each t.s.

select tablespace_name,count(*) from dba_segments group by tablespace_name;

15)Database growth per month for last year

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');

16)Query to find the ATG rollup Patch level in your application (11i).

connect as apps:

SELECT (bug_number),

decode((bug_number),

'3438354','ATG_PF.H',

'4017300','ATG_PF.H RUP 1',

'4125550','ATG_PF.H RUP 2',

'4334965','ATG_PF.H RUP 3',

'4676589','ATG_PF.H RUP 4',

'5473858','ATG_PF.H RUP 5',

'5903765','ATG_PF.H RUP 6',

'6241631','ATG_PF.H RUP 7')

FROM ad_bugs

WHERE bug_number IN

('3384350',

'3438354',

'4017300',

'4125550',

'4334965',

'4676589',

'5473858',

'5903765',

'6241631')

order by bug_number desc; 

17)To find RUP Level in R12 : -

select bug_number, creation_date

from ad_bugs

where bug_number in ('6022657','6266113','6728000')

ORDER BY 2;

18)To Check Maintenance Family Pack Level

select patch_level from fnd_product_installations

where application_id = 275;

19)Query to find the Apps Version : -

select release_name from fnd_product_groups;

20)Which FND_USER is locking that table

The column named "module" will tell you the name of the Form Function

or the Concurrent Program Short name which has aquired a lock onto that table.

SELECT c.owner ,c.object_name,c.object_type,

       fu.user_name locking_fnd_user_name

      ,fl.start_time locking_fnd_user_login_time,

      vs.module,vs.machine ,vs.osuser

      ,vlocked.oracle_username,vs.sid,vp.pid

      ,vp.spid AS os_process,vs.serial# ,vs.status ,vs.saddr

      ,vs.audsid ,vs.process

FROM fnd_logins fl ,fnd_user fu ,v$locked_object vlocked

    ,v$process vp ,v$session vs,dba_objects c

WHERE vs.sid = vlocked.session_id

AND vlocked.object_id = c.object_id

AND vs.paddr = vp.addr

AND vp.spid = fl.process_spid(+)

AND vp.pid = fl.pid(+)

AND fl.user_id = fu.user_id(+)

AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'

AND nvl(vs.status ,'XX') != 'KILLED';

21)Security related database initialization parameters and password file users.

select name || '=' || value "PARAMTER"

from   sys.v_$parameter

where  name in ('remote_login_passwordfile', 'remote_os_authent',

                'os_authent_prefix', 'dblink_encrypt_login',

                'audit_trail', 'transaction_auditing');

22)List security related profile information

select profile, resource_name, limit

from   dba_profiles

where  resource_name like '%PASSWORD%'

   or  resource_name like '%LOGIN%';

23)To find space used by a database user

Query to find space used by a database user. Following query can be used to know the space used by the logged in user in MBs:

SELECT sum(bytes)/1024/1024 user_size FROM user_segments;

24)Query to find the space occupied by all the users in a database.

This requires access to dba_segments table:

SELECT owner, sum(bytes)/1024/1024 total_size FROM dba_segments

GROUP BY owner ORDER BY total_size DESC;

25)Total space occupied by all users:

SELECT sum(bytes)/1024/1024 total_size FROM dba_segments;

26)To find Database Size

The database mainly comprises of datafiles, temp files and redo log files.

The biggest portion of a database’s size comes from the datafiles.

27)To find out how many megabytes are allocated to all datafiles:

SELECT sum(bytes)/1024/1024 data_size FROM dba_data_files;

28)To get the size of all TEMP files:

SELECT nvl(sum(bytes),0)/1024/1024 temp_size FROM dba_temp_files;

29)To get the size of the on-line redo-logs:

SELECT sum(bytes)/1024/1024 redo_size FROM sys.v_$log;

Finally, summing up the three above, total database size can be found:

SELECT (dsize.data_size + tsize.temp_size + rsize.redo_size)/1024/1024 "total_size"

FROM (SELECT sum(bytes) data_size

FROM dba_data_files ) dsize,

(SELECT nvl(sum(bytes),0) temp_size

FROM dba_temp_files ) tsize,

(SELECT sum(bytes) redo_size

FROM sys.v_$log ) rsize;

30)To find free space in temporary tablesapce:

SELECT tablespace_name,SUM(bytes_used),SUM(bytes_free) FROM V$temp_space_header GROUP BY tablespace_name;

31)To find tablespace free space in a database

SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes,

b.free_bytes

FROM dba_data_files a,

(SELECT file_id, SUM(bytes) free_bytes

FROM dba_free_space b GROUP BY file_id) b

WHERE a.file_id=b.file_id

and a.tablespace_name='SYSTEM'

ORDER BY a.tablespace_name;

32)To find Table size in a database :

select sum(BYTES/1024/1024) as TOTAL_GIG from user_segments where

SEGMENT_NAME = 'TABLE_NAME';

Note: Need to execute as owner of the table. (OR)

select sum(BYTES/1024/1024) as TOTAL_GIG from dba_segments where SEGMENT_NAME='FND_TS_MIG_CMDS';

33)=== Temp Usage ===

select sum(blocks)/1024*8 "Size in MB" FROM v$sort_usage;

SELECT ss.sid, sum(st.blocks)/1024*8

FROM v$sort_usage st, v$session ss

where ss.saddr=st.session_addr

group by ss.sid

having sum(st.blocks)/1024*8 > 100 order by 2;

SELECT  /*+ RULE */ s.username, s.osuser, s.sid,

NVL(s.machine,'N/A'),

NVL(s.module,'N/A'), NVL(s.action,'N/A'),

NVL(s.program,'N/A'),

s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr',

u.tablespace, u.contents, u.extents,

round((u.blocks*8)/1024),s.sql_address, s.sql_hash_value

FROM    v$session s, v$sort_usage u

WHERE   s.saddr    = u.session_addr

AND     u.contents = 'TEMPORARY'

AND     s.audsid != USERENV('sessionid')

AND    (u.blocks*8)/1024 >= 1000

ORDER   BY 1,2,3,4,5 Desc;

34)=== High Redo ===

SELECT s.inst_id,s.sid, s.serial#, s.username,

s.program, i.block_changes

FROM gv$session s, gv$sess_io i

WHERE s.sid = i.sid

AND i.block_changes > 10000000

ORDER BY 6 desc, 1, 2, 3, 4;

35)=== Rollback Used ===

                                 

SELECT rn.name, ROUND(rs.rssize/1024/1024),

s.username, s.osuser, s.sid, NVL(s.machine,'N/A'),

NVL(s.module,'N/A'), NVL(s.action,'N/A'),

NVL(s.program,'N/A'),

s.sql_address, s.sql_hash_value, p.spid,

s.status ||' for '||LPAD(((last_call_et/60)-mod((last_call_et/60),60))/60,2,'0') ||':'||LPAD(ROUND(mod((last_call_et/60),60)),2,'0') ||' Hr' ,

round(ceil((t.used_ublk*8)/1024),1)

FROM   v$rollname rn, v$rollstat rs,

v$session s, v$transaction t, v$process p

WHERE  rn.usn = rs.usn

AND    round((t.used_ublk*8)/1024) >= 1000

AND    rs.usn = t.xidusn

AND    s.sid = p.pid (+)

AND    s.taddr = t.addr

ORDER  BY 2 desc, s.sid ,s.status;

36)=== Roll back segement Information ====

select tablespace_name, status segment_status,

count(extent_id) "Extent Count", sum(blocks) "Total Blocks",

sum(blocks)*8/(1024*1024) "Total Space in GB"

from dba_undo_extents

where tablespace_name like '%UNDO%'

group by tablespace_name, status;

37)=== Shared Pool Usage ===

SELECT INST_ID,ROUND(bytes/1024/1024, 2)||' MB'

FROM gv$sgastat 

WHERE name='free memory' AND pool='shared pool';

38)=== Archive Generation for last 5 hours ===

SELECT TO_CHAR(first_time, 'DD-MM-YY') AS Day,

TO_CHAR(first_time, 'HH24') AS Hour,COUNT(*) 

FROM v$log_history

WHERE TO_CHAR(first_time, 'DD-MM-YY') = TO_CHAR(sysdate, 'DD-MM-YY')

AND  TO_CHAR(first_time, 'HH24') >= TO_CHAR(sysdate, 'HH24') - 5

GROUP BY  TO_CHAR(first_time, 'DD-MM-YY'),

TO_CHAR(first_time, 'HH24') ORDER BY 2;

39)=== High Memory ===

select s.inst_id, s.sid, name,

round(value/1024/1024) Mem_in_mb, p.*

from gv$sesstat s, v$statname n, gv$session p

where n.statistic# = s.statistic#

and s.inst_id = p.inst_id

and s.sid = p.sid

and name like '%pga memory%'

and value > 1*1024*1024*512;

40)=== Performance ===

select sql_hash_value, count(*)

from v$session

where event like 'enq%'

group by sql_hash_value;

select sql_hash_value,username,osuser, count(*)

from v$session

where event like 'enq%'

and SQL_HASH_VALUE='&event'

group by sql_hash_value,username,osuser;

select sql_text

from v$sqlarea

where hash_value = '&hash_value';

select s1.sid,FETCHES,ROWS_PROCESSED

from v$sql s,v$session s1

where s.HASH_VALUE=s1.SQL_HASH_VALUE

and s1.sid=4885;

select s.sid,s.serial#,s.status,s.username,s.module,

s.osuser,x.event

from v$session s,v$session_wait x

where s.paddr in ( select addr

from v$process

where spid in (2340,23869,13827,18261,14880,2381))

and x.sid=s.sid;

select s.sid,s.serial#,s.status,s.username,

s.module,s.osuser,x.event

from v$session s,v$session_wait x

where x.sid=s.sid and x.event like '&event_name';

How to find the ORACLE_HOME path in Oracle Database?

In 9i:

SELECT substr(file_spec,1,instr(file_spec,'lib')-2) ORACLE_HOME FROM

dba_libraries

WHERE library_name='DBMS_SUMADV_LIB';

In 10g:

SQL > var OHM varchar2(100);

SQL > EXEC dbms_system.get_env('ORACLE_HOME', :OHM) ;

SQL > PRINT OHM

Linux/Unix:

echo $ORACLE_HOME

=== Other Script to update the daily report ===

select status,count(1) from gv$session group by status;

select count(1) from dba_tables where logging='NO';

select distinct status,count(1) from dba_indexes group by status;

select count(1) from dba_objects where status='INVALID';

select sum(bytes)/1024/1024 from dba_data_files;

select sum(bytes)/1024/1024 from v$log;

select sum(bytes)/1024/1024/1024 from dba_data_files
+
select sum(bytes)/1024/1024/1024 from dba_temp_files
+
select sum(bytes)/1024/1024/1024 from v$log

select t.tablespace_name, round(a.bytes,2) tbsize, 
nvl(round(c.bytes,2),'0') tbfree, 
nvl(round(b.bytes,2),'0') tbused, 
to_char(round(100 * (nvl(b.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbusedpct, 
to_char(round(100 * (nvl(c.bytes,0)/nvl(a.bytes,1)),2)) || '%' tbfreepct 
from dba_tablespaces t, 
(select tablespace_name, round(sum(bytes)/1024/1024,2) bytes 
from dba_data_files 
group by tablespace_name 
union 
select tablespace_name, round(sum(bytes)/1024/1024,2) bytes 
from dba_temp_files 
group by tablespace_name ) a, 
(select e.tablespace_name, round(sum(e.bytes)/1024/1024,2) bytes 
from dba_segments e 
group by e.tablespace_name 
union 
select tablespace_name, sum(max_size) bytes 
from v$sort_segment 
group by tablespace_name) b, 
(select f.tablespace_name, round(sum(f.bytes)/1024/1024,2) bytes 
from dba_free_space f 
group by f.tablespace_name 
union 
select tmp.tablespace_name,  (sum(bytes/1024/1024) - sum(max_size)) bytes 
from dba_temp_files tmp, v$sort_segment sort 
where tmp.tablespace_name = sort.tablespace_name 
group by tmp.tablespace_name) c 
where 
t.tablespace_name = a.tablespace_name (+) 
and t.tablespace_name = b.tablespace_name (+) 
and t.tablespace_name = c.tablespace_name (+) 
order by t.tablespace_name 
/