#!/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