Oracle

Oracle Database

XE

Ubuntu 64bit Installation

axel -a http://oss.oracle.com/debian/dists/unstable/main/binary-i386/libaio_0.3.104-1_i386.deb
axel -a http://oss.oracle.com/debian/dists/unstable/non-free/binary-i386/oracle-xe-universal_10.2.0.1-1.1_i386.deb
sudo dpkg -i --force-architecture libaio_0.3.104-1_i386.deb
sudo dpkg -i --force-architecture oracle-xe-universal_10.2.0.1-1.1_i386.deb
sudo /etc/init.d/oracle-xe configure

Add the following to your shell's rc (e.g. ~/.bashrc):

ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server
PATH=\$PATH:\$ORACLE_HOME/bin
export ORACLE_HOME
export ORACLE_SID=XE
export PATH

The host and port configuration can be found in the following files (if you want to bind the service to a different address/hostname):

• /etc/default/oracle-xe
• $ORACLE_HOME/network/admin/listener.ora
• $ORACLE_HOME/network/admin/tnsnames.ora

DB files can be found in /usr/lib/oracle/xe/oradata/XE/.

Backup

Activate Archive Log:

% sqlplus sys/PASSWORD as sysdba

SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> -- CHECK IF DB IN ARCHIVLOG MODE
SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

To backup the database run:

% sudo -u oracle $ORACLE_HOME/config/scripts/backup.sh

Note: Logfiles will be placed into the user's home directory.

If you have problems with the Flash Recovery files check if you are using the right user (user must be in 'dba' group or user 'oracle'). Some helpful commands when searching for problems:

% ls -l /usr/lib/oracle/xe/app/oracle/ | grep flash_recovery_area

SQL> SHOW PARAMETER DB_RECOVERY_FILE_DEST;

Run backup.sh regularly (e.g. via cron) and backup the following directories:

• /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/dbs
• /usr/lib/oracle/xe/app/oracle/flash_recovery_area
• /usr/lib/oracle/xe/oradata/XE/

References: [1] [2]

Clone User/Schema

Cloning all tables from from one tablespace to another (see [Oracle/Create tablespace] on how to create a new tablespace).

#exp SOURCEUSER/PASS full=y file=schema.dmp
exp 'sys/PASS AS SYSDBA' owner=USER file=schema.dmp
imp system/PASS[@SID] file=sourcedb.dmp fromuser=SOURCEUSER touser=TARGETUSER

References:

• Exp/Imp: Import Export FAQ
• Clone whole DB: Clone an Oracle database using RMAN duplicate (same server), Clone of a database on the same Host, Duplicate Database Using RMAN 9i

HowTos

System

• Oracle/Create tablespace (and user/schema)
• Oracle/Drop all user tables

SQL

• Oracle/Drop if exists
• Oracle/Find duplicates
• Oracle/Merge
• Oracle/NLS
• Oracle/Select into table
• Oracle/Sequence
• Oracle/Update join
• Oracle/Shuffle data (e.g. for simple masking of productive data in an development environment)

Client Commands

SET DEFINE [ON|OFF|X] -- were x = symbol, e.g. &

Java

• Oracle/Java

References

Specific

• Locking mechanisms

External Links

• ORACLE-BASE
• SS64 Oracle
• TECH on the Net Oracle/PLSQL Topics
• java2s Oracle PL/SQL Tutorial
• ask Tom Oracle