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 :

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

2. untar to a directory and compile the utility

tar xvf rlwrap-0.42.tar.gz

cd rlwrap-0.42



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..


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';

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

cat dbnodes.txt | while read line
sqlplus -s user/user123@$line @/u03/scripts/script.sql

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

$--> > 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++


. $HOME/.bash_profile
NOW=$(date +”%a %b %e %T %Y”)
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

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.


. $HOME/.bash_profile
NOW=$(date +”%a %b %e %T %Y”)
MON=$(date +”%b %Y”)
MON2=$(date +”%b%Y”)

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=>” –
TABLE “border=’1′ width=’90%’ align=’center'” –
spool Report_$MON2.html
alter session set nls_date_format = ‘DD/MM/YYYY’;
select * from tablename where condition;
spool off
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 ”

Add string to query Oracle 11g

Suppose you have a query result and want to concatenate a string to that output for better readibility, it can be done using the Concat operator in SQL*PLUS


Select Name, Size  from Table ;


EMV_DATA                                          23
LOG_RECORD_HIST                          6
EMVX                                                       5
CARDHOLDER_TRAN                       4
EJ_LOG                                                   3
ISO_MSG                                                3
CARD_TRAN_IDX2                           2
ACCT_IX                                                2
CARD_TRAN_IDX                              2


Now if you want to add a string “GB” at the end of the result set of  second column the SQL statement will be as below

Select Name, Size || ‘ GB’  from Table;

EMV_DATA                                          23 GB
LOG_RECORD_HIST                         6 GB
EMVX                                                      5 GB
CARDHOLDER_TRAN                      4 GB
EJ_LOG                                                  3 GB
ISO_MSG                                               3 GB
CARD_TRAN_IDX2                          2 GB
ACCT_IX                                                2 GB
CARD_TRAN_IDX                             2 GB





ORA-29701: unable to connect to Cluster Synchronization Service

Error on starting ASM from SQLPLUS or ASMCMD

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service
SQL> exit


As Grid User

$ crsctl start res ora.cssd
CRS-2672: Attempting to start ‘ora.cssd’ on ‘prmdb’
CRS-2672: Attempting to start ‘ora.diskmon’ on ‘prmdb’
CRS-2676: Start of ‘ora.diskmon’ on ‘prmdb’ succeeded

$ asmcmd
Connected to an idle instance.
ASMCMD> startup
ORA-00099: warning: no parameter file specified for ASM instance

ASM instance started

Total System Global Area 317333504 bytes
Fixed Size 2221120 bytes
Variable Size 289946560 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

$ sqlplus “/as sysasm”

SQL*Plus: Release Production on Thu Nov 15 12:53:09 2012

Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release – 64bit Production
With the Automatic Storage Management option

SQL> select name,state from v$asm_diskgroup;

—————————— ———–