Category: Oracle
By admin on Jun 20, 2011 in .NET, Oracle | Comments Off on .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 – http://www.oracle.com/technetwork/topics/dotnet/index-085163.html OraOps11w.dll Oracle.DataAccess.dll The following libraries from Instant Client (either one). Basic Lite should do for most of the cases. (http://www.oracle.com/technetwork/database/features/instant-client/index-097480.html Basic – oci.dll, orannzsbb11.dll, and oraociei11.dll Basic Lite – oci.dll, […]
By admin on Jan 29, 2010 in Oracle, Programming | 1 Comment
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 PartnerLink properties –> 5: </partnerLinkBinding> If the WebService is on the SAME host as […]
By admin on Jan 28, 2010 in Oracle, Programming | Comments Off on 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 10.1.3.5, the ora:toCDATA() is introduced […]
By admin on Nov 21, 2009 in Oracle | Comments Off on 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; […]
By admin on Oct 16, 2009 in open source, Oracle | Comments Off on 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 […]
By admin on Aug 31, 2009 in Oracle | Comments Off on 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 […]
By admin on Aug 30, 2009 in Oracle, side note | Comments Off on Oracle: Verify PGA and SGA
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’;
By admin on Aug 28, 2009 in Oracle, side note | Comments Off on 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))) || ‘ […]
By admin on Aug 3, 2009 in Oracle | Comments Off on 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’; […]
By admin on Apr 7, 2009 in Oracle | Comments Off on 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 […]
By admin on Mar 20, 2009 in Oracle | Comments Off on Oracle: Maximum Cursor Exceeded Problem
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 […]
By admin on Mar 11, 2009 in Oracle | Comments Off on Oracle: Partition Key is Important in Queries
The cost can be very high if you do not use it With partition key
By admin on Mar 10, 2009 in Oracle | Comments Off on 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’;
By admin on Feb 17, 2009 in Java, Oracle, Programming | Comments Off on 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 “-//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”> alter session set sort_area_size=#value# </update> <update id=”update_sort_area_retained_size” […]
By admin on Feb 14, 2009 in Oracle | Comments Off on 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 […]
By admin on Feb 12, 2009 in database, free ebook, general, Oracle | Comments Off on Free EBook: Expert Oracle JDBC Programming
From Apress at http://www.apress.com/resource/bookfile/1909
By admin on Aug 10, 2008 in Oracle, side note | Comments Off on 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 […]
By admin on Aug 4, 2008 in Oracle | Comments Off on 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 […]
By admin on May 29, 2008 in Java, Oracle, Programming | Comments Off on 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. 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 […]
By admin on Feb 3, 2008 in Oracle | Comments Off on 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 […]