David Fitzjarrell

Interesting questions can be posted in the Oracle database forums and one recent post generated a great deal of attention. A query and part of the execution plan were posted with the poster asking how to get rid of the table scan. The table has a primary key index so, on the face of it, […]

Oracle takes a rather simplistic view of the cost of deletes, which in most cases is fine. Small deletes, of say 1000 rows or less, really have no appreciable additional costs; larger deletes however can have additional overhead that isn’t taken into account by the optimizer when plans are generated. Let’s look at an example […]

Recovering dropped tables is an ongoing issue with any database engine, but Oracle has provided a method of restoring a table or a table partition from an RMAN backup that makes such recovery pretty convenient. Yes, there’s flashback database and the recycle bin, but what if flashback database isn’t configured and the recycle bin isn’t […]

Partitions can be a wonderful feature in Oracle; they allow faster access to data through partition pruning and they allow for easier archiving and removal of old data. There’s not much that can go wrong with partitions, until you want to create an interval-partitioned table with hash subpartitions and you have a situation where you […]

Security is on everyone’s mind, especially database administrators, and some DBMS vendors supply tools and/or settings, which allow the database to monitor and report possibly suspicious activity. Oracle is no different, yet one setting can be particularly distressing by creating Denial of Service (DoS) attacks from within. Let’s look at all of the supplied security […]

Defining unique records in an Oracle table is necessary for employee data, manufacturing part numbers and user names, to name a few examples. There are several ways this can be accomplished in the database but one of these methods may fail for purchased applications that use the data dictionary to verify such a constraint is […]

Ever since Oracle provided their version of ANSI syntax it’s been improving with each release. Unfortunately some steps forward also take steps backwards. A case in point is the following example, brought to my attention by Jonathan Lewis. Let’s look at the queries and see where Oracle has gone afoul of things. The queries seem […]

A “blast from the past” was recently updated by Oracle Support; it involves a subpool memory leak in Oracle 11.2.0.1 and shared cursors. Originally submitted in 2010 this issue was updated this year to reflect the status of ‘Fixed in Product Version 12.1’. There have been several bug fixes to the 11.2.0 series of releases […]

Oracle 11.2.0.3 can still throw a curveball when it comes to LOB columns, notably with the SQL*Loader utility. Under the right conditions SQL*Loader can fail to load a table, throwing an ORA-08102. Let’s look at the issue to see what configuration can create that result. LOB columns are interesting all by themselves due to the […]

Edition-based Redefinition can be a real asset when modifying existing application code such as procedures, packages and functions but it can also throw a monkey wrench into the works when invalid objects rear their ugly heads, as ORA-00600 errors can be thrown in Oracle release 12.1.0.2: ERROR at line 1: ORA-4045: errors during recompilation/revalidation of […]

An interesting issue can reveal itself in Oracle 12.1.0.2 when importing a list partitioned table using Data Pump: ORA-39083: Object type TABLE:

Database backups are a necessary part of a robust management strategy, and ensuring the success of such processes is key to providing reliable disaster recovery operations. Several media choices are available for such backups, which can involve third-party tools such as NetBackup and Tivoli. Unfortunately it appears that RMAN backups may ‘miscommunicate’ with Tivoli providing […]