Oracle XE on Debian (Sarge)

original article taken from: here

see also: Installing Oracle Database 10g Release 2 on Linux x86

This document describes guide for installing Oracle 10g Express Edition (formaly known as HTML DB) on Debian based machines.

Oracle Database Express Edition (XE) is an entry-level, small-footprint database based on the Oracle Database 10g Release 2 code base that's free to develop, deploy, and distribute; fast to download; and simple to administer.

Debian based host (32 bit) with minimum 4GB of free space for oracle data + 500Mb for package installation. - Min. 1Gb of ram.

1) As always start up by adding additional lines to sources.list and update package list:

 # echo "deb http://oss.oracle.com/debian/ unstable main non-free" >> /etc/apt/sources.list
 # apt-get update

2) Installation now may begin with installing following 2 packages from freshly updated list:

 # apt-get install oracle-xe-client 
 # apt-get install oracle-xe

3) Installation is now completed. Please notice runtime kernel parametar changes: `cat /etc/sysctl.conf`. Proceed further with configuration - just accept defaults - and enter password for SYS and SYSTEM accounts:

# sh /etc/init.d/oracle-sh configure

That's it. Oracle listener should be up and running on socket 1521: `lsof -n -i TCP:1521`, with web configuration console attached to your loopback device `firefox http://127.0.0.1:8080/apex` - this is a default installation.

• You can always preview/edit your current Oracle-XE settings with: `vim /etc/default/oracle-xe`.
• Since installation of previously installed packages is rather heavily space demanding, delete them from apt cache:

 # apt-get clean 

Your Debian based system should meet the minimum requirements for swap space, otherwise installastion would fail. Based on the amount of physical memory available on the system, Oracle Database 10g Express Edition requires 1006 MB of swap space (YMMV). In order to overcome this issue you can allways extend your partitions with like Knoppix ot Gparted. The alterntative way without reboot can be done by issuing:

 # dd if=/dev/zero of=/myswapfile bs=1M count=1000
 # mkswap /myswapfile
 # swapon /myswapfile

You can always disable Oracle XE startup in boot time with (no update-rc.d method here ):

 # chmod -x /etc/init.d/oracle-xe 

The alernative way to the same is to edit /etc/default/oracle-xe and change:

 #ORACLE_DBENABLED=true
 ORACLE_DBENABLED=false

If you have installed OracleXE database on remote *nix server, you can easily setup a ssh tunnel in order to gain access to a web administration console (since web console is bound to loopback device and therefore unavailable outside to the network):

 $ ssh oracle-xe-server -L 8081:localhost:8080

While keeping this proces running (or keep it on background via `screen` or `nohup`), you may now login to Oracle XE web administrations console on your local computer: http://localhost:8081/apex/. Please notice that your ssh daemon on your local computer should allow TCP forwarding: AllowTcpForwarding yes in your sshd configuration file.

By default OracleXe installation does not allow sql network connections to your XE database. To enable it, logon to web management console and enable “Remote connections”: “Administration→enable “Available from local server and remote clients”→ press “Apply Changes”. The same procedure can also be done from the commandline:

 $ sqlplus -S system/password@//localhost/XE <<!
 EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE); 
 EXIT;
 /
 !

If your SQL client uses a Oracle Instant client installation, the SQL connection can be obtained by entering:

 [foobar@iluzija ~]$ sqlplus username/password@//oraclexe.hostname.or.ip//XE

If somewhat connection is refused (due to firewall restrictions) you can use same ssh trick as done previously:

 $ ssh oracle-xe-server -L 1512:localhost:1512

where your sql connection should be like this:

 [foobar@iluzija ~]$ sqlplus username/password@//localhost//XE

In order to add single sql user with non-dba privilages to OracleXE execute following commands:

 #
 # Contents of: this_script.sql
 #
 CREATE USER myuser IDENTIFIED BY the_password DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp   QUOTA UNLIMITED ON users;
 CREATE ROLE myconnection;
 GRANT CREATE session, CREATE table, CREATE view, CREATE procedure, CREATE synonym TO myconnection;
 GRANT myconnection TO myuser;
 /

You can run this SQL script with:

# su - oracle 
oracle@server:~$ sqlplus / as sys
SQL> @this_script.sql
SQL> exit;

Alternatively use web managment console to add new Oracle XE users accordingly.

For deleting a sql users from OracleXE use web management console or execute following commands from the commandline:

# su - oracle 
oracle@server:~$ sqlplus / as sys
SQL> drop user myuser cascade;
SQL>exit;