Scripts

Check Tablespace Size
select nvl(b.tablespace_name,

nvl(a.tablespace_name,'UNKOWN')) Tablespace_Name,
round(nvl(kbytes_free,0),2) free_space,
round(kbytes_alloc-nvl(kbytes_free,0),2) used_space,
round(kbytes_alloc,2) total_space,
round(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,2) pct_used
from ( select sum(bytes)/1024/1024 Kbytes_free,
max(bytes)/1024/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name order by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 5 desc;

Check Temp Tablespace Size

select TABLESPACE_NAME, sum(bytes_free/1024/1024) "Free Space", sum(bytes_used/1024/1024) "Used Space",
sum(bytes_free/1024/1024) + sum(bytes_used/1024/1024) "Total Space",
(sum(bytes_used/1024/1024)/(sum(bytes_free/1024/1024) + sum(bytes_used/1024/1024)) * 100) "Percentage used"
from v$temp_space_header group by TABLESPACE_NAME;

Check Invalid objects in Schema

set pagesize 1000
select 'alter '||object_type ||' '||object_name||' compile; ' from user_objects where status = 'INVALID';

select 'alter '||owner ||'.'||object_type ||' '||object_name||' compile; ' from DBA_objects where status = 'INVALID' order by owner;

Drop All boject in schema

set line 200
set pagesize 10000
select 'drop '||object_type||' '||object_name||decode(object_type,'TABLE',' cascade constraints ',' ')||';' from obj where object_type NOT IN ('INDEX'
,'PACKAGE BODY','TABLE PARTITION','INDEX PARTITION','LOB') order by decode(object_type,'TABLE',1,'SEQUENCE',2),object_name;

Check Locking on Db

                 col OBJECT_NAME for a30

                 col TERMINAL for a10

                 set line 200

                 set pagesize 1000

                 col OWNER for a15

                 col USERNAME for a20

                SELECT a.sid,a.serial#, a.username,a.status, b.OWNER,substr(b.object_name,1,40) object_name,

                c.os_user_name,a.terminal,b.object_id, a.sql_id , to_char(a.LOGON_TIME,'DD/MM/YYYY HH24:MI:SS')

                from v$session a, dba_objects b, v$locked_object c

                where a.sid = c.session_id

                and b.object_id = c.object_id;

Row Cache Hit Ratio

SELECT (1 - (SUM (getmisses) / SUM (gets))) * 100 "Row Hit Ratio" FROM v$rowcache;

Buffer Cache Hit Ratio

select round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0))) * 100,2) "Buffer Hit Ratio"
from v$sysstat;

Sessions Using Temp Space

col SPID for a10

col MODULE for a30

col PROGRAM for a20

col TABLESPACE for a20

col SID_SERIAL for a20       

col OSUSER for a10

col TABLESPACE for a10

col USERNAME for a10

set line 200

select * from (SELECT S.sid || ',' || S.serial# sid_serial, S.username, S.osuser, P.spid, S.module,P.program,

SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,

T.tablespace,COUNT(*) statements

FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P

WHERE T.session_addr = S.saddr AND S.paddr = P.addr AND T.tablespace = TBS.tablespace_name

GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,

P.program, TBS.block_size, T.tablespace ) where mb_used > 100

order by mb_used;

Check long running sessions

set lines 154 verify off

column sid format 999999

column opname format a16

column target format a30

column username format a12

column TIME_REMAINING format 999999 heading "Time|Remain"

column ELAPSED_SECONDS format 999999 heading "Elaps.|Sec"

column units format a6

column start_time heading "Start|Time|ddmon hh:mi" for a11

column LAST_UPDATE_TIME heading "Last|Updated|ddmon hh:mi" for a11

column sofar format 9999999

column TOTALWORK format 9999999 heading "Total|Work"

column qcsid format 99999

column work_done format a15 heading "So Far/|TotalWork"

column sid_qcsid format a11 heading "sid,qcsid"

set long 999999

set line 200

set pagesize 1000

select sid, SERIAL# ||','||qcsid sid_qcsid, decode(opname, 'Index Fast Full Scan', 'Index FFS', opname) opname,

 target, sofar || '/' || TOTALWORK work_done, UNITS, to_char(START_TIME, 'ddmon hh24:mi') start_time,

 to_char(LAST_UPDATE_TIME, 'ddmon hh24:mi') LAST_UPDATE_TIME, TIME_REMAINING, ELAPSED_SECONDS,

USERNAME, SQL_ID--, QCSID

from v$session_longops where time_remaining > 0

/

select SQL_TEXT from v$sql where SQL_ID='&SQL_ID';

Check Load in DB

select ss.sid,ss.serial#, ss.username, ss.osuser,ss.program, ss.sql_id,se.SID, VALUE/100 cpu_usage_seconds

from

   v$session ss,

   v$sesstat se,

   v$statname sn

where

   se.STATISTIC# = sn.STATISTIC#

and

   NAME like '%CPU used by this session%'

and

   se.SID = ss.SID

and

   ss.status='ACTIVE'

and

   ss.username is not null

order by VALUE desc;

Check Rows Processed

select SQL_ID,SID,SQL_HASH_VALUE from v$session where SID =&sid;

select SQL_ID,HASH_VALUE,ROWS_PROCESSED from v$SQL where SQL_ID='cpnubrh2gbmx4';

Check Recent DDLs Modifications

set pagesize 200 colsep ' '

col Owner format a20

col Object_Name format a30

col Last_DDL format a20

break on Owner

select Owner, Object_type

Object_Name,

Timestamp Last_DDL

from DBA_OBJECTS

where  SysDate - TO_DATE(TimeStamp,'YYYY-MM-DD:HH24:MI:SS') < 1

order by Owner, Object_Name

/

Explain Plan

EXPLAIN PLAN FOR SELECT last_name FROM employees;

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

Check User Usage

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

GROUP BY owner ORDER BY total_size DESC;

List the Objects Accessed by SID

set pagesize 300

select sid,serial#,username,status, process from v$session

WHERE USERNAME IS NOT NULL

order by STATUS DESC;

col sid format 999

col owner format a10

col object format a20

col type format a10

set linesize 300

/* ENTER THE SID TO CHECK */

select sid,owner,object,type

from v$access where sid = &sid

order by owner,type;

Database General Check

spool on;

spool c:\Database_check.txt;

set heading on;

prompt

Prompt 'Database Information'

prompt

set line 200

col PLATFORM_NAME for a50

select DBID,  NAME, CREATED, log_mode, DB_UNIQUE_NAME,DATABASE_ROLE, PLATFORM_NAME, FLASHBACK_ON from v$database;

prompt

Prompt 'Log file check'

prompt

col MEMBER for a50

set lines 120;

select l.GROUP# GP#,l.MEMBERS "#of Members", l.BYTES/1024/1024 "size(Mb)",l.STATUS "Status" , l.ARCHIVED "ARC" ,lf.type "TYPE" ,lf.member "MEMBER" from

v$log l, v$logfile lf

where l.GROUP# = lf.GROUP#;

select nvl(b.tablespace_name,

             nvl(a.tablespace_name,'UNKOWN')) Tablespace_Name,

        round(nvl(kbytes_free,0),2) free_space,

        round(kbytes_alloc-nvl(kbytes_free,0),2) used_space,

        round(kbytes_alloc,2) total_space,

        round(((kbytes_alloc-nvl(kbytes_free,0))/kbytes_alloc)*100,2) pct_used

from (  select sum(bytes)/1024/1024 Kbytes_free,

              max(bytes)/1024/1024 largest,

              tablespace_name

        from  sys.dba_free_space

        group by tablespace_name ) a,

     (  select sum(bytes)/1024/1024 Kbytes_alloc,

              tablespace_name

        from sys.dba_data_files

        group by tablespace_name order by tablespace_name )b

where a.tablespace_name (+) = b.tablespace_name

order by 5 desc;

prompt

prompt 'All Datafile and Temp file size and Status'

prompt

set lines 200

col name for a66

select df.FILE#,df.NAME,df.STATUS,df.BYTES/1024/1024"size in MB",ts.NAME

from v$datafile df,V$tablespace ts

where df.TS#=ts.TS#

union all

select tf.FILE#, tf.NAME,tf.STATUS,tf.BYTES/1024/1024"size in MB",ts.NAME

from V$tablespace ts,v$tempfile tf

where tf.TS#=ts.TS#;

Prompt

col TABLESPACE_NAME for a22

COL file_name for a60

set lines 200

select FILE_ID,FILE_NAME,TABLESPACE_NAME,BYTES/1024/1024"size in MB",STATUS,AUTOEXTENSIBLE,INCREMENT_BY,MAXBYTES

from dba_data_files

order by FILE_ID;

prompt

prompt 'Temp Tablespace Usage'

prompt

select TABLESPACE_NAME, sum(bytes_free/1024/1024) "Free Space", sum(bytes_used/1024/1024) "Used Space",

sum(bytes_free/1024/1024) + sum(bytes_used/1024/1024) "Total Space",

(sum(bytes_used/1024/1024)/(sum(bytes_free/1024/1024) + sum(bytes_used/1024/1024)) * 100) "Percentage used"

from v$temp_space_header group by TABLESPACE_NAME;

prompt

select TABLESPACE_NAME,SEGMENT_BLOCK,CURRENT_USERS,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS,MAX_BLOCKS,MAX_USED_BLOCKS,MAX_SORT_BLOCKS from v$sort_segment;

prompt

prompt 'Temp File Details'

prompt

col NAME for a55;

select tf.NAME, tsh.TABLESPACE_NAME, tf.STATUS, tf.ENABLED from v$tempfile tf , v$temp_space_header tsh

where tf.FILE# = tsh.FILE_ID;

prompt

prompt 'Library Cache Hit Ratio'

prompt

select (1-sum(reloads)/(sum(reloads)+sum(pins)))*100 as "Library Hit Ratio" from v$librarycache;

prompt

prompt 'Buffer Cache Hit Ratio'

prompt

select  round((sum(decode(name, 'consistent gets',value, 0)) +

                sum(decode(name, 'db block gets',value, 0)) -

                sum(decode(name, 'physical reads',value, 0))) /

       (sum(decode(name, 'consistent gets',value, 0)) + sum(decode(name, 'db block gets',value, 0))) * 100,2) "Buffer Hit Ratio"

from   v$sysstat;

prompt

prompt 'Row Cache Hit Ratio'

prompt

SELECT (1 - (SUM (getmisses) / SUM (gets))) * 100 "Row Hit Ratio" FROM v$rowcache;

prompt

prompt 'Object of Non-sys and Non-system user exists in SYSTEM/SYSAUX tablespace'

prompt

set lines 25;

select count(SEGMENT_NAME)

from sys.dba_segments

where owner not in ('MGMT_VIEW')

and tablespace_name='SYSTEM'

and segment_name not like '%SYS%' and segment_name not like '%$%'

and segment_type not like '%PARTITION%';

prompt

prompt 'Unanyalized indexes with analysed tables'

prompt

set lines 25;

select count(TABLE_NAME)

from  dba_indexes  where

owner not in  ('MGMT_VIEW') AND last_analyzed< sysdate -7

and

--Analyzed tables

TABLE_NAME not in

(

--Unanalyzed tables

select TABLE_NAME

from  dba_tables  where owner not in  ('MGMT_VIEW')

--and owner in ('TBAADM','CUSTOM')

AND last_analyzed< sysdate -7

)

order by last_analyzed;

prompt

prompt 'Number of Unanalyzed tables '

prompt

set lines 100;

select 'TOTAL '  || count(TABLE_NAME)  || ' TABLES' ,

'Not Analyzed during last Seven days' "Comments"

from  dba_tables  where owner not in  ('MGMT_VIEW')

--and owner in ('TBAADM','CUSTOM')

AND last_analyzed< sysdate -7;

prompt

prompt 'Number of Unanalyzed Indexes '

prompt

set lines 100;

select 'TOTAL '  || count(INDEX_NAME)  || ' INDEXES' ,

'Not Analyzed during last Seven days' "Comments"

from  dba_indexes  where owner not IN ('MGMT_VIEW')

--and owner in ('TBAADM','CUSTOM')

AND last_analyzed< sysdate -7;

prompt

prompt 'Check Constraints Validations'

prompt

select owner, constraint_name, constraint_type, table_name, status, invalid from dba_constraints

where owner not in ('SYS','SYSTEM','DBSNMP','TSMSYS','OUTLN','DIP') and status='DISABLE';

prompt

prompt 'Check Undo management'

prompt

set line 200

col name for a50

col value for a50

select name, value from v$parameter where value IS NOT NULL and name like '%undo%';

prompt

prompt 'SQL memory management'

prompt

select name, value from v$parameter where value IS NOT NULL and name like '%pga%';

select name, value from v$parameter where value IS NOT NULL and name like '%workarea%';

prompt

prompt 'Resumable space allocation'

prompt

select name, value from v$parameter where value IS NOT NULL and name like '%resumable%';

prompt

prompt 'Segment Space management'

prompt

select TABLESPACE_NAME, SEGMENT_SPACE_MANAGEMENT, STATUS from dba_tablespaces;

prompt

prompt 'SGA componant sizes'

prompt

select name, value from v$parameter where value IS NOT NULL and name like '%size%';

spool off;

exit