I saw the exception in the subject title of this post this earlier this week while applying the most recent PSU. Of course, the problem had absolutely nothing to do with whether or not the patch was applicable to the…
Category: Database
Exhausting the heap on a JDBC batch with huge size
This is to be expected, but I had never seen it. I increased the batch size on a JDBC batch insert to 100,000 (yes, go ahead and laugh :)), and hit the exception below. As I noted, it makes sense,…
dbms_stats and no_invalidate
Since at least 10.2.0, Oracle has decided by default when to invalidate a dependent cursor when query optimizer statistics are gathered. You can determine what setting you have by the following: SQL> select dbms_stats.get_param(‘NO_INVALIDATE’) from dual; DBMS_STATS.GET_PARAM(‘NO_INVALIDATE’) ——————————————————————————– DBMS_STATS.AUTO_INVALIDATE SQL>…
Oracle database encryption
Under normal conditions using an out of the box setup, Oracle database datafiles are stored in a proprietary format. Data such as strings of alphabetic characters can be viewed just as they are in the database by the naked eye…
JDBC and Oracle Database Change Notification
Below is a simple example of a cutdown example of using DCN in Oracle with JDBC. I plan on presenting this to our developers as an alternative to the ATG InventoryCache manager module. import java.sql.*; import java.util.*; import oracle.jdbc.*; import…
RMAN recover with backup taken before RESETLOGS
This functionality has been available since the early 10 release, but I am embarassed to say I have never tested it. Of course, you know where this is going. Last Friday evening, we took a full QA database backup. Over…
SQL Server example of a C# stored procedure
Below is a simple example of using CLR. Source code for procedure… using System.IO; using Microsoft.SqlServer.Server; public partial class spaceProc { [Microsoft.SqlServer.Server.SqlProcedure] public static void freeSpace() { foreach (DriveInfo drive in DriveInfo.GetDrives()) { if (drive.IsReady) { SqlPipe p = SqlContext.Pipe;…
TIMESTAMP in GV$SQL_BIND_CAPTURE
This has been an issue since at least 10.2.0.3, and appears to still be the case in 11.2.0.3, almost five years later. A TIMESTAMP datatype in GV$SQL_BIND_CAPTURE is not printed. Fortunately, the following works. select anydata.accesstimestamp(value_anydata) from v$sql_bind_capture where sql_id=’&your_sql_id’;
tcpdump to see Oracle errors
Not all exceptions are created equally, and most you can ignore (the one below you can, in general). However, if you have to troubleshoot on JBOSS (or anywhere a Linux application connects to an Oracle database), what is below is…
SQL Server MVCC with read_committed_snapshot
Oracle originally built its flagship database product with multi-version consistency control. Just as it sounds, multiple versions of a row can exist, albeit only one can exist as the current row version at any given time. It accomplishes this by…