Install Notepad++ in Ubuntu 12.04

June 11, 2012February 28, 2013 ~ sqlandplsql ~ 22 Comments

Installing Notepad++ in Ubuntu 12.04

Notepad++ is an excellent open source editor. But it is not officially supported for Ubuntu. But we can install it in via Wine. ( Wine is a windows package for Ubuntu)

Steps

1. If you doesn’t have Wine installed, install it from Dashboard or terminal using sudo apt-get install wine

2. Go to Notepad++ website and download windows installer ( Direct Link here )

Click on v6.3 installer

Choose Open with Wine Windows Program

Choose language.

Install. create short cut on Desktop

( Note:- I have modified the GUI property using Setting –> Style Configurator –> Default Style )

Alternatively I have a youtube video too

Notepad++ is arguably best free editor in Windows and now you can use it in Ubuntu too.

Install oracle 11g on windows 7

May 24, 2012October 1, 2012 ~ sqlandplsql ~ 5 Comments

This article clearly describes installation of Oracle 11g release 2 on Windows 7

Pre-requisites
1. Operating system – Windows 7 64 bit operating system
2. Hard disk space – At least 2 GB free space
3. System RAM – At least 512 MB

4. Administrator privilege to install

1) Download Oracle 11g release 2

1. Go to Oracle website
2. Log in. You need to have an oracle account to download
3. Make sure you are downloading Oracle 11g release 2 for Windows 64 bit operating system.
4. Create a Oracle11g folder in desktop.

5.Extract both zip files into same folder. ( This will help us while installation)

2) Installation

Steps

1. Goto Oracle11g folder in desktop.
2. Open database folder

3. Right click setup.exe and select Run as Administrator

4. This is will launch Oracle Universal Installer. In landing page it will prompt you to put your email address for any future security update or support. Click Next.

Enter the email if you wish to receive support

5. Next step is to choose the database options. There are 3 options. It is best to choose ‘Create and Configure database” if you do not sure how to install database manually. Click Next.

6. Next we choose ‘Desktop Class’. This is for normal desktops and laptops. If you wish to install server you could choose ‘Server Class’ which is server-based systems. Click Next.

Choose Desktop Class for desktops and laptops

7. Next step is to provide configuration details

Oracle Base – Base folder. ORACLE_BASE

Software Location – This is called ORACLE_HOME

Global Database Name – This is the database identifier. You need to specify while logging into database. ORACLE_SID

Password – This is administrative password. You must supply Oracle recommended standards for passwords.

Click Next.

8. Next is pre-requisite checks. Wait till it finishes. Sometime it might take a while.

9. When finished it will display the window with summary of installation. Click Finish

10. Next is installation. This might take few minutes.

11. In between it will ask to click ‘Allow Access’ since windows firewall blocked it. Just click ‘Allow Access’

12. It will create and configure the database if you choose ‘Create Database and Configure’ in step 5.

13. Password Management. Unlock the account you want and supply password. Click OK

14. Database installation successfully completed. Click Close.

Congratulations ! You have successfully installed Oracle 11g on Windows 7 .

Also Read – Install Oracle 11g on Ubuntu Linux

Please post your comments below.

Nth highest salary

April 19, 2012February 27, 2017 ~ sqlandplsql ~ 10 Comments

There many ways to find nth highest salary.

Consider below EMP table structure

EMPNNO	EMPNAME	SALARY	DEPTNO
10	Bill	12000	5
11	Solomon	10000	5
12	Susan	10000	5
13	Wendy	9000	1
14	Benjamin	7500	1
15	Tom	7600	1
16	Henry	8500	2
17	Robert	9500	2
18	Paul	7700	2
19	Dora	8500	3
20	Samuel	6900	3
21	Mary	7500	3
22	Daniel	6500	4
23	Ricardo	7800	4
24	Mark	7200	4

List of all employees,salary,rank in descending order

SALARY	EMPNO	RANK
12000	10	1
10000	11	2
10000	12	2
9000	13	3
7500	14	4
7600	15	5
8500	16	5
9500	17	6
7700	18	7
8500	19	8
6900	20	9
7500	21	9
6500	22	10
7800	23	11
7200	24	12

1) Find highest salary/salaries using DENSE_RANK() function

select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk = 1;

EMPNO SALARY RK
———- ———- ———-
10 12000 1

2) Find 2nd highest salary/salaries using DENSE_RANK() function

select * from (select empno,salary,dense_rank() over(order by salary desc) as rk from emp) where rk = 2;

EMPNO SALARY RK
———-           ———- ———-
11 10000             2
12 10000               2

If you replace 2 with N in above query it will provide Nth highest salary/salaries.

3. Find 2nd highest salary using ROW_NUMBER() function

select * from (select empno, salary, row_number() over(order by salary desc) rk from emp order by salary desc ) where rk = 2;

EMPNO SALARY RK
———- ———- ———-
11 10000 2

Note that this query will not give multiple records if there are employees with same salaries ( compare query (2) and (3) )

4. Find 2nd highest salary using ROWNUM

select * from (select empno, salary,rownum rk from emp order by salary desc) where rk = 2;

EMPNO SALARY          RK
———-           ———- ———-
11 10000             2

Note that this query also will not give multiple records if there are employees with same salaries ( compare query (2) and (4) )

Conclusion :- Use DENSE_RANK, ROW_NUMBER(), ROWNUM with respect to the requirement. As per my experience query with ROW_NUMBER() is most appropriate and good for performance.

You may interested in below posts also
RANK() function
DENSE_RANK() function
Autonomous Transaction
Cursors
Soundex function
Oracle Interview Questions

Installing Oracle 11g on Ubuntu

December 2, 2011February 17, 2013 ~ sqlandplsql ~ 13 Comments

This article clearly describes installation of Oracle 11g release 2 on Ubuntu 10.10 desktop

Pre-requisites
1. Operating system – Ubuntu 10.10 32 bit operating system (Maverick Meerkat)
2. Hard disk space – 2 GB free space
3. System RAM – At least 512 MB

1) How to install Ubuntu 10.10
Go to Ubuntu Home page Ubuntu and download Ubuntu 10.10 desktop and install.

2) Download Oracle 11g release 2
1. Go to Oracle website
2. Log in. You need to have an oracle account to download
3. Make sure you are downloading Oracle 11g release 2 for 32 bit operating system.
(Direct link – Oracle 11g R2 linux 32 bit opertaing system )
4. Extract both zip files into one folder
E:- Create a Oracle11g folder in desktop and copy both folders into it.

Now we have Ubuntu operating system and Oracle installations files ready.

3) X Server configuration
For Oracle installation in Linux machines need X server configuration.

Steps
1. Open a terminal
2. Connect as root ( You need to have root account )
root@ubuntu$ su – root
3. run ifconfig
root@ubuntu$ ifconfig
Find the ip address. (find for inet addr looks like xxx.xxx.x.x format )
Say, ip address is 192.120.1.1
4. run below command
root@ubuntu$ xhost + 192.120.1.1
5. To verify X Server configuration you can use xclock command
root@ubuntu$ xclock
If a clock pop ups server configuration is fine.

4) Updating packages
If you ave the fresh install of Ubuntu you can skip this step.
Otherwise run below commands

root@ubuntu$ apt-get update
root@ubuntu$ apt-get dist-upgradeRestart
root@ubuntu$ reboot

Install missing libraries
root@ubuntu$ apt-get install build-essential libaio1 gawk ksh libmotif3 alien libtool lsb-rpm

5) Updating shell and system configurations
Run below commands in sequential order and enter password for your oracle user

root@ubuntu$ cd /bin

root@ubuntu$ ls -l /bin/sh
root@ubuntu$ ln -sf bash /bin/sh
root@ubuntu$ ls -l /bin/sh
root@ubuntu$ lrwxrwxrwx 1 root root 4 2008-05-01 22:51 /bin/sh -> bash
root@ubuntu$ cd /root
root@ubuntu$ addgroup oinstall
root@ubuntu$ addgroup dba
root@ubuntu$ addgroup nobody
root@ubuntu$ usermod -g nobody nobody
root@ubuntu$ useradd -g oinstall -G dba -p password -d /home/oracle -s /bin/bash oracle
root@ubuntu$ passwd oracle
root@ubuntu$ mkdir /home/oracle
root@ubuntu$ chown -R oracle:dba /home/oracle

root@ubuntu$ ln -s /usr/bin/awk /bin/awk
root@ubuntu$ ln -s /usr/bin/rpm /bin/rpm
root@ubuntu$ ln -s /usr/bin/basename /bin/basename
root@ubuntu$ mkdir /etc/rc.d
root@ubuntu$ for i in 0 1 2 3 4 5 6 S ; do ln -s /etc/rc$i.d /etc/rc.d/rc$i.d ; done

root@ubuntu$ mkdir -p /u01/app/oracle
root@ubuntu$ chown -R oracle:dba /u01

Change the run level if it is 2 or 3 (It should be 5 for oracle install)
root@ubuntu$ telinit 5

Now we need to update few configuration files

1. /etc/sysctl.conf
Run below command and add the following entries at the end of the file.
root@ubuntu$ gedit /etc/sysctl.conf

fs.file-max = 65535
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144

2. /etc/security/limits.conf
Run below command and add the following entries at the end of the file.
root@ubuntu$ gedit /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16383
oracle soft nofile 1023
oracle hard nofile 65535

3. /etc/pam.d/login

Run below command and add the following entries at the end of the file.
root@ubuntu$ gedit /etc/pam.d/login

session required /lib/security/pam_limits.so
session required pam_limits.so
Run below command or re-start
root@ubuntu$ sysctl -p

6) Oracle Installation
Now we are almost ready for Oracle Installation.
Log in as oracle user.

user@ubuntu$ su – oracle
Password:

For oracle user also need x server setup. Try to run xclock command. If it pop ups we are fine. Otherwise go to step 3)

oracle@ubuntu$ xclock

Go to the folder we have extracted the Oracle 11g.
oracle@ubuntu$ cd /home/user/Desktop/Oracle11g
oracle@ubuntu$ cd database1
oracle@ubuntu$ ./runInstaller

Wait few seconds. It will launch Oracle 11g installation wizard.

Starting Oracle Universal Installer…

Checking Temp space: must be greater than 80 MB. Actual 80115 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3917 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall

Click Next. Chose custom installation. Enter database configuration parameters while prompting. In pre-requisite check it will show there are some library files are missing. Just ignore those and continue installation.

6) Setting environment variables

First make sure
1) /u01/app/oracle/product/11.2.0/dbhome_1 is exists
2) /u01/app/oracle/product/11.2.0/dbhome_1/bin exists
3) orcl is the tnsname specified at installation.

Add the below entries in /etc/profile as root user

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export PATH=$PATH:/u01/app/oracle/product/11.2.0/dbhome_1/bin
export ORACLE_SID=orcl

7) Running Oracle 11g first time
Log in as oracle

oracle@ubuntu$ echo $ORACLE_HOME
/u01/app/oracle/product/11.2.0/dbhome_1

oracle@ubuntu$ echo $ORACLE_SID
orcl

oracle@ubuntu$ sqlplus /nolog
It connect to SQL as idle instance. Execute below commands. Enter password created for sys at installation. Start database using startup command

SQL >

SQL> connect sys as sysdba
Enter password:
Connected.
SQL> startup

SQL> select count(*) from all_objects;

COUNT(*)
————–
70778

Congratulations ! You have successfully installed Oracle 11g on Ubuntu.

Please post your comments below, share this link and click like button.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: