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 … Continue reading RLWRAP SQL*PLUS to have BASH like Functionality in SQLPLUS

Hang anaylze oracle 11g

What to do when a production database goes hanging and there is no time left and your boss is standing on your head. This article could be just be your life saver http://www.dbi-services.com/index.php/blog/entry/oracle-is-hanging-dont-forget-hanganalyze-and-systemstate

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 … Continue reading Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

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 … Continue reading Generate Excel | CSV Report from SQL*PLUS

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 … Continue reading Generating HTML Reports from SQL*Plus

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                                     … Continue reading Add string to query Oracle 11g

ORA-29701: unable to connect to Cluster Synchronization Service

Problem ------ 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 Disconnected Solution -------- 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 … Continue reading ORA-29701: unable to connect to Cluster Synchronization Service