RSS Feed for OracleCategory: Oracle

.NET C#: Deploy Application Without Oracle Client Installed »

You can deploy your database application using Oracle without installing the Oracle client. What you will need is The following libraries from ODP.NET – OraOps11w.dll Oracle.DataAccess.dll The following libraries from Instant Client  (either one). Basic Lite should do for most of the cases. ( Basic – oci.dll, orannzsbb11.dll, and oraociei11.dll Basic Lite – oci.dll, […]

Oracle BPEL: Partner Link Timeout »

Starting version, 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 PartnerLink properties –> 5: </partnerLinkBinding> If  the WebService is on the SAME host as […]

Oracle BPEL: Adding CDATA Section to XML Payload »

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, the ora:toCDATA() is introduced […]

Oracle: Order of Index Fields »

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; […]

Open Source Oracle Load Test Tool »

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 […]

Oracle: Using ADDM »

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 […]

Oracle: Verify PGA and SGA »

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

Oracle: Detect Server Uptime »

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’) stime 5: ,’Uptime : ‘ || floor(sysdate – startup_time) || ‘ days(s) ‘ || 6: trunc( 24*((sysdate-startup_time) – 7: trunc(sysdate-startup_time))) || ‘ […]

Oracle: Tracing ORA Error »

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’;   […]

Orace: Analyze versus DBMS_STATS »

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 […]

Oracle: Maximum Cursor Exceeded Problem »

Here are some useful SQLs to help resolve maximum cursor exceeded problem. The SQLs are from 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 = ‘opened cursors current’; Monitor By User Name and Machine […]

Oracle: Partition Key is Important in Queries »

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

Oracle: Detect Locked Objects »

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

Java: Alter Oracle Session Information using iBATIS »

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 “-// SQL Map 2.0//EN” “” > <sqlMap namespace=”TEST”> <update id=”update_sort_area_size” parameterClass=”int”> alter session set sort_area_size=#value# </update>   <update id=”update_sort_area_retained_size” […]

Oracle TEMP Tablespace Full When Query Returning Large Results »

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 […]

Free EBook: Expert Oracle JDBC Programming »

From Apress at

Oracle SQLPlus Note »

@ 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 […]

Oracle: Alter Password Policy »

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 password values […]

New JDBC URL Syntax for Oracle 10g JDBC Driver »

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.   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 […]

Oracle: Oops, my spfile is corrupted !! »

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 […]