Tuesday, March 20, 2018

Accent Insensitive search in Oracle

Here is way to search non english characters in Oracle :  Please see the example demo below -

Here I have created a table "accent" with few accent and non-accent data. then tried to search - 

 CREATE TABLE accent (name VARCHAR2 (200));  
  INSERT INTO accent  
    VALUES ('Peña Báináõ');  
  INSERT INTO accent  
    VALUES ('Pena Bainao');  
  INSERT INTO accent  
    VALUES ('Pena Bainaoooo');  
  INSERT INTO accent  
    VALUES ('Penaffff Bainao');  
      UTL_RAW.cast_to_varchar2 (NLSSORT (name, 'nls_sort=binary_ai')))  
  FROM accent;  
  SELECT *  
          UTL_RAW.cast_to_varchar2 (  
            NLSSORT (name, 'nls_sort=binary_ai')))  
      FROM accent)  
   --example from dual table  
  SELECT 'Peña Báináõ' original,  
     UTL_RAW.cast_to_varchar2 (  
      NLSSORT ('Peña Báináõ', 'nls_sort=binary_ai'))  

Links -

Wednesday, June 21, 2017

Simple Linux shell script for oracle export or expdp backup from cron job

This shell script is for expdp backup of Oracle database, it will take export ( data pump ) backup of "scott" user every night at 1:00AM and it will remove the backup older than 3 days.

1) save the script as below (with oracle user)



export ORACLE_BASE=/userdata/app/ora11gR2; #export ORACLE_BASE
export ORACLE_HOME=$ORACLE_BASE/prduct/; #export ORACLE_HOME
export ORACLE_SID=orcl; #export ORACLE_SID
export EXPORT_FOLDER=/userdata/app/dump;
DATE=$(date +"%m%d%y") ;
$ORACLE_HOME/bin/expdp system/system schemas=scott directory=DIR_DUMP1 dumpfile=$DATE-EXPDP_CRON.DMP logfile=$DATE-EXPDP_CRON_LOG.log ;
find /userdata/app/dump/ -name *EXPDP_CRON_TAR.tar.bz2 -mtime +3 -delete;


Please change ORACLE_BASE,ORACLE_HOME,ORACLE_SID and EXPORT_FOLDER (directory location, where backup will store) values as per your environment and change or add schemas name as yours.

2) Create a cron job log location as below (with oracle user)


3) Adding to cron job-

oracle$ crontab -e

then add below line with vi editor commands

0 1 * * * /app/scripts/expdb_backup.sh >> /app/logs/expdb_backup_logs.log 2>&1

[ Note- for testing purpose, you can schedule it for every 5 minutes like below

*/5 * * * * /app/scripts/expdb_backup.sh >> /app/logs/expdb_backup_logs.log 2>&1


4) Make the script executable

oracle$ chmod +x /app/scripts/expdb_backup.sh

5) check the cron job logs for details

oracle$ cat /app/logs/expdb_backup_logs.log


Tuesday, June 20, 2017

Linux Shell script to check is this very script still running or not, with cron job

I was told to write a shell script (in Redhat Linux 7) to schedule few big processes and also told to check if this script still running no need to run again even through next cron job time start.

Means, checking this script status first then decide to run or not (from this script on the run time). this was little tricky. if it was to check another process's status then it was easy.

So here is my solution-

1) save this as check_run.sh

-------------------------this script will check for  check_run.sh ---------------------
echo "This script name is $(basename $0)" 
if [[ "`/sbin/pidof -x $(basename $0) -o %PPID`" ]]; then
    echo "This script is still running with PID `/sbin/pidof -x $(basename $0) -o %PPID`"
    exit  #exit still running
   echo "This script/process is not running"

   # Write your main processes/commands here to execute
   #sleep 1m  --for testing purpose
    sleep 30 #---30 second
echo "Schdule job Completed"

2) to check from command line ( open two terminal and run as below with 30 second)

root# cd /home/root/scripts
root@scripts# ./check_run.sh     # or root@scripts# sh -c check_run.sh

  (then only it will work. remember, if you call like root# sh check_run.sh then it will not work. you have to call it directly as cron job do, so it will work from cron job. tested)

Description of the script for newbies:-
pidof                   : show the process ID of running instance.
-x                         : Tells pidof to include scripts, these are usually excluded.
$(basename $0) : this is for being replaced by the name of your script when you execute it.
-o                        : Omits a given PID
%PPID                 : PID of this very script. So when this script is run it will not count itself.

3) How to add in cron job?

a) Adding in crontab

root# crontab -e
   (add follwing line with vi editor commands) (esc --> I for insert -->esc --> :wq --> enter)  

*/5 * * * * /home/root/scripts/check_run.sh >> /home/root/scripts/check_run_logs.log 2>&1

Here I have scheduled it for every minutes to run, you can change as yours. see in cron job docs.

b) Making it executable

root# chmod +x /home/root/scripts/check_run.sh

c) You can add the script's path to PATH environmental variable in .bash_profile if you see error in log file (/home/root/scripts/check_run_logs.log) like "No such file or directory" or "command not found".



then Log out root user and login again. now it should work.

d)  Testing-
Check cron job log like below to see running time and script name as below

root# cat /var/log/cron

e) Check your created log like below to see successful status

root# cat /home/root/scripts/check_run_logs.log

===================The End=====Cheers=================

Tuesday, November 15, 2016

Copy a table from one database to another database in Oracle

We can use any of following  method to copy one table from one database to another database.

1) using create table as select with help of DB_LINK between two databases

create table target_table_name as select * from source_table_name@dblink 

2) Using Legacy exp/imp utility

3) Using datapump expdp/impdp

4) Using SQLPlus copy command

5) Using other tools like SQL developer/Toad for Oracle etc.

Here I am giving only example of #4 (using SQLplus copy command). becuase this is looks like interesting to me.

SQL> COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

example - (from any machine in your network)

SQL> copy from hr/hr@orcl to scott/tiger@orcl2 create employees_copy using select * from employees ;

Be carefull- SQLPlus COPY supports only the following datatypes:


Friday, August 26, 2016

How to Generate or Create ERD diagram with Oracle SQL Developer- Version

1)     Make an connection to your schema
2)      Then Go to tab View --> Data Modeler --> Browser  (double click) . it will open a window like below screen

3)      Then Right click on Relational  Models--> New relational models
4)      Then select your required tables from connection menu and drag these to New Relational model page.  There you go. Now you can decorate yourself .