Oracle11g

Deprecated SQL*Plus Commands in Oracle 11g R2

This just a short note to point out that the below SQL*Plus commands are deprecated as of Oracle 11g R2, but available in the current release of SQL*Plus for backward compatibility. The documentation states that this may only be available by setting the SQLPLUSCOMPATIBILITY variable in the future releases.  You should modify scripts using obsolete …

Deprecated SQL*Plus Commands in Oracle 11g R2Read More »

RECORD feature of TKPROF

This is just a short note the point out that RECORD option of TKPROF is extremely useful in recording and capturing the non-recursive SQL statements in the order of execution from the SQL trace file.  I have not had a requirement to use it until—today—one of my developers lost the original test script used when …

RECORD feature of TKPROFRead More »

SQL*Plus Feature: TIMING and SET TIMING command

SET TIMING—one of the most frequently used commands—displays timing statistics on each SQL command or PL/SQL block. What if you want to collect timing statistics for multiple group of statements? TIMING command becomes handy in such situation—one of things that I knew and have forgotten over time. It can be used to collect data to do …

SQL*Plus Feature: TIMING and SET TIMING commandRead More »

SQL returns wrong result when the table order is swapped

I noticed a strange behavior—a SQL statement, which used to return no rows in the past, started returning rows in an environment where the test data set has not changed. I performed a code comparison and observed a difference in the order of a table in FROM clause. After looking through, I found a bug entry on MOS …

SQL returns wrong result when the table order is swappedRead More »

Oracle Database 11g: Interactive Quick Reference, Your Essential Guide to Oracle Database 11g Release 2

Last fall, I posted a note about Oracle Database 11g Architecture and Background Processes. This post is just a short note to point out that last week, Oracle University has made Oracle Database 11g Interactive Quick Reference available to public for download; it is a flash application, available for offline use in compressed file (.ZIP) …

Oracle Database 11g: Interactive Quick Reference, Your Essential Guide to Oracle Database 11g Release 2Read More »

Enabling and Disabling Database Options

This is just a short note to point out that starting from Oracle database 11g Release 2, a command line utility, chopt (change option) is available to enable or disable a particular database feature; the tool is located under ORACLE_HOME/bin directory.  Currently, you can enable or disable the following features using this utility: Oracle Data Mining RDBMS Files, …

Enabling and Disabling Database OptionsRead More »

Execution Plan Operations and Options

The row source tree is the core of the execution plan. It contains the sequence of operations that the database performs to run the statement; each operation may have options associated with it. The operation in execution plan is also known as row source operator.  If you look at the below example, TABLE ACCESS operation …

Execution Plan Operations and OptionsRead More »

OVERLAPS predicate

OVERLAPS predicate is a useful feature, which is not available in most SQL implementations that can help to determine whether the two periods overlap each other. If two periods overlap then the predicate evaluates to TRUE otherwise FALSE. Let us create a test table with three rows – one with overlapping dates, second with non-overlapping …

OVERLAPS predicateRead More »

Removing table rows using an inline view

The SQL standard allows you to use an inline view in a DELETE statement, see the pseudo form below – If a condition such as key-preserved table is not met then it will result in error, “ORA-01752: cannot delete from view without exactly one key-preserved table.” I would like to show you the caveat of …

Removing table rows using an inline viewRead More »

Joins, Join Conditions, Filters and Join Types

Joins A join is a query that combines rows from two or more tables. Oracle performs a join whenever multiple tables appear in the FROM clause of the query. Below is a SQL join statement, legacy syntax and ANSI/ISO syntax (also referred as new join syntax, was first introduced in Oracle 9i release) in a …

Joins, Join Conditions, Filters and Join TypesRead More »