set verify off

set linesize 300

col sid format 99999

col username format a10

col logontime format a25

col "DB Machine/Apps Machine" format a23

col "DB User / OS User" format a35

accept trgtsid number default 0 prompt 'What is the SID : '

select to_char(s.logon_time,'mm/dd hh24:mi:ss') logontime,

        substr(i.host_name,1,to_number(instr(i.host_name,'.'))-1)||' / '||substr(s.machine,1,to_number(instr(s.machine,'.'))-1) "DB Machine / Apps Machine",

        last_call_et,

        s.sid,

        s.serial#,

        s.status,

        s.type,

        s.username||' / '||s.osuser "DB User / OS User",

        s.action,

        p.spid,

        s.sql_hash_value

from    gv$session s,

        gv$process p,

        gv$instance i

where sid = &trgtsid

and p.addr = s.paddr

and s.inst_id=i.inst_id

and p.inst_id=i.inst_id

/

[orapdev@apdbasrv01 scripts]$ cat sessinfo.sql

def aps_prog    = 'sessinfo.sql'

def aps_title   = 'Session information'

col "Session Info" form A85

set verify off

accept sid      prompt 'Please enter the value for Sid if known            : '

accept terminal prompt 'Please enter the value for terminal if known       : '

accept machine  prompt 'Please enter the machine name if known             : '

accept process  prompt 'Please enter the value for Client Process if known : '

accept spid     prompt 'Please enter the value for Server Process if known : '

accept osuser   prompt 'Please enter the value for OS User if known        : '

accept username prompt 'Please enter the value for DB User if known        : '

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)'|| ' (Since) '||to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS')||chr(10)||

       '   Client Program Name : '||s.program||chr(10) "Session Info",

           '   Action / Module     : '||s.action||'  / '||s.module||chr(10) ||'User Name : '||

      fu.description

  from v$process p,v$session s, apps.fnd_logins f, apps.fnd_user fu

 where p.addr = s.paddr

   and s.sid = nvl('&SID',s.sid)

   and nvl(s.terminal,' ') = nvl('&Terminal',nvl(s.terminal,' '))

   and nvl(s.process,-1) = nvl('&Process',nvl(s.process,-1))

   and p.spid = nvl('&spid',p.spid)

   and s.username = nvl('&username',s.username)

   and nvl(s.osuser,' ') = nvl('&OSUser',nvl(s.osuser,' '))

   and nvl(s.machine,' ') = nvl('&machine',nvl(s.machine,' '))

   and nvl('&SID',nvl('&TERMINAL',nvl('&PROCESS',nvl('&SPID',nvl('&USERNAME',

       nvl('&OSUSER',nvl('&MACHINE','NO VALUES'))))))) <> 'NO VALUES'

   and f.spid (+)  = nvl('&Process',s.process)

   and fu.user_id (+) = f.user_id

   and f.end_time(+) is null

order by f.start_time

/

undefine sid

[orapdev@apdbasrv01 scripts]$ cat create_datafile.sql

sqlplus / as sysdba

set pages 0 feedback 0

select 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '||''''||replace(name,'SUPROD','SUFDEV')||''''||';' from v$datafile;

spool $HOME/sufdev_datafile_newname.rman

/

spool off

exit

[orapdev@apdbasrv01 scripts]$ cat create_temp.sql

sqlplus / as sysdba

set pages 0 feedback 0

select 'SET NEWNAME FOR TEMPFILE '||FILE#||' TO '||''''||replace(name,'SUPROD','SUFDEV')||''''||';' from v$tempfile;

spool $HOME/sufdev_tempfile_newname.rman

/

spool off

exit

[orapdev@apdbasrv01 scripts]$ cat create_log.sql

set pages 0 feedback 0 line 150

select 'SQL "alter database rename file '''''||member||''''' to '''''||replace(member,'SUPROD','SUFDEV')||''''' ";' from v$logfile;

spool $HOME/sufdev_redofile_newname.rman

/

spool off

exit

[orapdev@apdbasrv01 scripts]$ cat cmrun.sql

rem ********************************************************************

rem * Filename          : jobs.sql - Version 1.4

rem * Author            : Virag Saksena

rem * Original          : 02-APR-95

rem * Last Update       : 20-DEC-95

rem * Description       : Information about jobs running on the system

rem * Usage             : start jobs.sql

rem ********************************************************************

set lines 132

col os form A7 head AppProc

col spid form a6 head DBProc

col program form A43 trunc

set pages 38

col time form 9999.99 head Elapsed

col "Req Id" form 9999999999

col "Parent" form a7

col "Prg Id" form 999999

col qname head "Concurrent Manager Queue" format a25 trunc

col sid format 99999 head SID

set recsep off

select q.concurrent_queue_name || ' - ' || target_node qname

      ,a.request_id "Req Id"

      ,decode(a.parent_request_id,-1,NULL,a.parent_request_id) "Parent"

      ,a.concurrent_program_id "Prg Id"

      ,a.phase_code,a.status_code

      ,b.os_process_id "OS"

      ,vs.sid

      ,vp.spid

      ,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 "Time"

      ,c.concurrent_program_name||' - '||

       c2.user_concurrent_program_name "program"

from APPLSYS.fnd_Concurrent_requests a,APPLSYS.fnd_concurrent_processes b

    ,applsys.fnd_concurrent_queues q

    ,APPLSYS.fnd_concurrent_programs_tl c2

    ,APPLSYS.fnd_concurrent_programs c

    ,v$session vs

    ,v$process vp

where a.controlling_manager = b.concurrent_process_id

  and a.concurrent_program_id = c.concurrent_program_id

  and a.program_application_id = c.application_id

  and c2.concurrent_program_id = c.concurrent_program_id

  and a.phase_code in ('I','P','R','T')

  and b.queue_application_id = q.application_id

  and b.concurrent_queue_id = q.concurrent_queue_id

  and c2.language = 'US'

  and vs.process (+) = b.os_process_id

  and vs.paddr = vp.addr (+)

order by 1,2

/

[orapdev@apdbasrv01 scripts]$ cat compile_all_use.sql

SQL> set head off

SQL> select 'alter '||decode(object_type,'PACKAGE BODY','PACKAGE',object_type)||' '|| owner||'.'||object_name||' compile'||decode(object_type,'PACKAGE BODY',' body;',';')

  2  from dba_objects where status='INVALID'

  3  order by object_type

  4  /

SQL> set head on

SQL> spool off

[orapdev@apdbasrv01 scripts]$ cat autofree.sql

col TABLESPACE_NAME format a20

Select  a.tablespace_name,  b.tot_space, a.free_space

From ( Select tablespace_name , sum ( bytes/1024/1024 )  free_space

        From dba_free_space

         group by tablespace_name ) a,

      ( Select tablespace_name, sum ( bytes/1024/1024 ) tot_space

         From dba_data_files

         group by tablespace_name ) b

where a.tablespace_name = b.tablespace_name

and ( a.free_space / b.tot_space ) * 100 < 20

/

select sum( BYTES_USED/1024/1024) TEMP_USED,sum(BYTES_FREE/1024/1024) TEMP_FREE from V$TEMP_SPACE_HEADER where tablespace_name='TEMP' group by tablespace_name;

--select tablespace_name,sum(bytes/1024/1024) TEMP_USED,sum(maxbytes/1024/1024) ACTUAL_SPACE,sum(maxbytes/1024/1024)-sum(bytes/1024/1024) TEMP_FREE from dba_temp_files group by tablespace_name;

[orapdev@apdbasrv01 scripts]$ cat tsusage.sql

column file_name format a45

column tablespace_name format a10

set verify off

accept tbs prompt 'What is the Tablespace Name: '

SELECT  dts.tablespace_name,

NVL(ddf.bytes / 1024 / 1024, 0) avail,

NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,

NVL(dfs.bytes / 1024 / 1024, 0) free,

TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')

"Used %" ,

TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes

* 100, 0), '990.00') free_pct,

decode(sign(

(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /

1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0))  "Required MB"

FROM

sys.dba_tablespaces dts,

(select tablespace_name, sum(bytes) bytes

from dba_data_files group by tablespace_name) ddf,

(select tablespace_name, sum(bytes) bytes

from dba_free_space group by tablespace_name) dfs

WHERE

dts.tablespace_name = ddf.tablespace_name(+)

AND dts.tablespace_name = dfs.tablespace_name(+)

and dts.tablespace_name = '&tbs';

select file_name,bytes/1024/1024 from dba_data_files

where tablespace_name = '&tbs';

select distinct autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like '&tbs';

[orapdev@apdbasrv01 scripts]$ cat hashsql.sql

set verify off

accept trgthash number default 0 prompt 'What is the SQL Hash Value : '

set linesize 500

select t.sql_text

from v$sqltext_with_newlines t

where t.hash_value + 0 = &trgthash

and &trgthash != 0

order by t.piece;

[orapdev@apdbasrv01 scripts]$ cat my.sql

set pages 66 lines 132

column Tablespace_Name format A20

column "Free %" format 999.99

column "Largest Chunk" format 9999.999

column "Free Space" format 9,999,999.999

column "Tot Space" format 9,999,999.999

column auto_mb_left hea 'Auto_MB_Left' format 9,999,999.999

compute sum of "Free Space" on report

compute sum of "Tot Space" on report

break on report

ttitle off

select Tablespace_Name,

        nvl(Sum_Alloc_Bytes,0)/(1024*1024) "Tot Space",

        nvl(Sum_Free_Bytes,0)/(1024*1024) "Free Space",

        nvl(Max_Bytes,0)/(1024*1024) "Largest Chunk",

        Count_Chunks,

        auto_mb_left,

        (100*((nvl(Sum_Free_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))/((nvl(Sum_Alloc_Bytes,0)/1024/1024)+nvl(auto_mb_left,0))) AS "Free %"

from

        (select Tablespace_Name, SUM(Bytes) Sum_Alloc_Bytes

                from DBA_DATA_FILES

                group by Tablespace_Name),

        (select Tablespace_Name FS_TS_NAME,

                MAX(Bytes)  AS Max_Bytes,

                COUNT(Bytes)  AS Count_Chunks,

                SUM(Bytes) AS Sum_Free_Bytes

                from DBA_FREE_SPACE

                group by Tablespace_Name),

        ( SELECT d.tablespace_name auto_tbspname,

                floor(sum(decode(sign( ((d.maxbytes-d.bytes)/1024/1024) - (d.increment_by*to_number(p.value)/1024/1024) ),

                        -1,0,((d.maxbytes-d.bytes)/1024/1024)))) auto_MB_Left

          FROM dba_data_files d,  v$parameter p

          WHERE p.name='db_block_size'

            and d.autoextensible='YES'

          GROUP BY d.tablespace_name) b

where Tablespace_Name = FS_TS_NAME (+)

and Tablespace_name = auto_tbspname (+)

order by 7 desc;

[orapdev@apdbasrv01 scripts]$ cat countinvalid.sql

SELECT OWNER, COUNT(*)FROM DBA_OBJECTS WHERE STATUS = 'INVALID'GROUP BY OWNER;

rem set heading off;

prompt Total Invalid Ojbects:

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

set heading on;

rem SELECT OWNER, COUNT(*)FROM DBA_OBJECTS WHERE STATUS = 'INVALID'GROUP BY OWNER;

rem set heading on;

[orapdev@apdbasrv01 ~]$ cat env_backup.sh
export dir=env_bkp_03oct14
cd $HOME/$dir
cp $CONTEXT_FILE $HOME/$dir
cp $TNS_ADMIN/*.ora $HOME/$dir
cp $ORACLE_HOME/*.env $HOME/$dir
cp -r $ORACLE_HOME/appsutil/ $HOME/$dir
echo " List of env files copied under $HOME/$dir"
sqlplus -s /nolog <<EOF
connect /as sysdba
set head off
set feed off
set lines 300
col file_name for a85
spool $HOME/$dir/dbinfo.lst
select 'Datafile Location' from dual;
select file_name from dba_data_files;
select 'Redolog Location' from dual;
select member from v\$logfile;
select 'Tempfile Location' from dual;
select file_name from dba_temp_files;
select 'Controlfile location'a from dual;
select name from v\$controlfile;
spool off
exit
EOF
[orapdev@apdbasrv01 ~]$ cat stopdb.sh
#
# Go to the script directory
#
cd  $ORACLE_HOME/appsutil/scripts/APDEV_apdbasrv01
echo "Checking APPS processes belong to apsuprod. The following processes are still running, please stop them first. Script exit."
     PIDS=`ps -eaf | grep APPS | grep apsuprod | awk '{print $2}'`
          if [ "$PIDS" ] > 0
           then
            #  kill -9 $PIDS
             echo $PIDS
             exit;
          fi
echo ""
echo "Please make sure all processes for apsuprod are stopped properly"
echo ""
#
# Stop the database listener
#
./addlnctl.sh stop APDEV
sleep 15
#
# Extremely CAREFUL to modify this to kill oracle processes
#
echo "Killing External Database processes."
     PIDS=`ps -eaf | grep LOCAL=NO | grep orsuprod | awk '{print $2}'`
          if [ "$PIDS" ]
           then
              kill -9 $PIDS
            # echo $PIDS
          fi
#
# Stop database
#
./addbctl.sh stop immediate
[orapdev@apdbasrv01 ~]$ cat startdb.sh
#Created to start the database after cold backups
cd $ORACLE_HOME/appsutil/scripts/APDEV_apdbasrv01
./addbctl.sh start
./addlnctl.sh start APDEV
echo
echo "Started Processes: "
ps -efw |grep ora_ |grep orapdev
ps -efw |grep tns |grep APDEV
[orapdev@apdbasrv01 ~]$ cat space.sh
du -sch /d03/app/oradata/APDEV
du -sch /d04/app/oradata/APDEV
du -sch /d05/app/oradata/APDEV
du -sch /d06/app/oradata/APDEV
du -sch /d08/app/oradata/APDEV
du -sch /d10/app/oradata/APDEV
[orapdev@apdbasrv01 ~]$ cat 12c_APDEV.sh
export ORACLE_HOME=/d01/app/oracle/APDEV/product/12.1.0
export ORACLE_SID=APDEV
export PATH=/d01/app/oracle/APDEV/product/12.1.0/bin:$PATH
export TNS_ADMIN=/d01/app/oracle/APDEV/product/12.1.0/network/admin/APDEV_apdbasrv01
[orapdev@apdbasrv01 ~]$ cat hhealthcheck.sh
echo "Please wait ....."
export timestamp=`date +%d%m%Y`;
export yesterday=`date --date="-1 day"`
rm ./healthcheck_*2016
echo $ORACLE_SID
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo "-------------DB HEALTH CHECK---------------------" >> ./healthcheck_$timestamp
date >> ./healthcheck_$timestamp
hostname >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo ""
echo ""
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo "----------------DISK USAGE--------------------" >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
df -h >> ./healthcheck_$timestamp
echo ""
echo ""
echo ""
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo "----------------APMON-------------------------" >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
ps -ef|grep kbwa* >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo "----------------DEFUNC-------------------------" >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
ps -ef|grep defunc >> ./healthcheck_$timestamp
echo ""
echo ""
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo "----------------TABLESPACE--------------------" >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
sqlplus / as sysdba << EOF  >> ./healthcheck_$timestamp
set line 200;
select host_name,instance_name,status from v\$instance;
select name,open_mode,database_role from v\$database;
@./autofree.sql
@./temp_tbs.sql
archive log list
select count(*) from dba_objects where status ='INVALID';
@\$ORACLE_HOME/rdbms/admin/utlrp.sql
select count(*) from dba_objects where status ='INVALID';
show parameter background_dump_dest
EOF
echo "-------------Alert Log Report-----------------" >> ./healthcheck_$timestamp
#alert_dgrd.log |grep -n --max-count=1  "`date --date="-1 day" +"%b %_d"`"
#adrci exec="show alert -p originating_timestamp > systimestamp-1;1;"
#showalert()
#adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
#   show alert -p \\\"originating_timestamp > systimestamp-1\\\""
#showalert >> ./healthcheck_$timestamp
echo "----------------------------------------------" >> ./healthcheck_$timestamp
echo "Health Check Report File Generated as  ./healthcheck_$timestamp"
echo -n "Do you want to view this file Now?(y or n)"
read ans
if [ $ans = y ]; then
cat ./healthcheck_$timestamp
fi
[orapdev@apdbasrv01 ~]$ cat locks_wait.sql
set serveroutput on size 10000
declare
  cursor c1 is
    select * from v$lock where request != 0
      order by id1, id2;
  wid1            number := -999999;
  wid2            number := -999999;
  wholder_detail  varchar2(120);
  v_err_msg       varchar2(80);
  wsid            number(5);
  wstep           number(2);
  wtype           varchar2(10);
  wobject_name    varchar2(180);
  wobject_name1   varchar2(80);
  wlock_type      varchar2(50);
begin
  for c1_rec in c1 loop
    if c1_rec.id1 = wid1 and c1_rec.id2 = wid2 then
       null;
    else
       wstep  := 10;
       select sid , type into wsid , wtype
         from v$lock
         where id1  = c1_rec.id1
           and id2  = c1_rec.id2
           and request = 0
           and lmode != 4;
      dbms_output.put_line('  ');
       wstep  := 20;
      select 'Holder DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process
              ||' SID: '|| s.sid||' Status: ' || s.status  ||' ty:'||wtype
          into wholder_detail
          from v$session s, v$process p
          where s.sid= wsid
            and s.paddr = p.addr;
      dbms_output.put_line(wholder_detail||' ID1='||to_char(c1_rec.id1)
                          ||', ID2='|| to_char(c1_rec.id2));
      begin
        select decode(wtype,'TX', 'Transaction',
                            'DL', 'DDL Lock',
                            'MR', 'Media Recovery',
                            'RT', 'Redo Thread',
                            'UN', 'User Name',
                            'TX', 'Transaction',
                            'TM', 'DML',
                            'UL', 'PL/SQL User Lock',
                            'DX', 'Distributed Xaction',
                            'CF', 'Control File',
                            'IS', 'Instance State',
                            'FS', 'File Set',
                            'IR', 'Instance Recovery',
                            'ST', 'Disk Space Transaction',
                            'TS', 'Temp Segment',
                            'IV', 'Library Cache Invalida-tion',
                            'LS', 'Log Start or Switch',
                            'RW', 'Row Wait',
                            'SQ', 'Sequence Number',
                            'TE', 'Extend Table',
                            'TT', 'Temp Table',
                            'Un-Known Type of Lock')
           into wlock_type
           from dual;
        declare
          cursor c3 is
            select object_id from v$locked_object
              where session_id = wsid;
        begin
          wobject_name := '';
          for c3_rec in c3 loop
            select object_type||': '||owner||'.'||object_name
              into wobject_name
              from dba_objects
              where object_id = c3_rec.object_id;
            wobject_name := wobject_name ||' '||wobject_name1;
          end loop;
        exception
          when others then
            wobject_name := wobject_name ||' No Object Found';
        end;
        dbms_output.put_line('Lock Held: '||wlock_type||' for Object :'||wobject_name);
      exception
        when no_data_found then
          dbms_output.put_line('Lock Held: '||wlock_type||' No object found in DBA Objects');
      end;
    end if;
       wstep  := 30;
    select '....   Requestor DBU: '||s.username ||' OSU: '||s.osuser ||' DBP:'||p.spid||' APP: '|| s.process
              ||' SID: '|| s.sid||' Status: ' || s.status
          into wholder_detail
          from v$session s, v$process p
          where s.sid= c1_rec.sid
            and s.paddr = p.addr;
    dbms_output.put_line(wholder_detail);
    wid1  := c1_rec.id1;
    wid2  := c1_rec.id2;
  end loop;
  if wid1 = -999999 then
       wstep  := 40;
    dbms_output.put_line('No one requesting locks held by others');
  end if;
exception
  when others then
         v_err_msg := (sqlerrm ||'  '|| sqlcode||' step='||to_char(wstep));
    DBMS_OUTPUT.PUT_LINE(v_err_msg);
end;
/