Archive

Archive for the ‘oracle’ Category

Oracle DB – Quick Reference

December 12, 2014 Leave a comment

A quick reference for us Novices

View current database select * from global_name;
Create a new user create user “username” identified by “password”;
eg create user felix identified by felix
Grant connect and resource privileges grant connect, resource to felix;
Allow user to create new tables grant dba to felix;
Change the default number of lines per page(14) set pagesize 60;
Change the default number of characters per line (80) set linesize 100;
Clear formatting clear column;
Run previous command /

Now I can connect to database as

vagrant-ubuntu-oracle-xe$ rlwrap sqlplus64 felix/felix@//localhost:1521/XE

Documentation:
http://docs.oracle.com/cd/B19306_01/server.102/b14357/toc.htm

Source:
http://wiki.titan2x.com/index.php?title=Oracle_SQL_cheat_sheet
http://www.vttoth.com/CMS/index.php/technical-notes/81
https://community.oracle.com/thread/1058514?tstart=0

Categories: oracle

vagrant-ubuntu-oracle-xe – ERROR ORA-12162; ORA-12547;

December 11, 2014 Leave a comment

Problem:
When I tried to connect to vagrant-ubuntu-oracle-xe box I got the following errors

vagrant-ubuntu-oracle-xe$ vagrant up

vagrant-ubuntu-oracle-xe$ sqlplus64 system/manager@//localhost:1521/XE

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 11 21:50:08 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:
ORA-12547: TNS:lost contact
Enter user-name: 
Enter password: 
ERROR:
ORA-12162: TNS:net service name is incorrectly specified

Solution:

vagrant-ubuntu-oracle-xe$ vagrant halt
==> default: Attempting graceful shutdown of VM...

vagrant-ubuntu-oracle-xe$ vagrant up

vagrant-ubuntu-oracle-xe$ vagrant ssh

Welcome to your Vagrant-built virtual machine.
Last login: Fri Sep 14 02:23:18 2012 from 10.0.2.2
vagrant@oracle:~$ cat /u01/app/oracle/product/11.2.0/xe/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

Added the following to the tnsnames.ora file above (So now I have two entries)
XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )
vagrant@oracle:~$ sqlplus system/manager@//oracle:1521/XE

SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 11 14:11:37 2014
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> quit

vagrant@oracle:~$ exit
logout
Connection to 127.0.0.1 closed.

vagrant-ubuntu-oracle-xe$ sqlplus64 system/manager@//localhost:1521/XE

SQL*Plus: Release 11.2.0.4.0 Production on Thu Dec 11 22:11:00 2014
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
SQL> quit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

Source:
https://github.com/hilverd/vagrant-ubuntu-oracle-xe/issues/19

Installing cx_Oracle

October 25, 2013 3 comments

Problem:
I needed to use python with oracle.
Before the installation, this is what I get.

>>> import cx_Oracle
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
ImportError: No module named cx_Oracle

Solution:
Follow the steps below.
Download version 11 of Oracle instantclient (as the cx_Oracle 5.1.2 in this article will not work with an oracle version past 11g. Both x86 and x64 will work.)

Installination Steps.
1. Download oracle instant client from oracle.com http://www.oracle.com/technetwork/topics/linuxsoft-082809.html (You need to register on the site first.)
Get the following files

instantclient-basic-linux-11.2.0.4.0.zip
instantclient-sdk-linux-11.2.0.4.0.zip
instantclient-sqlplus-linux-11.2.0.4.0.zip

Extract them all into the same folder.

2. Make sure you have the following packages installed.
(I use synaptic to install these as I am on debian 6)

build-essential
python-dev
libaio-dev

3. Set ORACLE_HOME variable in your .profile file.

$ cd ~
$ vim .profile

# Add the following to the end of the file. Change the path to point to the directory where you unzipped the files you downloaded in step 1 above.

export ORACLE_HOME="/usr/local/oracle/instantclient_11_2"
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME

4. Create a soft link to libclntsh.so. NB. Do this for a different terminal so that you can read the new changes in .profile. (Or source .profile YMMV.)

$ cd ~
$ cd $ORACLE_HOME
$ ln -s libclntsh.so.11.1 libclntsh.so

5. As root, update ldconfig

$ su 
root> cd /etc/ld.so.conf.d

Add the following line to oracle.conf. Create the file if it does not exist.

root> vim oracle.conf
/usr/local/oracle/instantclient_11_2

Once done run the following command.

root> ldconfig

6. Download cx_Oracle from sourceforge
http://sourceforge.net/projects/cx-oracle/files/
Please note that version 5.1.2 does not as yet support the latest version of instantclient. So make sure that you download version 11 not 12. You can tell which versions are supported by reading setup.py and looking for filesToCheck. Change directory to where you extracted the files to.
As root.

root> cd cx_Oracle-5.1.2/
root> python setup.py build
root> python setup.py install

Update (2014.Dec.06)
If you get the following error when you try “python setup.py build”
Traceback (most recent call last):
File "setup.py", line 132, in
raise DistutilsSetupError("cannot locate an Oracle software " \
distutils.errors.DistutilsSetupError: cannot locate an Oracle software installation

It is because it is not able to find $ORACLE_HOME
Switch to another terminal

$source .profile
$cd cx_Oracle-5.1.2/

Then as root try to build and install again.

7. Check your install.

$ python
Python 2.6.6 (r266:84292, Dec 27 2010, 00:02:40) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> print cx_Oracle.version
5.1.2
>>> 


Update (2014.Nov.25)

Followed the same instructions to install on Linux x64.

$ python
Python 2.7.8 (default, Sep  9 2014, 22:08:43) 
[GCC 4.9.1] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import cx_Oracle
>>> print cx_Oracle.version
5.1.2
>>>  
Categories: oracle, python Tags: ,