Oracle

Oracle Database

General

 /* GET VERSION */
 select * from v$version;
 select * from product_component_version;

 /* DISABLE PASSWORD EXP. (esp. on XE) */
 alter profile default limit password_life_time unlimited;

 /* REACTIVATE LOCKED USERS */
 --select * from dba_users where account_status like '%EXPIRED%';
 alter user USERNAME identified by PASSWORD; -- password without single quotes
 alter user USERNAME account unlock;

Check Listeren status:

lsnrctl status

Start / stop listener:

lsnrctl start
lsnrctl stop

SQL Plus

Connect to remote DB

sqlplus '[username]/[password]@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
  (HOST=[HOSTNAME])(PORT=1521))(CONNECT_DATA=(SID=[SID])))'

SERVICE_NAME is another valid option instead of SID.

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[@HOST:PORT/SID] 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 (object name: DATA_PUMP_DIR), 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 DATA_PUMP_DIR as '/oracle/DATA_PUMP_DIR';
> grant read, write on directory DATA_PUMP_DIR to SOURCEUSER;
> select * from dba_directories; -- check

expdp system/PASS schemas=SOURCEUSER version=TARGETVERSION directory=DATA_PUMP_DIR dumpfile=db.dmp exclude=grant,index,statistics

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 object must also be created (or an existing one must be used). The version parameter can 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

If the table spaces differ (old/new) you have use the remap options.

impdp system/PASS dumpfile=db.dmp remap_schema=source_schema:dest_schema \
   remap_tablespace=source_tablespace:dest_tablespace

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;
/

System

Listerer Configuration

listener.ora

# listener.ora Network Configuration File: /oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
     (PROGRAM = extproc)
   )
 (SID_DESC=
       (GLOBAL_DBNAME = mydb)
       (ORACLE_HOME = /oracle/app/product/11.2.0/dbhome_1)
       (SID_NAME = mydb)
  )
 )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydoma.in)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /oracle/app

tnsnames.ora

# tnsnames.ora Network Configuration File: /oracle/app/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

BISDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.mydoma.in)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb)
    )
  )

Service & Listener

-- register db to listener
ALTER SYSTEM REGISTER;

LSNRCTL> start / stop / reload / services

oradim -SHUTDOWN -SID XE -SHUTTYPE SRVC,INST
oradim -STARTUP -SID XE -STARTTYPE SRVC,INST

-- Try to restart e.g. on ORA-01109:Database not open
Shutdown
Startup Mount
alter database archivelog;
alter database open;

Locked objects, kill session

Check current sessions and kill one.

SELECT sid, serial#, status, osuser, machine FROM v$session WHERE username = '[USER]' AND machine = '[HOST]';

SELECT 'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''' IMMEDIATE;' FROM v$session WHERE username ='[USER]';

select c.owner, c.object_name, c.object_type, b.sid, b.serial#,  b.status, b.osuser, b.machine
  from v$locked_object a, v$session b, dba_objects c
 where b.sid = a.session_id
   and a.object_id = c.object_id;

ALTER SYSTEM KILL SESSION '449,44397' IMMEDIATE;

set serveroutput on format wraped;
declare
  sid_ number;
  serial_ number;
begin
  select sid, serial# into sid_, serial_
  from v$session where sid = (select sid from v$mystat where rownum = 1);
  dbms_output.put_line('Session id is ' || sid_);
  dbms_output.put_line('Serial# is ' || serial_);
end;
/

Get charset

select value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

Client Commands

Session settings

-- don't treat &foo strings as parameter (which opens input on execution)
SET DEFINE OFF;

-- special chars are escaped in my strings, eg neede when DEFINE is ON and
-- we must escape \&
SET ESCAPE ON;

-- print query times for each query
SET TIMING ON;

Print

Simple print example:

create or replace procedure print (msg varchar2)
as begin
  dbms_output.put_line(msg);
end;

-- usage
--SET SERVEROUTPUT ON
--DBMS_OUTPUT.ENABLE (buffer_size => NULL);
set serveroutput on format wraped;
exec print('foo');

-- alternative (put_line flush problem in pl/lsql / procedures)
--drop table print_log;
create table print_log (msg varchar2(255), time timestamp);

create or replace procedure print (msg varchar2)
as begin
  dbms_output.put_line(msg);
  insert into print_log values (msg, systimestamp);
  commit;
end;

select * from print_log order by time;

Quick tune

DECLARE
  task_name VARCHAR2(255) := 'MY_QUICKTUNE_TASK';
  sql_stmt VARCHAR2(4000) := 'SELECT foo
      FROM bar WHERE x = ''bla''';
BEGIN
  DBMS_ADVISOR.QUICK_TUNE(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_name, sql_stmt);
END;

SELECT COUNT(*) FROM user_advisor_journal;
SELECT task_name, journal_entry_type, journal_entry
  FROM user_advisor_journal WHERE task_name = 'MY_QUICKTUNE_TASK';
SELECT command, attr1, attr3, attr4
  FROM user_advisor_actions WHERE task_name = 'MY_QUICKTUNE_TASK';
SELECT attr5
  FROM user_advisor_actions WHERE task_name = 'MY_QUICKTUNE_TASK';
SELECT execution_start, execution_end, status, error_message
  FROM user_advisor_log WHERE task_name = 'MY_QUICKTUNE_TASK';
SELECT description, advisor_name, created, status, recommendation_count, source, how_created
  FROM user_advisor_tasks WHERE task_name = 'MY_QUICKTUNE_TASK';
SELECT DBMS_ADVISOR.get_task_script('MY_QUICKTUNE_TASK') AS script FROM dual;

exec dbms_advisor.delete_task('MY_QUICKTUNE_TASK');

Snippets

Select blob as text:

select b.*, utl_raw.cast_to_varchar2(dbms_lob.substr(b.cbindata)) from tbinary b;

Generate a v4 UUID:

select lower(regexp_replace(rawtohex(sys_guid()), '([A-F0-9]{8})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{4})([A-F0-9]{12})', '\1-\2-\3-\4-\5')) uuid from dual;

HowTos

System

Oracle/Create tablespace (and user/schema)
Oracle/Drop all user tables
Oracle/Recover Objects (from trash)

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)
Oracle/Constraints
Oracle/Diff

Client Commands

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

Java

Oracle/Java

Troubleshooting

Unable to connect locally

You get: ORA-12505, TNS:listener does not currently know ...
But the DB is running and you can connect with sqlplus:
- sqlplus sys/admin as sysdba
- select database_status from v$instance; -> ACTIVE

Then try setting the local listener like this:

alter system set LOCAL_LISTENER='(DESCRIPTION_LIST =  (DESCRIPTION = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))))';
alter system register;

Source: [3]

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

Oracle

Contents

General

SQL Plus

Connect to remote DB

XE

Ubuntu 64bit Installation

Backup

Clone User/Schema

References:

Change HTTP Port

System

Listerer Configuration

listener.ora

tnsnames.ora

Service & Listener

Locked objects, kill session

Get charset

Client Commands

Session settings

Print

Quick tune

Snippets

HowTos

System

SQL

Client Commands

Java

Troubleshooting

Unable to connect locally

References

Specific

External Links

Navigation menu

Oracle

General

SQL Plus

Connect to remote DB

XE

Ubuntu 64bit Installation

Backup

Clone User/Schema

References:

Change HTTP Port

System

Listerer Configuration

listener.ora

tnsnames.ora

Service & Listener

Locked objects, kill session

Get charset

Client Commands

Session settings

Print

Quick tune

Snippets

HowTos

System

SQL

Client Commands

Java

Troubleshooting

Unable to connect locally

References

Specific

External Links

Navigation menu

Search