Oracle Tips

anim-bulbglow.gif (80393 bytes)

Acacia is happy to provide programming tips. Help others by adding your own tips to this page.

The tips shown here are for information only and should be tested thoroughly before use in any production environment.

Last updated 18:20:01-07/04/2008

Click here to add your own tips.

Click to search the Acacia site
Click to search using Google

Oracle Tips

Check out these Oracle DBA tips.

Tip Details
Ancestor searching SQL
Rob L		 downwardsSearch.sql

select d.name
from documents d
where d.id_object in ('A998','A1000')
and id_parent in (
select f.id_object
from folders f
start with f.id_object = 'fA159787'
connect by f.id_parent = prior f.id_object);


upwardsSearch.sql

select d.name
from documents d
where d.id_object in ('A998','A1000')
and 'fA159787' in (
select f.id_object
from folders f
start with f.id_object = d.id_parent
connect by f.id_object = prior f.id_parent);
C++ Oracle API (OCI)
Geoff		 Please refer to http://technet.oracle.com/doc/server.804/a58234/toc.htm for the Oracle OCI manual.
Check Listener
Acacia		 To check if the listener is working -

login as the Oracle user
lsnrctl
stat
Create Database
Geoff		 CREATE TABLESPACE "MY_TABLES"
LOGGING DATAFILE 'C:\ORACLE\ORADATA\ORA\MY_TABLESPACE.ora' SIZE 250M EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

drop user my_user cascade;

create user my_user
identified by my_password
default tablespace MY_TABLESPACE
temporary tablespace temp
quota unlimited on MY_TABLESPACE;

grant connect, resource to my_user;

imp system/mypassword@mydatabase
full=no
fromuser=olduser
touser=my_user
file=my_db_dump.dmp
log=my_db_dump.log
Database connections
Acacia		 export ORACLE_SID=dev
Default date format
Acacia		 alter session set NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS'
Determine SGA
Mark		 SGA = (DB_BLOCK_BUFFERS × DB_BLOCK_SIZE)+ SORT_AREA_SIZE + SHARED_POOL_SIZE
+ LOG_BUFFER + LARGE_POOL_SIZE + JAVA_POOL_SIZE

Each Oracle connection shares this total memory size.
You need to ensure that the /etc/system shared memory segment is at least this size.
Display Oracle error message description on Unix.
Acacia		 oerr ora 12154
dotarull
 [*map/map_all_coml11.txt
dota
 [*map/map_all_ag2.txt
dota
 [*map/map_all_ag2.txt
dota
 [*map/map_all_coml10.txt
dota
 [*map/map_all_coml11.txt
dota
 [*map/map_all_coml11.txt
dota
[*map/map_cnc2_11_mordy.txt
dota
[*map/map_cnc2_12_mordy.txt
Existance Checks and Outer Joins
Des		 I sometimes want to do existence checks where I know *some* information about the lookup table.  The outer-join SQL for this is not immediately obvious (or wasn’t to me).

My fix to the personnel-file catalogues is a typical case in point - I want to check which values in the catalogue table are NOT defined in valid_values.  However, valid_values contains entries for lots of fields, so I want to limit my lookup to those rows with id_validation = a known value, so that I don’t get erroneous ‘hits’ on an entry for another field.

This can be done with a correlated sub-select:

SELECT DISTINCT ca10_col2 /* xxx File Category */
FROM ca10_1 C /* the xxx Personnel File Subtype Catalogue table */
WHERE NOT EXISTS
(SELECT 1 FROM valid_values V
WHERE V.id_validation = ‘dvlsA1007’ AND V.text_value(+) = C.ca10_col2);

But sometime a join is faster.  The usual outer-join construction for an existence check includes a (+) on the lookup table, and a test that the column is null:
SELECT DISTINCT ca10_col2
FROM ca10_1 C, valid_values V
WHERE C.ca10_col2 = V.text_value(+)

In this case we additionally want to restrict the rows consulted in the lookup table to those with a particular value for id_validation:
SELECT DISTINCT ca10_col2
FROM ca10_1 C, valid_values V
WHERE V.id_validation(+) = ‘dvlsA1007’
AND V.text_value(+) = C.ca10_col2;

The important thing is the (+) in the id_validation clause.  Without this, the join will revert to an inner join despite the (+) on the text_value clause.  The SQL will still work, but you’ll get the wrong answer.  The basic rule is that if you have a (+) on *any* join criterion, making that table the outer table, then you should code a (+) on *all* references to that table.  This is obvious when joining on multiple columns, but less so when specifying a constant as above, where the temptation is to use the same clause as works in the subselect.
Expand tablespace
Geoff		 alter tablespace OBJECTI
add datafile '/u00/eprdobj/objecti_02.dbf'
size 500 M;
Explain Plan
Geoff		 -- Explain the execution plan
explain plan
set statement_id = 'freddo' for
select id from big_file
where id_log > 555

select * from plan_table
where statement_id = 'freddo';
Export a database
Geoff		 To avoid version compatibility issues:
Export from 8i
Import into 9i

exp system/mypassword@srcdbname owner=myapp full=no file=srcdbname.dmp log=srcdbname.log
Extract stored procedure text
Geoff		 -- SQL to extract the text of a trigger
spool /tmp/&&ProcedureName

-- Ensure we can see all the lines of the long trigger text
set LONG 10000

-- Set headings off
set pagesize 0
set linesize 80
set wrap on

-- Display a trigger
select text
from dba_source
where name = '&&ProcedureName'
and type = 'PROCEDURE'
and owner ='FRED';

spool off
Extract trigger text
Geoff		 -- SQL to extract the text of a trigger
spool /tmp/&&TriggerName

-- Ensure we can see all the lines of the long trigger text
set LONG 10000

-- Set headings off
set pagesize 0
set linesize 80
set wrap on

-- Display a trigger
select trigger_body
from all_triggers
where trigger_name = '&&TriggerName';

spool off
Find data file names
geoff		 select name from v$datafile;
Get next sequence number
Acacia		 select aliases_seq.NEXT_VAL from dual;
High connection rate
Geoff		 If your Oracle database is receiving more that 10 connections per second, try using multiple listeners to spread the work load.

In cases where a table is accessed very often, have a look at setting up a "keep pool" in Oracle.
How to show Oracle Settings
Geoff		 show parameters
Increase tablespace by adding data files
Geoff		 alter tablespace OBJECTI add datafile '/u00/eprdobj/objecti_02.dbf'
size 500 M;
Index selection tuning
Geoff		 http://www.oracleadvice.com/Tips/optind.htm

This explains the optimizer_index_cost_adj parameter.
Move table to a new tablespace
Acacia		 create table NEWTABLE tablespace TS no logging as select * from OLDTABLE;
drop OLDTABLE;
rename NEWTABLE to OLDTABLE;
Optimisation
Geoff		 To ensure indexes are updated, run dbms_states.gather_schema_stats on production databases nightly for tables and indexes that have greater than 10% changes.
Oracle explain plan
Geoff		 ./product/8.x.x/admin/utlxplan.sql

create table PLAN_TABLE (
statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns number,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30));
Oracle performance boost
Bob Pitt		 You may be able to boost Oracle performance using the following:

. Putting cursor_sharing = force and _sqlexec_progression_cost = 0 in the
init.ora halves the load on the CPU(s) because ORACLE is then using bind
variables instead of static variables, and thus is reusing the SQL rather
than reparsing it.
. Putting cursor_sharing = force and _sqlexec_progression_cost = 0 in the
init.ora AND timed_statistics = true causes ORACLE to trip up and eject
the application from the database (ORACLE bug 1538450).
. Putting cursor_sharing = exact and timed_statistics = true seems to work
fine although at lesser of a performance improvement. Still very worthwhile
improvement though. I'll confirm after we have run a week without falling
over.
Oracle Problems
Acacia		 To view Oracle errors, look at the file
$<DB>_HOME/bdump/alert_<DB>.log
Oracle Session Tracing
Geoff		 The following will allow you to trace a single unix Oracle client process, given the PID.

select SID, Serial#
from sys.v$session
where process = [PID]
exec sys.dbms_system.set_sqltrace_in_session([SID],[Serial#],true)

tkprof xxxx.trc xxxxx.rpt
view xxxxx.rpt
Oracle SQL Stats
Acacia		 Use the following to look at a summary of Oracle SQL activity.

select executions, disk_reads, sql_text
from v$sqlarea
order by disk_reads
Oracle Statistics
Geoff		 EXECUTE DBMS_STATS.GATHER_TABLE_STATS (OWNNAME=>'COMSQ',TABNAME=>'COMS_TCN_REFERENCE',CASCADE=>TRUE,METHOD_OPT=>'FOR ALL INDEXED COLUMNS');
Oracle tablespace free report
Geiff		 SELECT a.tablespace_name, a.bytes bytes_used, b.bytes bytes_free, b.largest,
ROUND (((a.bytes-b.bytes)/a.bytes)*100,0) percent_used FROM (
SELECT tablespace_name,
SUM(bytes) bytes
FROM sys.dba_data_files
GROUP BY tablespace_name
) a, (
SELECT tablespace_name,
SUM(bytes) bytes,
MAX(bytes) largest
FROM sys.dba_free_space
GROUP BY tablespace_name
) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY percent_used DESC;
Server Manager SQLs
Acacia		 To run problem SQLs (with reserved chars etc)

svrmgr
connect user/password@sid
spool /tmp/sql.log
desc users
select * from users;\n@textfile.sql
/
spool off
quit
Set default date format for session
geoff		 alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';
Show SQL of Solaris Oracle Process
Geoff		 clear breaks
clear columns
set verify off
set pagesize 40
define PID=&1;
select a.sql_text
from v$sqltext a, v$session b, v$process c
where a.address = b.sql_address
and b.paddr = c.addr
and a.hash_value = b.sql_hash_value
and c.spid = &PID
order by c.spid,a.hash_value,a.piece;
SQL Coding Tips
Geoff		 http://www.oreilly.com/news/sqlnut_1200.html
SQL parameters
Geoff		 -- Accept parameter 1 on the sqlplus command line as in:
-- >@process.sql fred
define myparam = &1;

-- Use is in a select
select *
from users
where name = '&myparam';
Start Listener
Acacia		 To start the listener -

login as the oracle user
lsnrctl start
Start Oracle Instance
Acacia		 To start an Oracle instance -

login as the oracle user
cd $<DB>_HOME/pfile
svrmgr
connect / as sysdba
startup pfile=init<DB>.ora
Stored Procedure Example
Acacia		 See http://www.acacialt.com.au/acacia/Files/oracle_stored_proc_sample.txt for an example of an Oracle stored procedure.
Stored Procedure List
Acacia		 select object_name from user_objects where object_type = 'PROCEDURE';
TNSNAMES
Acacia		 TNSNAMES.ora can be found at $ORACLE_HOME/network/admin on Unix systems.  It contains connection details for SQLNet so that you can connect to local and remote databases.
X-Windows DBA Studio
Acacia		 To start an X-Windows session to Oracle Dba Studio -

@(xterm, method=stdappdb) -display @d&
export DISPLAY=10.0.3.103:0.0
oemapp dbastudio

Click here to add your own tips.