twit88.com

Starting version 10.1.3.4, to set time out in seconds for a partner link, it is just configuration in bpel.xml.
E.g. the configuration below sets the timeout to 5 seconds

1: <partnerLinkBinding name="WebService"> 

2:   <property name="timeout">5</property>  

3: <property name="optSoapShortcut">false</property>   

4: <!– other […]

In Oracle BPEL, to add a CDATA section to a XML payload looks like not easy.
E.g. if you want to add the CDATA to the XML below, it is not easy as using Java

1: <?xml version = ‘1.0′ encoding = ‘UTF-8′?>

2: <tns:RunTask><xmlRequestDocument><![CDATA[test

3: data]]></xmlRequestDocument></tns:RunTask>

As of version 10.1.3.5, […]

Here are some general guidelines for the order of index fields in Oracle.
The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.

If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries […]

Hammerora is a load generation tool for Oracle 8i,9i,10g and 11g, MySQL and web applications on Linux/UNIX and Windows written in TCL/TK released under the GNU Public License.  Therefore with Hammerora there is no expensive per "Virtual User" licensing. You can create the desired number of sessions that your hardware will sustain.
Hammerora has been designed […]

The Automatic Database Diagnostic Monitor ( ADDM ) is an advisor which detects problem area’ s in the database and and which gives recommendations. ADDM uses the statistical data from the AWR - Automatic Workload Repository - stored in the sysaux tablespace. By default the statistical data is gathered / written to disk by snapshots […]

For current PGA size of each session, use:
SELECT s.sid, n.name, s.value FROM v$sesstat s, v$statname nWHERE s.statistic# = n.statistic#AND n.name = ’session pga memory’;
For maximum PGA size each session has reached so far, use:
SELECT s.sid, n.name, s.value FROM v$sesstat s, v$statname nWHERE s.statistic# = n.statistic#AND n.name = ’session pga memory max’;

1: SELECT to_char(startup_time,‘DD-MON-YYYY HH24:MI:SS’) “DB Startup Time”
2: FROM sys.v_$instance;

1: select
2: ‘Hostname : ‘ || host_name
3: ,‘Instance Name : ‘ || instance_name
4: ,‘Started At : ‘ || to_char(startup_time,‘DD-MON-YYYY HH24:MI:SS’) […]

You can set a diagnostic event for the ORA-1652 error in your individual database session with the following statement:

ALTER SESSION SET EVENTS ‘1652 trace name errorstack’;

You can set a diagnostic event for the ORA-1652 error in your individual database session with the following statement:

ALTER SYSTEM SET EVENTS ‘1652 trace name errorstack’;
 

Sort space usage by sessions

SELECT […]

It is clearly documented in Oracle documentation that DBMS_STATS should be used.
Do not use the COMPUTE and ESTIMATE clauses of ANALYZE to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics […]

Here are some useful SQLs to help resolve maximum cursor exceeded problem.
The SQLs are from http://www.orafaq.com/node/758
Monitor Open Cursor

–total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = ‘opened cursors current’;

Monitor By User Name and Machine

–total cursors open, by username & machine
select sum(a.value) […]

The cost can be very high if you do not use it

With partition key

Useful queries at times to resolve locked objeccts in Oracle
To show lock,

select O.object_name,
O.object_type,
S.sid,
S.serial#,
L.oracle_username,
L.os_user_name,
L.locked_mode
from dba_objects O,
v$locked_object L,
v$session S
where O.object_id = L.object_id
and S.sid = L.session_id

To unlock,

ALTER SYSTEM KILL SESSION ’sid, serialid’;

There are times that you may want to alter Oracle session information. If you are using iBATIS, this can be done the following way
Create the update statement. E.g.

<?xml version=“1.0″ encoding=“UTF-8″ ?>
<!DOCTYPE sqlMap PUBLIC “-//ibatis.apache.org//DTD SQL Map 2.0//EN” “http://ibatis.apache.org/dtd/sql-map-2.dtd” >
<sqlMap namespace=“TEST”>

<update id=“update_sort_area_size” parameterClass=“int”>
[…]

This is a problem I encountered with Oracle 10g.
I have a connection pool defined, and when I used the connection pool to retrieve large number of records from a table repeatedly, eventually TEMP tablespace is full. The space is never released until I terminate the Java program.
While reading the Oracle documentation on memory architecture, it […]

From Apress at http://www.apress.com/resource/bookfile/1909

@ and @@
The @ (at symbol) is equivalent to the START command and is used to run SQL*Plus command scripts. A single @ symbol runs a script in the current directory (or one specified with a full or relative path, or one that is found in you SQLPATH or ORACLE_PATH). @@ will start a sqlplus […]

Whenever an Oracle user is created, the default profile is DEFAULT is none is specified. To set the login attempts to unlimited, run the following to change the DEFAULT profile.

alter profile DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Make the password unavailable for 90 days

ALTER PROFILE new_profile
LIMIT PASSWORD_REUSE_TIME 90
PASSWORD_REUSE_MAX UNLIMITED;

Set default […]

This is something you might have overlooked if you are using Oracle JDBC Driver.
Prior to Oracle 10g, the syntax for the JDBC URL looks like below

jdbc:oracle:<driver_type>:@hostname:<;listener-port>:<SID>

E.g.

jdbc:oracle:thin:@db.oracle.com:1521:mydb
 

However, there is a special THIN style service name syntax supported with only THIN JDBC driver that was introduced in 10g JDBC driver

jdbc:oracle:thin:@//host_name:port_number/service_name

Here is an example,

jdbc:oracle:thin:scott/tiger@//myhost:1521/myservicename

The format […]

I was trying to restart my Oracle 10g database server. Oops, the database server can be be started because the spfile is corrupted. Someone must have edited it using a text editor…
I cannnot use the alter system command to regenerate the spfile since the database is not running. What can I do ??
After some trials […]

An interesting finding…

create table test_table
(
col_1 number
)
compress;

alter table test_table add (col_2 number);

insert into test_table values(1,2);

After this, try to drop one column

alter table test_table drop column col_2;

ORA-39726: unsupported add/drop column operation on compressed tables

This is expected as the table is still compressed.

alter table test_table nocompress;

alter table test_table drop column col_2;

ORA-39726: unsupported add/drop column operation on compressed tables

Even […]