DBA scripts

Query to find the foreign keys and their parent keys

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"

SELECT   uc.constraint_name||CHR(10)
|| '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
,    'REFERENCES'||CHR(10)
||  '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      ucc1.POSITION = ucc2.POSITION 
AND      uc.constraint_type = 'R'
ORDER BY ucc1.TABLE_NAME
,        uc.constraint_name;

To find Foreign key for a corresponding primary key

COL constraint_source FORMAT A38 HEADING "Constraint Name:| Table.Column"
COL references_column FORMAT A38 HEADING "References:| Table.Column"

SELECT   uc.constraint_name||CHR(10)
||  '('||ucc1.TABLE_NAME||'.'||ucc1.column_name||')' constraint_source
,       'REFERENCES'||CHR(10)
||  '('||ucc2.TABLE_NAME||'.'||ucc2.column_name||')' references_column
FROM     user_constraints uc
,        user_cons_columns ucc1
,        user_cons_columns ucc2
WHERE    uc.constraint_name = ucc1.constraint_name
AND      uc.r_constraint_name = ucc2.constraint_name
AND      uc.constraint_type = 'R'
AND      uc.constraint_name = UPPER('&input_constraint_name');

To view allocated, used & free space in a datafile

col file_name for a70
set linesize 142
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;

Query to find the failed logins

SQL> col OS_USERNAME for a20
SQL> col USERNAME for a20
SQL> col USERHOST for a20
SQL> set linesize 142
select os_username,
username,
userhost,
to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
returncode
from dba_audit_session
where action_name = 'LOGON'
and returncode > 0
order by timestamp ;

How to find a session with high archive logs

You can query v$sess_io and v$session to findout the the session which is generating lot of redo. i.e. lot of block changes occuring.

SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
 FROM v$session s, v$sess_io i
 WHERE s.sid = i.sid
 ORDER BY 5 desc

Also query v$transaction and v$session to find out the session that is generating lot of undo information.

Get description of oracle processes

SQL> select nm, max(description) descript
from
(select regexp_replace( name,'[0-9a-z]','#') nm, description
from v$bgprocess)
group by nm
order by nm;

How large is the database

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20
select round(sum(used.bytes)/1024/1024/1024)||'GB' "Database Size"
,    round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
,    round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from    (select    bytes
from    v$datafile
union    all
select    bytes
from     v$tempfile
union     all
select     bytes
from     v$log) used
,    (select sum(bytes) as p
from dba_free_space) free
group by free.p
/

Distribution of objects and data – Schema wise

set pages 999
col "size MB" format 999,999,999
col "Objects" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "size MB"
from (select owner, count(*) obj_cnt from 
                            dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024) seg_size
from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

Show the ten largest objects in the database

col    owner format a15
col    segment_name format a30
col    segment_type format a15
col    mb format 999,999,999
select  owner
,    segment_name
,    segment_type
,    mb
from    (
select    owner
,    segment_name
,    segment_type
,    bytes / 1024 / 1024 "MB"
from    dba_segments
order    by bytes desc
)
where    rownum < 11
/

Show all used features

select    name
,    detected_usages
from    dba_feature_usage_statistics
where     detected_usages > 0
/

Tablespace usage

set pages 999
col tablespace_name format a40
col "size MB" format 999,999,999
col "free MB" format 99,999,999
col "% Used" format 999
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from  dba_data_files group by tablespace_name union all
select  tablespace_name || '  **TEMP**'
, sum(bytes)/1024/1024 used_mb
from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/

Find AWR snapshot interval and retention settings

SQL> SELECT extract(day from snap_interval) *24*60
                           +extract(hour from snap_interval)*60
           +extract(minute from snap_interval) snapshot_Interval,
 extract(day from retention) *24*60
                                +extract(hour from retention)*60
              +extract(minute from retention) retention_Interval
 FROM dba_hist_wr_control;
Snapshot_Interval Retention_Interval
 —————–                 ——————
 60                      10080

Querying SGA and PGA info

SQL> select sum(value)/1024/1024 SGA_MB from v$sga;

When automatic shared memory management is enabled, Oracle will adjust the memory parameters on the fly. To see currently allocated sizes:

SQL> set linesize 142
SQL> col COMPONENT for a30
SQL> select * from v$sga_dynamic_components;

Find the usage by component

 select case when component is null then 'others'
 else component
 end component, round(sum(size_mb),1) size_mb, 
                                round(sum(used_mb),1) used_mb
 from (
 select case when name = 'buffer_cache' then 'db_buffer_cache'
 when name = 'log_buffer'   then 'log_buffer'
 else pool
 end component,
 ((bytes/1024)/1024) size_mb,
 case when name = 'buffer_cache'
 then (((bytes - (select count(*)
 from v$bh
 where status='free') *
 (select value
 from v$parameter
 where name = 'db_block_size')
 )/1024)/1024)
 when name != 'free memory' then ((bytes/1024)/1024)
 end used_mb
 from v$sgastat
 )group by component

IF AMM is enabled,

with data
as
(
select decode( grouping( pool ), 1, 'total:', pool ) "Pool",
sum(bytes) bytes
from (select nvl(pool,'*'||name) pool, bytes from v$sgastat )
group by rollup (pool)
)
select "Pool", bytes, round(bytes/1024/1024) mbytes
 from data
union all
 select 'PGA target', v-bytes, round((v-bytes)/1024/1024)
    from data, (select to_number(value) v
                  from v$parameter
                             where name = 'memory_target')
   where "Pool" = 'total:'

Viewing PGA sizes

select * from v$sysstat where name like 'workarea executions%'
select * From v$pgastat

Calculate datafile size to which it can be shrunk

SET LINES 1000 trims ON pages 0
 SELECT f.TABLESPACE_NAME,
 NVL(ROUND(f.bytes/1024/1024,0),0) AS fbytes ,
 NVL(ROUND(MAX(e.blocks + e.block_id)/1024/1024*t.block_size,0),0) AS used ,
 NVL(ROUND((f.bytes/1024/1024) - (MAX(e.blocks + e.block_id)/1024/1024*t.block_size),0),0) AS free,
 'ALTER DATABASE DATAFILE '''||file_name||''' RESIZE '||NVL(DECODE(ROUND(MAX(e.blocks + e.block_id)*t.block_size * 1.01 / 1024 / 1024,0),0,1,ROUND(MAX(e.blocks + e.block_id)*t.block_size * 1.01 / 1024 / 1024,0)+1),1)||'M;' AS "EXECUTE STATEMENT BELOW"
 FROM dba_extents e,
 dba_data_files f,
 dba_tablespaces t
 WHERE f.file_id = e.file_id(+)
 AND f.tablespace_name = t.tablespace_name
 GROUP BY file_name,f.tablespace_name,
 autoextensible,f.bytes,
 f.maxbytes, t.block_size ORDER BY 4

Checking Database Growth Trend

One of the simple way to find the growth of the database is using v$datafile view. following is the simple query and its output which gives the growth trend in month and year

select to_char(CREATION_TIME,'RRRR') year, 
       to_char(CREATION_TIME,'MM') month, 
       round(sum(bytes)/1024/1024/1024) GB
from   v$datafile
group by  to_char(CREATION_TIME,'RRRR'),   
          to_char(CREATION_TIME,'MM')
order by   1, 2;

Tracking Oracle database growth

select b.tsname tablespace_name , MAX(b.used_size_mb) cur_used_size_mb , 
round(AVG(inc_used_size_mb),2) avg_increas_mb 
from 
(SELECT a.days,a.tsname ,used_size_mb,
        used_size_mb - LAG(used_size_mb,1) 
OVER ( PARTITION BY a.tsname ORDER BY a.tsname,a.days) inc_used_size_mb 
from 
(SELECT TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY') days,
ts.tsname ,
MAX(round((tsu.tablespace_usedsize* dt.block_size)/(1024*1024),2)) 
used_size_mb 
from 
dba_hist_tbspc_space_usage tsu,dba_hist_tablespace_stat ts ,
dba_hist_snapshot sp, dba_tablespaces dt 
where tsu.tablespace_id= ts.ts# 
AND tsu.snap_id = sp.snap_id 
AND ts.tsname = dt.tablespace_name 
AND sp.begin_interval_time sysdate-7 
GROUP BY TO_CHAR(sp.begin_interval_time,'MM-DD-YYYY'), 
ts.tsname ORDER BY ts.tsname, days ) a
) b GROUP BY b.tsname ORDER BY b.tsname;