#!/bin/bash
cat textfile.txt |while read Name
do sqlplus -s <a href="mailto:userid/password@db_name">userid/password@db_name</a> > output.log <<EOF
set verify off
set heading off
select customerid
from customers
where customername like '%${Name}%';
/ exit EOF
SET LINESIZE 145
SET PAGESIZE 9999
SET VERIFY off
COLUMN instance_name FORMAT a13 HEAD 'Instance|Name / Number'
COLUMN thread# FORMAT 99999999 HEAD 'Thread #'
COLUMN host_name FORMAT a13 HEAD 'Host|Name'
COLUMN status FORMAT a6 HEAD 'Status'
COLUMN startup_time FORMAT a20 HEAD 'Startup|Time'
COLUMN database_status FORMAT a8 HEAD 'Database|Status'
COLUMN archiver FORMAT a8 HEAD 'Archiver'
COLUMN logins FORMAT a10 HEAD 'Logins?'
COLUMN shutdown_pending FORMAT a8 HEAD 'Shutdown|Pending?'
COLUMN active_state FORMAT a6 HEAD 'Active|State'
COLUMN version HEAD 'Version'
SELECT
instance_name || ' (' || instance_number || ')' instance_name
, thread#
, host_name
, status
, TO_CHAR(startup_time, 'DD-MON-YYYY HH:MI:SS') startup_time
, database_status
, archiver
, logins
, shutdown_pending
, active_state
, version
FROM
gv\$instance
ORDER BY
instance_number
/
This is located in a common library
#!/usr/bin/ksh
# Purpose: Search the alert log of $ORACLE_SID for error
# messages.
# If any error messages are found, send notification
# (see Notes below).
#
# Prereqs: $ORACLE_SID environment variable must be set or
# passed as a positional parameter. Also, $ORATAB
# environment variable must be set.
#
# Notes: If this script is invoked from a terminal session,
# display the results to the screen (STDOUT); otherwise,
# send a mail message to the specified mail accounts.
#
# Exit Status: 99 = Required input, $ORACLE_SID, not supplied.
# 90 = Invalid Oracle sid.
# 95 = init.ora file not found for input sid.
# 97 = Alert log file does not exist.
#
# Author: Michael Culp
# Date: May 2002
#===============================================================
# R E V I S I O N H I S T O R Y
#===============================================================
# Who:
# Date:
# Comments:
################################################################
function error_notification
{
if [ "$TERMINAL" = "Y" ]; then
echo "$ERROR_MSG"
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
echo "$ERROR_MSG" | mail $(sendvar)
done
fi
}
export SEND_MAIL_TO='mike.culp@gmail.com'
export SEND_MAIL_TO2='mike.culp@gmail.com'
SERVER=$(uname -n)
DATE=$(date +%c%t)
ERROR_MSG_FILE=/home/oracle/alertlog_message
# check to see if command is being executed interactively
/usr/bin/tty -s
STATUS=$?
if [ $STATUS = 0 ]
then
clear
TERMINAL=Y
else
TERMINAL=N
fi
# Test for required inputs.
# Positional parameters override env variables.
if [ ${1:-0} = 0 ]; then
if [ ${ORACLE_SID:-0} = 0 ]; then
ERROR_MSG=`echo "ORACLE_SID must be supplied as input to $0"`
error_notification
exit 99
fi
else
export ORACLE_SID=$1
fi
# Call the validate_sid function to verify ORACLE_SID is valid.
# The function also sets the ORACLE_HOME and ORACLE_BASE variables.
validate_sid
if [ "$VALID_SID" != "Y" ]; then
ERROR_MSG=`echo "ORACLE_SID ($ORACLE_SID) is invalid. Script is $0"`
error_notification
exit 90
fi
INIT_PATH="$ORACLE_HOME"/dbs
INIT_FILE="$INIT_PATH"/init"$ORACLE_SID".ora
if [ ! -s "$INIT_FILE" ]; then
ERROR_MSG=`echo "$INIT_FILE does not exist. Script is $0"`
error_notification
exit 95
fi
LOG_PATH="/"`awk -F= '/background_dump_dest/ { print $2 }' "$INIT_FILE" |tr -s " "|cut -f2-30 -d/`
ALERTLOG="$LOG_PATH"/alert_"$ORACLE_SID".log
# Create a list of valid Oracle error message prefixes by parsing
# the oerr script in $ORACLE_HOME/bin
/home/oracle/bin/awk_message_prefixes > /tmp/ora_prefixes
# Archiver error message prefix is not included in the oerr
# script. Add an entry for this into the "ora_prefixes" file.
echo '^ARC[0-9]: Error' >> /tmp/ora_prefixes
# Search the alert.log for any occurrences of an Oracle error
# message prefix.
grep -bsf /tmp/ora_prefixes $ALERTLOG > $ERROR_MSG_FILE
status=$?
if [ $status -eq 1 ]; then # no matches found
if [ $TERMINAL = 'Y' ]; then
echo "Amazingly enough, there are NO errors in $ALERTLOG"
fi
elif [ $status -gt 1 ]; then
ERROR_MSG=`echo "$ALERTLOG does not exist."`
error_notification
exit 97
fi
# If there are any messages in the message file, send
# appropriate notifications.
if [ -s $ERROR_MSG_FILE ]; then
echo "\nMessages from server $SERVER on $DATE" >> $ERROR_MSG_FILE
if [ $TERMINAL = 'Y' ]; then
cat $ERROR_MSG_FILE
else
env|grep SEND_MAIL_TO|while read LINE
do
alias sendvar="echo "`echo $LINE|awk 'BEGIN { FS="=" }; { print "$" $1 }'`
mail $(sendvar) < $ERROR_MSG_FILE
done
fi
fi
if [ -f $ERROR_MSG_FILE ]; then
rm $ERROR_MSG_FILE
fi
if [ -f /tmp/ora_prefixes ]; then
rm -f /tmp/ora_prefixes
fi
exit
exec DBMS_STATS.GATHER_TABLE_STATS( ownname=>'SCHEMA_OWNER',
tabname=>'SOME_TABLE',
degree=>16,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => 'FOR ALL COLUMNS SIZE AUTO',
granularity => 'AUTO',
cascade => TRUE,
no_invalidate => FALSE);
#!/bin/bash
while read line
do /opt/oracle/bin/sqlplus -s <a href="mailto:user@db/password">user@db/password</a> @query.sql $line
done < /tmp/textfile.txt
BEGIN
FOR c IN (SELECT c.owner,
c.table_name,
c.constraint_name
FROM user_constraints c,
user_tables t
WHERE c.table_name = t.table_name AND
c.status = 'ENABLED'
ORDER BY c.constraint_type DESC)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' disable constraint ' || c.constraint_name);
END LOOP;
END;
/
BEGIN
FOR c IN (SELECT c.owner, c.table_name, c.constraint_name
FROM user_constraints c, user_tables t
WHERE c.table_name = t.table_name AND
c.status = 'DISABLED'
ORDER BY c.constraint_type)
LOOP
dbms_utility.exec_ddl_statement('alter table ' || c.owner || '.' || c.table_name || ' enable constraint ' || c.constraint_name);
END LOOP;
END; /
SELECT DECODE(value,
NULL,
'PFILE',
'SPFILE') "Init File Type"
FROM sys.v_$parameter
WHERE name = 'spfile';
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
set linesize 200
col host_name for a45
select INST_ID,
INSTANCE_NAME,
HOST_NAME,
STARTUP_TIME DATABASE_STATUS,
status
from gv\$instance;
Here is a nice little script I like to use for showing the database size for various components.
#!/bin/ksh ############################################################################
# Script Name..: db_overall_size.ksh
# Description..:
# Author.......:
# Date.........: 04/10/2013
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
# ############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 200
set pages 200
spool db_size.txt set feed off
column systot format 99,990.99 heading "System |Alloc|GB"
column sysused format 99,990.99 heading "System |Used|GB"
column systm format 99,990.99 heading "System Tbsp|MB"
column sysax format 99,990.99 heading "Sys Aux|GB"
column systmusd format 99,990.99
column sysused format 99,990.99
column undotb format 99,990.99 heading "Undo|GB"
column datatot format 99,990.99 heading "Data|Alloc|GB"
column dataused format 99,990.99 heading "Data|Used|GB"
column tmptot format 99,990.99 heading "TEMP|Alloc|GB"
column redotot format 99,990.99 heading "Online|Redo|MB"
column ctltot format 99,990.99 heading "Ctrl File|MB"
column systall format 99,990.99 column systused format 99,990.99
column fratot format 99,990.99 column fraused format 99,990.99
column total_gb format 99,990.99 column free_gb format 99,990.99
select name from v\$database;
select s.system_size SYSTOT,
s.system_size-f1.free_mb SYSUSED,
st.system_size SYSTM,
sa.system_size SYSAX,
ud.system_size UNDOTB,
d.data_size DATATOT,
d.data_size-f2.free_mb DATAUSED,
t.temp_size TMPTOT,
r.redo_size REDOTOT,
c.controlfile_size CTLTOT,
s.system_size+t.temp_size+r.redo_size+c.controlfile_size SYSTALL,
s.system_size-f1.free_mb+t.temp_size+r.redo_size+c.controlfile_size SYSTUSED,
fra.fratot FRATOT,
fra.fraused FRAUSED
from (select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like 'SYSTEM' or
tablespace_name like 'SYSAUX' or
tablespace_name like 'UNDO%' or
tablespace_name like 'TOOLS' or
tablespace_name like 'USERS') s,
(select sum(bytes)/1024/1024/1024 data_size
from dba_data_files
where tablespace_name not like 'SYSTEM' and
tablespace_name not like 'SYSAUX' and
tablespace_name not like 'UNDO%' and
tablespace_name not like 'TOOLS' and
tablespace_name not like 'USERS') d,
(select sum(bytes)/1024/1024 system_size
from dba_data_files
where tablespace_name like 'SYSTEM' ) st,
(select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like 'SYSAUX' ) sa,
(select sum(bytes)/1024/1024/1024 system_size
from dba_data_files
where tablespace_name like 'UNDO%' ) ud,
(select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files) t,
(select sum(bytes)/1024/1024 redo_size
from sys.v\$log) r,
(select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v\$controlfile) c,
(select sum(bytes)/1024/1024/1024 FREE_MB
from dba_free_space
where tablespace_name like 'SYSTEM' or
tablespace_name like 'SYSAUX' and
tablespace_name like 'UNDO%' and
tablespace_name like 'TOOLS' and
tablespace_name like 'USERS') f1,
(select sum(bytes)/1024/1024/1024 FREE_MB
from dba_free_space
where tablespace_name not like 'SYSTEM' and
tablespace_name not like 'SYSAUX' and
tablespace_name not like 'UNDO%' and
tablespace_name not like 'TOOLS' and
tablespace_name not like 'USERS') f2,
(select name, (SPACE_LIMIT)/1024/1024/1024 FRATOT, (space_used)/1024/1024/1024 FRAUSED
from V\$RECOVERY_FILE_DEST) fra;
spool off
EOF
Here is a little header that I like to use for my shell scripts for Oracle code,
feel free to suggest mods
#!/bin/ksh ############################################################################ # Script Name..: <script name>.ksh # Description..: # Author.......: Michael Culp # Date.........: 04/ /2013 # Version......: # Modified By..: # Date Modified: # Comments.....: # Schema owner.: # : alter session set current should be used to change schemas # Login User...: # Run Order....: # Dependent on.: # Script type..: # ############################################################################ sqlplus -s "/ as sysdba" <<EOF set lines 150 set pages 150 -- spool <some file name> -- spool off EOF
Compression Check Script
#!/bin/ksh
############################################################################
# Script Name..: comp_chk.ksh
# Description..: Estimate the compression for a table
# Author.......: Michael Culp
# Date.........:
# Version......:
# Modified By..:
# Date Modified:
# Comments.....:
# Schema owner.:
# Login User...:
# Run Order....:
# Dependent on.:
# Script type..:
#
############################################################################
sqlplus -s "/ as sysdba" <<EOF
set lines 150
set pages 150
set serveroutput on
-- spool <some file name>
declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_compression.GET_COMPRESSION_RATIO(SCRATCHTBSNAME=>'USERS',
OWNNAME=>'CFGADM',
TABNAME=>'EQUIPMENT',
PARTNAME=>null,
COMPTYPE=>2, ---2 means OLTP
BLKCNT_CMP =>lv_BLKCNT_CMP,
BLKCNT_UNCMP =>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR);
dbms_output.put_line('====================================================');
dbms_output.put_line('1. Compression Ratio.: '||lv_cmp_ratio);
dbms_output.put_line('2. Block Count.......: '||lv_blkcnt_cmp);
dbms_output.put_line('3. Compression Type.......: '||lv_comptype_str);
dbms_output.put_line('4. Blk Count Compressed...: '||lv_BLKCNT_CMP);
dbms_output.put_line('5. Blk Count Un-compressed: '||lv_BLKCNT_UNCMP);
dbms_output.put_line('6. Row Count Compressed : '||lv_row_cmp);
dbms_output.put_line('4. Row Count Un-Compressed: '||lv_row_uncmp);
dbms_output.put_line('====================================================');
end;
/
-- spool off
EOF
#!/bin/ksh
sqlplus -s "/ as sysdba" <<EOF
select sw.sid sid,
p.spid spid,
s.username username,
s.osuser osuser,
sw.event event,
s.machine machine,
s.program program,
decode(sw.event,'db file sequential read', sw.p3, 'db file scattered read', sw.p3, null) blocks
from v\$session_wait sw,
v\$session s,
v\$process p
where s.paddr = p.addr
and event not in ('pipe get','client message')
and sw.sid = s.sid
EOF