Oracle

Oracle Database

General

• Get Version:

 select * from v$version;
 select * from product_component_version;

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 statistics=none file=schema.dmp
imp system/PASS[@SID] file=sourcedb.dmp fromuser=SOURCEUSER touser=TARGETUSER

If the source db is newer than the target db (or 32/64bit), there are multiple options:

• Use exp from the target db version (the lower version)
• Use copy command in sqlplus or expdp (data pump):

Note: Within XE 11 there should exist a default dump dir in oraclexe/app/oracle/admin/XE/dpdump, so there is no need to create a new one. It will be used by default, so the directory= parameter is not needed.

sqlplus sys/PASS[@SID] as sysdba

> create or replace directory dbdumps as '/oracle/dbdumps';
> grant read, write on directory dbdumps to SOURCEUSER;

expdp system/PASS schemas=SOURCEUSER version=TARGETVERSION directory=dbdumps dumpfile=db.dmp

TARGETVERSION could e.g. be something like 10.2 or 10.2.0.1.0 Repeat the same on the target system with impdp, usually with same parameters (without the version parameter). An oracle directory must also be created (or an existing one must be used). The version parameter can also be set to COMPATIBLE. Note that generally the directory + files must be accessible (on fs level) by the oracle user (check rights).

Copy the file to the dbdump directory of the target host and start the import (again, in case of file access issues check the access rights of the dump file).

impdp system/PASS dumpfile=db.dmp

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
• expdp/impdp: Oracle Data Pump

Change HTTP Port

sqlplus system@xe

-- get currently used http and ftp port (defaut: 8080, 0)
select dbms_xdb.gethttpport as "HTTP-Port"
     , dbms_xdb.getftpport as "FTP-Port" from dual;
-- change port(s), set value = 0 to disable the service
begin
  dbms_xdb.sethttpport('8088');
  --dbms_xdb.setftpport('2100');
end;
/

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