Day -DBA scripts-fragmentation

set head off
set feedback off
spool kill_&a_sess_i1.sql
select 'spool kill_&a_sess.log' from dual;
select 'set feedback on' from dual;
select 'alter system kill session '||chr(39)||sid||','||serial#||chr(39)||';'
from v$session
where username='&a';
select 'spool off' from dual;
spool off
@kill_&a_sess_i1.sql
spool chk_Rajtest_sess_i1.log
select 'set feedback on' from dual;
select sid, serial# from v$session where username='Rajtest';
spool off

Rebuild index

set feedback off

set echo off

set serverout off

set pages 0

set lines 200 

col name new_val dbname noprint

select name from v$database;

spool rebuild_idx_&&dbname\.sql

prompt spool rebuild_idx_&&dbname

select 'alter index '||owner||'.'||table_name||' rebuild tablespace '||tablespace_name||'_NEW;' 

from dba_indexes 

where tablespace_name not in ('SYSTEM', 'SYSAUX', 'UNDOTBS1','USERS','TEMP')

and index_type != 'LOB'

and tablespace_name in (select tablespace_name from dba_tablespaces where bigfile='NO')

order by tablespace_name, table_name; 

prompt spool off

spool off

Tablespace usage

set pages 1000

set lines 133

set pagesize 80

set wrap off

ttitle center 'Weekly Tablespace Monitoring' skip 2 -

center 'List of Tablespaces where "Space Used" > 80%' skip 2

col tablespace_name format a30

col TABLESPACE_NAME heading "Tablespace|Name"

col Allocated_size heading "Allocated|Size(GB)" form 99999999.99

col Current_size heading "Current|Size(GB)" form 99999999.99

col Used_size heading "Used|Size(GB)" form 99999999.99

col Available_size heading "Available|Size(GB)" form 99999999.99

col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99

select a.tablespace_name

        ,a.alloc_size/1024/1024/1024 Allocated_size

        ,a.cur_size/1024/1024/1024 Current_Size

        ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size

        ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size

        ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used

from     dba_tablespaces t

        ,(select t1.tablespace_name

        ,nvl(sum(s.bytes),0) used

        from  dba_segments s

        ,dba_tablespaces t1

         where t1.tablespace_name=s.tablespace_name(+)

         group by t1.tablespace_name) u

        ,(select d.tablespace_name

        ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size

        ,sum(d.bytes) cur_size

        ,count(*) file_count

        from dba_data_files d

        group by d.tablespace_name) a

where t.tablespace_name=u.tablespace_name

and ((u.used+a.file_count*65536)*100)/a.alloc_size>50

and t.tablespace_name=a.tablespace_name

order by t.tablespace_name;

user login

set pages 0

 set lines 150

 set linesize 150

 set term on

 set feedback off

select 'sample_time,username,program,machine' from sys.dual;

SELECT

   sample_time||','||

   u.username||','||

   h.program ||','||

   h.MACHINE

FROM

   DBA_HIST_ACTIVE_SESS_HISTORY h,

   DBA_USERS u,

   DBA_HIST_SQLTEXT s

WHERE  sample_time > sysdate -30

AND

   INSTANCE_NUMBER in ( '1','2')

   AND  u.USERNAME not in ('SYS','SYSTEM','DBSNMP','SYSMAN','PRD')

   AND h.user_id=u.user_id

   AND h.sql_id = s.sql_iD

ORDER BY  sample_time ASC;

Fragmentation

set linesize 200 trimspool on
col owner for a22
col table_name for a30
col tablespace_name for a20
select t.owner,t.table_name, s.tablespace_name,
round(s.bytes/1024/1024) actual_MB,
round((t.num_rows+1) * t.avg_row_len/1024/1024) optimal_MB,
round(s.bytes/1024/1024) -
round((t.num_rows+1) * t.avg_row_len/1024/1024) CLAIMABLE_MB
from dba_tables t, dba_segments s
where t.owner = s.owner and s.owner='&owner_name'
and  t.table_name = s.segment_name
and round(s.bytes/1024/1024) - round((t.num_rows+1) * t.avg_row_len/1024/1024) > 200
order by CLAIMABLE_MB desc
/

long running sql

col os_username for a12
col os_pid for 99999
col session_machine for a15
col session_program for a15
col current_sql for a120
SELECT
s.sid sid
, s.serial# serial_id
, s.status session_status
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
, s.machine session_machine
, s.program session_program
, s.client_info 
, SUBSTR(sa.sql_text, 1, 1024) current_sql
FROM
v$process p
, v$session s
, v$sqlarea sa
WHERE
p.addr (+) = s.paddr
AND s.sql_address = sa.address(+)
AND s.sql_hash_value = sa.hash_value(+)
AND s.audsid <> userenv('SESSIONID')
AND s.username IS NOT NULL
AND s.status = 'ACTIVE'
ORDER BY sid;

CPU usage 

set linesize 200
set pagesize 120
col module for a60
SELECT   mymodule "Module", SUM (cpu_time) "CPU Time", SUM (wait_time) "Wait 
Time",
         SUM (cpu_time) + SUM (wait_time) "Total Time"
    FROM (SELECT a.module mymodule,
                 (CASE (session_state)
                     WHEN 'ON CPU'
                        THEN wait_time / 100
                  END
                 ) cpu_time,
                 (CASE (session_state)
                     WHEN 'WAITING'
                        THEN time_waited / 100
                  END
                 ) wait_time
            FROM dba_hist_active_sess_history a, dba_hist_snapshot b
           WHERE b.end_interval_time > sysdate-1
             AND a.snap_id = b.snap_id
             AND a.user_id NOT IN (0, 5)
             AND a.instance_number = b.instance_number)
GROUP BY mymodule
  HAVING SUM (cpu_time) + SUM (wait_time) > 0
ORDER BY 2 DESC;