sql output to csv. (with comma in the column data).

 

Script to generate csv output. this scripts covers comma's in the column value to print it correct.

set pagesize 1000

set linesize 1000

set feedback off

set heading off

set echo off

select 'ABC_xyz.csv' spool_name  from dual;

spool &spool_name

select '"sysdate",

'||'"col_name_1",

'||'"col_name_2",

'||'"col_name_3",

'||'"col_name_4",

'||'"col_name_5"' from dual

union all

select distinct '"'||sysdate||'",'

||'"'||col_name_1||'",'

||'"'||col_name_2||'",'

||'"'||col_name_3||'",'

||'"'||col_name_4||'",'

||'"'||to_char(B.col_name_5,'DD-MON-YYYY HH24:MI:SS')||'"'

FROM Table A;

spool off;

Script to find parent-child relationships between tables in oracle.

 Edit the below script and provide the inputs when executed 

********************************************************************

accept tablename prompt 'Enter the table name:'

accept tableowner prompt 'Enter the table owner name:'

SET LINESIZE 255

set verify off

col GIVEN_TABLE for a30

col PARENT_TABLE for a30

col CHILD_TABLE for a30

col CHILD_TABLE_OWNER for a20

col GIVEN_TABLE_OWNER for a20

col PARENT_TABLE_OWNER for a20

col PARENT_TAB_CONST for a30

col CHILD_TAB_FK_CONST for a30

col G_TAB_columns for a30

col GIVEN_FK_CONST_NAME for a30

col G_TAB_FK_COLUMNS for a30

col GIVEN_TAB_CONST for a30

Prompt Below are the Child tables for &tablename table.

prompt -----------------------------------------------

SELECT p.table_name GIVEN_TABLE, p.owner GIVEN_TABLE_OWNER, p.constraint_name GIVEN_TAB_CONST,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_columns

,c.owner CHILD_TABLE_OWNER,c.table_name CHILD_TABLE,c.constraint_name CHILD_TAB_FK_CONST

FROM dba_constraints p, dba_constraints c,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and p.table_name=t.table_name

and p.constraint_name=t.constraint_name

AND p.table_name = UPPER('&tablename')

and p.owner=('&tableowner')

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

Prompt Below are the Parent tables for &tablename table.

prompt ------------------------------------------------

SELECT c.table_name GIVEN_TABLE,c.owner GIVEN_TABLE_OWNER, c.constraint_name GIVEN_FK_CONST_NAME,

LISTAGG(t.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY t.COLUMN_NAME) as G_TAB_FK_columns,p.owner PARENT_TABLE_OWNER,p.table_name PARENT_TABLE

,p.constraint_name PARENT_TAB_CONST

FROM dba_constraints p, dba_constraints c ,dba_cons_columns t

WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')

AND c.constraint_type = 'R'

AND p.constraint_name = c.r_constraint_name

and c.table_name=t.table_name

and c.constraint_name=t.constraint_name

AND c.table_name = UPPER('&tablename')

and c.owner='&tableowner'

group by c.table_name ,c.owner , p.table_name ,p.owner ,c.constraint_name,p.constraint_name;

***********************************************************

script to Block users from logging to schema directly from sql developer or Toad

Edit below script to work as per the need: 

CREATE OR REPLACE TRIGGER BLOCK_USER_LOGIN

AFTER LOGON ON Schema_NAME

DECLARE

v_prog sys.v$session.program%TYPE;

v_name sys.v$session.osuser%TYPE;

BEGIN

SELECT program INTO v_prog

FROM v$session a,dba_users b

where 

a.username=b.username and

(a.program like 'SQL D%' or a.program like 'TOAD') and

AND  a.audsid != 0;

 

IF (v_prog) LIKE '%SQL Dev%'    

THEN

insert into UNFI_TEST_LOGON_DETAILS values (v_prog,sysdate);

commit;

--RAISE_APPLICATION_ERROR(-20983, 'You are not allowed to login as schema owner.');

END IF;

END;

/

to drop the changes....

drop trigger BLOCK_USER_LOGIN;

mysql: script to email when sessions in mysql instance are high or reached defined max value.

 Edit once you have copied to Vi Editor

1) session_count.sql

***************************************

tee ./logs/session.log

select count(*) from information_schema.processlist;

notee;

**********************************

2 ) Session_count.sh

**********************************************************************

#!/bin/ksh

#########################################################################

# PROGRAM NAME: session_count.sh   for MySQL

#

# USAGE: ./session_count.sh 

#

#########################################################################

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d%H%M")

hostname=`hostname -s`

MAILTO=USER_SIVA@ABC.com

PWD=/opt/siva/scripts/sessions  # put script dir path if you want to run from "crontab"

repdir=$PWD

LOG=$repdir/logs

if [ ! -d $LOG ]; then

mkdir  $LOG

else 

rm $LOG/session.log

if [ -f $LOG/session_details.html ]; then

rm $LOG/session_details.html

fi

fi

####### ******************** Below list of changes are req:

conf_file=/etc/my.cnf

BACKUP_USER=TEST_USER     # can be root.

BACKUP_PASS=`cat /opt/.orapasswd/.backupusr`     # to hide password

Vald_sess_C=200             # max value limit to email.

log_ret=30                  # retention for logfiles

Logfile=mysql_Session_"$hostname"_$DATE.log

echo " started $DATE" >>$LOG/$Logfile

#### Changes are done:

####### ******************** run Active file:

if [ -f $LOG/bkp_active_file ]

 then

   echo "previous mysql session_count.sh script is still running. confirm before running again" | mailx -s "Mysql-Session Status: $hostname - Failed to execute" $MAILTO

   exit 0

fi

###

touch $LOG/bkp_active_file

mysql -Ns -u TEST_USER -p$BACKUP_PASS < $repdir/session_count.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1 

####### ******************** Check the session count:

Check_Sess_C=`cat $LOG/session.log`

if [ $Check_Sess_C -gt $Vald_sess_C ];

then

mysql -H -u TEST_USER -p$BACKUP_PASS < $repdir/session_details.sql >> $LOG/mysql_Session_"$hostname"_$DATE.log 2>&1

####### ******************** Email Output to Mail:

( echo " HOST: $hostname - MySQL Sessions are High - Details: "

  echo "To: USER_SIVA@ABC.com"

  echo "From:  <$hostname> "

   echo "MIME-Version: 1.0"

   echo "Content-Type: text/html; charset=US-ASCII"

echo "<html>"

echo "<body>"

   echo "<font size="+2">" "<font color="red">"

   echo "<u>"

   echo "Mysql sessions are high - Count - $Check_Sess_C ."

   echo "</u>"

   echo "<br/>"

   echo "<font size="-1">" "<font color="black">"

   echo "<pre>"

   cat $LOG/session_details.html

   echo  "<pre>"

   echo "Successful end of $repdir/session_count.sh"

echo "</body>"

echo "</html>"

 ) | /usr/sbin/sendmail $MAILTO

### End of Email

fi

####### ******************** Purge Log directory:

if [ ! -d $LOG ]; then

mkdir  $LOG

else

/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;

fi

####### ******************** Remove run active file:

if [ -f $LOG/bkp_active_file ]

 then

rm $LOG/bkp_active_file

echo " removed run-active file" >> $LOG/$Logfile

fi

exit

### All steps are Done.

***************************************************************

Mysql : Script to restore backup using MEB.

 Edit once you have copied to Vi editor...

******************************************************************

#!/bin/ksh

#########################################################################

# PROGRAM NAME: mysql-meb-restore_auto.sh 

#

# USAGE: Mysql- Restoring databases from MEB backup. 

#

##########################################################################

#

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d%H")

hostname=`hostname -s`

MAILTO=USER_SIVA@ABC.com

PWD=/opt/siva/scripts/restore  # put script dir path if you want to run from "crontab"

repdir=$PWD

LOG=$repdir/dblogs

echo $LOG

## *********************** Below list of changes are req:

conf_file=/etc/my.cnf

BACKUP_USER=TEST_USER

BACKUP_PASS=`cat /opt/.orapasswd/.backupusr`       # to hide password

datadir=/opt/data/mysql   #### make sure this is correct.

bkup_dir_a=/backup/test/TESTDB/meb/FULL/      # Backup location hardcoded:

innodb_log_file_v=1610612736                  # change innodb_log_file_size as  in the backup file 

Logfile=mysql_restore_auto_"$hostname"_$DATE.log

log_ret=15                                    # retention for logfiles.

echo  "started $DATE" >>$LOG/$Logfile

## Disable below lines if $bkup_dir is hardcoded:   below lines will automatically select last night backup.

p_date=$(date --date='-1 day' '+%Y-%m-%d')

bk_dir="$p_date*"

bkup_dir_f=`find $bkup_dir_a -type d -name "$bk_dir"`

bkup_dir=$bkup_dir_f

echo $bkup_dir

#bkup_dir=/backup/test/TESTDB/meb/FULL/2020-07-20_23-00-08   # disable above line and enable this line if you want to mention backup directroy.

########## Changes are done:

#**** Check if Backup directory exists:

if [ ! -d "$bkup_dir" ]; then

echo "backup directory $bkup_dir doesn't exists" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

exit;

else

echo "$bkup_dir exists" 

fi

###

## ***************** run Active file:

if [ -f $LOG/bkp_active_file ]

 then

   echo "previous mysql restore/backup is still running. confirm before running again" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

   exit 0

fi

### checking Logs Directory:

if [ ! -d $LOG ]; then

mkdir  $LOG

else

/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;

fi

####

## ************************************************* Prep Steps before Start of Restore:

touch $LOG/bkp_active_file

####### Checking MySQL Instance status:

echo "Step 1: checking MySQL Instance status:"

inst_up=`ps -ef| grep mysqld.pid |wc -l`

if [ "$inst_up" -gt 1 ]; then

echo "Mysql instance is up. Shutting down now."

sudo systemctl stop mysqld

RETCODE=$?

print  "RETCODE:  $RETCODE"

if [ "$RETCODE" -ne 0 ]; then

   print  "echo MySQL Instance did not shutdown cleanly. please check" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

rm $LOG/bkp_active_file

exit 0;

fi

else

echo "Mysql instance is not up"

fi

#### 

### Prep step for Mysql Restore:

echo "Step 2: cleaning the $datadir for restore:"

cd $datadir

sudo /usr/bin/rm -r $datadir/* 

sudo /usr/bin/rm -r /data/log/*

####

## ************************************************* Start Of MySQL Restore:

echo "Step 3: MySQL restore started:"

/opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=/etc/my.cnf --port=1234 --host=localhost --user=$BACKUP_USER --password=$BACKUP_PASS --datadir=/data/data/mysql --innodb_log_file_size=$innodb_log_file_v --with-timestamp --backup-dir=$bkup_dir --messages-logdir=$LOG copy-back-and-apply-log >>$LOG/$Logfile 2>&1

####

## *********************** Check Any errors in the logfile:

echo "Step 4: Checking errors in Restore:"

error_chk=$(cat $LOG/$Logfile | awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /WARNING/{print buf,$0}' | wc -l)

if [[ $error_chk -le 1 ]]

  then

     echo "Mysql Restore completed successfully - ignorable warning(1) - $error_chk " |tee -a $LOG/$Logfile

   else

    echo "Mysql Restore  was unsuccessful - has more errors than required(1). - error count - $error_chk" |tee -a $LOG/$Logfile

    echo "Mysql Restore  was unsuccessful - has more errors than required(1). - error count - $error_chk . please check" | mailx -s "Mysql-Restore Status: Completed with errors" $MAILTO

fi

####

## ********************** Remove run active file:

if [ -f $LOG/bkp_active_file ]

 then

rm $LOG/bkp_active_file

echo " removed run-active file" |tee -a $LOG/$Logfile

fi

## ************************************************* Post steps after MySQL Restore: 

echo "Step 5: Changing $datadir permissions:"

sudo chown -R mysql.mysql $datadir              # enable this if you are using different owner than mysql.

echo "Step 6: Starting MySQL Instance:"

sudo systemctl start mysqld

RETCODE=$?

print  "RETCODE:  $RETCODE"

if [ "$RETCODE" -ne 0 ]; then

   print  "MySQL Instance did not start cleanly. please check" | mailx -s "Mysql-Restore Status: $hostname - Failed" $MAILTO

exit 0;

fi

## *********************** Final MySQL status:

inst_up=`ps -ef| grep mysqld.pid |wc -l`

if [ "$inst_up" -gt 1 ]; then

echo "Mysql-Restore Status: All steps are done" |tee -a $LOG/$Logfile

echo "Mysql instance is up - After-Restore - Final-step Done " | mailx -s "Mysql-Restore Status: $hostname - completed" $MAILTO

else

echo "Mysql instance is not up - After-Restore - Final-step failed" | mailx -s "Mysql-Restore Status: $hostname -failed " $MAILTO 

fi

####

## All steps Done: End

Script to backup / resize Alert log file and purge the old logs.

Edit the lines once copied to the editor. 

******************************** Script **********************

#!/bin/ksh

# ***********--------------------------------------------------************#

# *********** Script name      :                                           #

# *********** Author           : Siva B                                    #

# *********** Usage            : To backup alert log and re-size to 0.     #

# *********** Date             :                                           #

#                                                                          #

# *********** Version Change History                                       #

#                                                                          #

#                                                                          #

# ***********--------------------------------------------------************#

# *********** Define variables:

#       OS variables:

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d")

hostname=`hostname -s`

PWD=/opt/siva/alert_test

repdir=$PWD

LOG=$repdir/logs

################ Change Retention Here ###########

ORACLE_BASE=/opt/app/oracle

BKP_ALT_DIR=/opt/siva/alert       # to save the alert log file in some other mount/disk.

DB_NAME=TEST_DB                   # Disable this incase if you want to run for all DB's on this host and enable below.

############ Done ########################

# Log directory checks:

if [ ! -d ${LOG} ]

 then

    mkdir -p $LOG

  else

    rm $LOG/*log

fi

if [ ! -d ${BKP_ALT_DIR} ]

 then

    mkdir -p $BKP_ALT_DIR

fi

# copying alert log files to desired folder:

# enable below line if you want to backup alert log file and reset for all db's on this host.

 # for db in `cat /etc/oratab| egrep -v '(^#|\+|\-)'|cut -f1 -d: -s|egrep -v 'OMS'|egrep -v 'AGENT'`

   for db in `echo $DB_NAME` 

      do

         db_check=`pgrep -al pmon|grep $db |cut -f3,4 -d_`

          if [ -n "$db_check" ]

              then

              echo $db

              db_sid=$db_check

              l_db=`echo $db | tr [A-Z] [a-z]` 

               alert_dir=$ORACLE_BASE/diag/rdbms/$l_db/$db_check/trace 

                  cd $alert_dir

                  echo $alert_dir

                  echo "$BKP_ALT_DIR"

                  cp alert_$db_check.log $BKP_ALT_DIR/alert_"$db_check"_$DATE.log 

  gzip $BKP_ALT_DIR/alert_"$db_check"_$DATE.log

                echo "alert_$db_check.log copy is done for $db" >>$LOG/DB_up_list.log

            ready=0

              while [ $ready -eq 0 ]

                   do

                 if [ `lsof | grep $ORACLE_BASE/diag/rdbms/$l_db/$db_check/trace/alert_$db_check.log |grep -v tail |wc -l` != 0 ];

                           then

                         echo "`lsof | grep $ORACLE_BASE/diag/rdbms/$l_db/$db_check/trace/alert_$db_check.log |grep -v tail |wc -l`"

                            ready=0

                              sleep 5

                    else

                            ready=1

                  fi

              done

                  echo "resetting alert_$db_check.log for $db" >>$LOG/DB_up_list.log

                  > alert_$db_check.log

                  echo "alert_$db_check.log reset for $db is done" >>$LOG/DB_up_list.log

           else

               echo "$db" >>$LOG/exclude_list.log

          fi

   done

echo "purge older logs "

/usr/bin/find $BKP_ALT_DIR -type f -mtime +90 -exec rm {} \; 

echo "completed:" >>$LOG/DB_up_list.log

find sql's with multiple hash values in last one hour.

 --- Below code will tell us if we have multiple plans for a given sql_id when looked at a sql_id 

select sql_id,sum(executions_delta),avg(TPE),plan_hash_value,app from

(

SELECT sql_id,executions_delta,

round((elapsed_time_delta)/decode((executions_delta*1000000),0,1,(executions_delta*1000000)),6) TPE,plan_hash_value

,CASE(A.module) when 'JDBC Thin Client'  then 'JDBC' else A.module end app 

FROM  dba_hist_sqlstat A

JOIN  dba_hist_snapshot ss

  ON  A.snap_id = ss.snap_id

WHERE 

(ss.end_interval_time between (sysdate -1/24) and sysdate ) and 

 A.module not in ('em? agent_SQL_oracle_database','emagent_AQMetrics','Oracle Enterprise Manager.Metric Engine') 

  and rownum <200

order by 2 desc)

group by sql_id,plan_hash_value,app

order by 1 desc;

-- Details:-

SELECT sql_id,

to_char(begin_interval_time,'DD HH24:MI') begin_interval_time,to_char(ss.end_interval_time,'DD HH24:MI') end_interval_time,executions_delta,

round((elapsed_time_delta)/decode((executions_delta*1000000),0,1,(executions_delta*1000000)),6) TPE,plan_hash_value

,CASE(A.module) when 'JDBC Thin Client'  then 'JDBC' else A.module end app 

FROM  dba_hist_sqlstat A

JOIN  dba_hist_snapshot ss

  ON  A.snap_id = ss.snap_id

WHERE 

(ss.end_interval_time between (sysdate -1/24) and sysdate ) and 

 A.module not in ('em? agent_SQL_oracle_database','emagent_AQMetrics','Oracle Enterprise Manager.Metric Engine') 

 and rownum <200

order by 2 desc;

Blocking session script for Oracle

Below code is used to find the blocker and waiters in the Oracle Database.

code:-

select chn_id,sid,ses_serial,status,blkr_sid,Final_BLK_SES,wait_event,Process,osuser,sql_id,Machine,program,Wait_Secs,sql_text from (SELECT wc.chain_id CHN_ID,
 wc.sid,
  wc.sess_serial# ses_serial,
 CASE
   WHEN DECODE(blocker_sid,NULL,'',blocker_sid) IS NULL
   THEN 'blocker'
   ELSE 'waiter'
 END AS status,
  DECODE(blocker_sid,NULL,'',blocker_sid) blkr_sid,
  s.final_blocking_session Final_BLK_SES,
  wait_event_text wait_event,
  s.Process,
  s.osuser,
  s.sql_id,
 SUBSTR(s.machine,1,INSTR(s.machine,'.',1)-1) Machine ,
 SUBSTR(s.program,1,INSTR(s.program,'.',1)-1) program,
  in_wait_secs Wait_Secs
 ,dbms_lob.substr(q.sql_text,50,1) sql_text
FROM v$wait_chains wc, gv$session s,gv$session bs,gv$instance i,gv$process p ,gv$sql q
WHERE wc.instance              = i.instance_number (+)
AND s.sql_id                   =q.sql_id(+)
AND (wc.instance               = s.inst_id (+)
AND wc.sid                     = s.sid
AND wc.sess_serial#            = s.serial# (+))
AND (s.final_blocking_instance = bs.inst_id (+)
AND s.final_blocking_session   = bs.sid (+))
AND (bs.inst_id                = p.inst_id (+)
AND bs.paddr                   = p.addr (+))
AND ( num_waiters              > 0
OR ( blocker_osid             IS NOT NULL
AND in_wait_secs               > 10 ) ) ---- change here for secs in wait
  CONNECT BY PRIOR wc.sid      = blocker_sid
AND PRIOR wc.sess_serial#      = blocker_sess_serial#
AND PRIOR i.inst_id            = blocker_instance
  START WITH blocker_is_valid  = 'FALSE')
  group by chn_id,sid,ses_serial,status,blkr_sid,Final_BLK_SES,wait_event,Process,osuser,sql_id,Machine,program,Wait_Secs,sql_text
  ORDER BY chn_id,wait_secs DESC;

Thank you,

Sivaram