dotmaik1

##################################################################################
# Name        : GSS_FULL_DB.sh 
# Location    : /home/oracle/scripts/mxoptix1/GSS
# Created By  : [ Wipro DBA Team ]
# Purpose     : GSS for FULL DB - MXAPPS[PROD]  
#
###################################################################################
mail_list="[email protected],[email protected]"

echo -e "\nGSS for FULL DB Process Started at `date` \n \n \n" > /home/oracle/scripts/mxapps1/GSS/FULL_DB_GSS.log

dotmaik1

set verify off
accept trgthash number default 0 prompt 'What is the SQL Hash Value : '
select t.sql_text 
from v$sqltext_with_newlines t
where t.hash_value + 0 = &trgthash
and &trgthash != 0
order by t.piece;

dotmaik1

set lines 200
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);

dotmaik1

#!/bin/bash
export ORACLE_SID=mxapps1
export ORACLE_HOME=/u001/MXDB/oracle/product/11.2.0/db
export PATH=/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/etc/opt/emcpower/bin:/u001/MXDB/oracle/product/11.2.0/db:/u001/MXDB/oracle/product/11.2.0/db/bin
export FECHA=`date '+%d%m%Y_%H%M%S'`

$ORACLE_HOME/bin/sqlplus -s "/as sysdba" <<EOF

SET PAGES 2000 LINES 200
SET SERVEROUTPUT ON

dotmaik1

# set -x
# Shell script to monitor or watch the disk space
# name: fs_check.sh
# umbrales: 85-89% WARNING; 90-99% SERIOUS; 100% CRITICAL
# ===================================================================================================#
# Set admin email so that you can get email.
email="[email protected]"
# Include list of FS to monitoring, if several partions then use "|" to separate the partitions.
INCLUDE_LIST="/u01|/u04|/u05|/oradata"
#

dotmaik1

set lines 200 pages 200
col state for a15
col seq# for 999999
col P1_P2_P3_TEXT for a30
col event for a20
select w.sid, to_char(p.spid,'99999') PID,
       substr(w.event, 1, 28) event, substr(s.username,1,8) username,
       substr(s.osuser, 1,8) osuser,
       substr(w.state,1,20),
       w.seq#,

dotmaik1

--Scheduler JOBS

set lines 200
set pages 500
column OWNER format a15
column SCHEDULE_NAME format a25
column START_DATE format a50
column REPEAT_INTERVAL format a20
SELECT OWNER, JOB_NAME, JOB_STYLE, SCHEDULE_NAME, START_DATE, REPEAT_INTERVAL,  STATE FROM DBA_SCHEDULER_JOBS;

dotmaik1

set lines 200
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
round((end_time-start_time)*1440,2) "Minutes"
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key desc;

dotmaik1

rman target /
crosscheck archivelog all;
crosscheck backupset;
catalog recovery area;
delete expired archivelog all;

dotmaik1

select extract(day from (sysdate - min(trunc(time))) day to second) + 1
from v$restore_point
/
I loaded the result into an environment variable.  (Many thanks to whoever posted the code for that on this site.  Experts Exchange rocks!)

Then I used RMAN to delete the archivelogs like this:
delete archivelog all completed before 'SYSDATE - $RESTORE_POINT_AGE';


rman