First Day and Last Day of Current Month Oracle

Query to Select the First Day and Last Day of the Current Month

 

select trim(to_date(last_day(sysdate),’DD/MM/YYYY’)) as lastday from dual;

select trunc((sysdate),’month’) as FirstDay from dual;

RLWRAP SQL*PLUS to have BASH like Functionality in SQLPLUS

So you are an Oracle DBA and have got used to the luxury of BASH shell in nix systems. Where you can scroll up and down and commands from a time long forgotten magically appear. Wouldn’t it be wonderful to have a similar thing for good ole sqlplus. Well here’s how you do it, via an excellent utility on linux called rlwrap.

1. Download rlwrap from this location : http://utopia.knoware.nl/~hlub/uck/rlwrap/

Incase above URL is not available, you can refer the steps in this link to install rlwrap

https://www.pythian.com/blog/rlwrap-utility-dba/

2. untar to a directory and compile the utility

tar xvf rlwrap-0.42.tar.gz

cd rlwrap-0.42

./configure

make

make install

3. Login with Oracle user and add the below alias to your bash profile

alias sqld=’rlwrap sqlplus “/as sysdba”‘

4. Login with sqld command (which is an alias by wrapping sqlplus using rlwrap, you can do this manually as well)

And happily scroll up and down through the sqlplus command line.

 For manually doing it

$ rlwrap sqlplus

Enjoy !

Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites

1. Oracle client should be installed on the Unix/Linux Server

2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands

3. All databases should have one common user with a common password (this method is not the safest method
since the password will be in plain text in your Shell script)
Now let us proceed to first create a text file called dbnodes.txt which will have the TNSNAMES for the
database we will connect to..

boston
chicago
newyork

Above is the entries in the dbnodes.txt file, these are the databases where we will loop the sql commands

Create a sql script with the commands you have to run on all the databases, lets call the file script.sql

set echo on
set linesize 200
set pages 0
select sysdate from dual;
select user from dual;
select instance_name,host_name from gv$instance;
alter system set cpu_count=8 scope=both sid='*';
show parameter cpu_count;
alter session set nls_date_format='DD/MM/YYYY';
exit;

I do this to dynamically to cap the cpu_count on some of our databases to contain any resouce hogging.

Now the final shell script which will call the dbnodes.txt and script.sql to loop the above sql commands
through all databases. The shell script is called dbloop.sh

#!/bin/bash
cat dbnodes.txt | while read line
do
sqlplus -s user/user123@$line @/u03/scripts/script.sql
done

Copy all 3 files dbnodes.txt, script.sql and dbloop.sh to one directiry and run it like below; to collect the log of the SQL commands.

$--> dbloop.sh > dbrun.log

This will spool the output to a logfile.

So there you see it, one of the easiest ,method to run a set ofcommon sql commands on hundreds of server. I use this script to do basics like checking a certain parameters on all our production databases, to check dataguard status for multiple production DB’s etc. The use cases of this script is unlimited. Hope you enjoyed it 🙂 Keep it Easy and Keep It Oracle !

Generate Excel | CSV Report from SQL*PLUS

Shell Script to generate a column seperator report in SQL*PLUS using a shell script. It can be opened in Excel and or text editor like NOTEPAD++

 

#!/bin/ksh
. $HOME/.bash_profile
NOW=$(date +”%a %b %e %T %Y”)
HOST=$(hostname)
MON2=$(date +”%b%Y”)

cd $HOME/scripts
rm -rf Report_$MON2.csv

sqlplus -s /NOLOG << EOF > /tmp/log.txt
CONNECT abc/*****

set colsep ;
set pagesize 1000
set trimspool on
set headsep off
set linesize 9999

alter session set nls_date_format = ‘DD/MM/YYYY’;
spool Report_$MON2.csv
select * from tablename where condition;
spool off
exit
EOF

Generating HTML Reports from SQL*Plus

This is a script to query a table, generate the result in HTML and send an email using mailx for the report.

 

#!/bin/ksh
. $HOME/.bash_profile
NOW=$(date +”%a %b %e %T %Y”)
MON=$(date +”%b %Y”)
MON2=$(date +”%b%Y”)
HOST=$(hostname)

cd $HOME/scripts
rm -rf  Report*.html

sqlplus -s /NOLOG << EOF > /tmp/log.txt
CONNECT abc/********

set pagesize 1000

set markup HTML ON HEAD “<style type=’text/css’> –
<title>Card Report</title> –
body { –
font:10pt Arial,Helvetica,sans-serif; –
color:blue; background:white; } –
p { –
font:8pt Arial,sans-serif; –
color:grey; background:white; } –
table,tr,td { –
font:10pt Arial,Helvetica,sans-serif; –
text-align:right; –
color:Black; background:white; –
padding:0px 0px 0px 0px; margin:0px 0px 0px 0px; } –
th { –
font:bold 10pt Arial,Helvetica,sans-serif; –
color:#336699; –
background:#cccc99; –
padding:0px 0px 0px 0px;} –
h1 { –
font:16pt Arial,Helvetica,Geneva,sans-serif; –
color:#336699; –
background-color:White; –
border-bottom:1px solid #cccc99; –
margin-top:0pt; margin-bottom:0pt; padding:0px 0px 0px 0px;} –
h2 { –
font:bold 10pt Arial,Helvetica,Geneva,sans-serif; –
color:#336699; –
background-color:White; –
margin-top:4pt; margin-bottom:0pt;} –
a { –
font:9pt Arial,Helvetica,sans-serif; –
color:#663300; –
background:#ffffff; –
margin-top:0pt; margin-bottom:0pt; vertical-align:top;} –
.threshold-critical { –
font:bold 10pt Arial,Helvetica,sans-serif; –
color:red; } –
.threshold-warning { –
font:bold 10pt Arial,Helvetica,sans-serif; –
color:orange; } –
.threshold-ok { –
font:bold 10pt Arial,Helvetica,sans-serif; –
color:green; } –
</style> –
<title>Database Report $MON2</title>” –
BODY “<img src=http://www.acme.com/company_logo..gif/>” –
TABLE “border=’1′ width=’90%’ align=’center'” –
ENTMAP OFF SPOOL ON
spool Report_$MON2.html
alter session set nls_date_format = ‘DD/MM/YYYY’;
select * from tablename where condition;
spool off
exit
EOF
cd /export/home/oracle/scripts
echo “Report generated on $NOW for Department from $HOST. Use Notepad++ to open CSV file.” > /export/home/oracle/scripts/output
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “.”
echo>>output “*** This is an automatically generated message. You have received this message because you are a member of the e-mail notification subscription list for t
his server. Please do not reply. ***”

 

(cat /export/home/oracle/scripts/output ; uuencode Report_$MON2.html Report_$MON2.html)
| mailx -s “Monthly Report for the Month of $MON ” abc@domain.com
exit