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 [...]
Apr 042011
Earlier I posted a list of operations and options available in Oracle database as of 11g R2, you should note that not all options are available for each operation. This post covers, over 200, commonly observed row source operations—unique combination of operations and options—each post contains a sample script to produce the operation and some may contain hints and [...]
Mar 152010
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 [...]
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 [...]
The initialization parameter REMOTE_OS_AUTHENT offers the trusted authentication model to the network, which is users can have OS accounts on machines other than the database server and gain access to database, convenience of single sign-on through remote OS authentication
OERR stands for Oracle Error, which is a utility ships with Oracle distribution for Linux and UNIX that helps retrieve messages from message (.msg) files based on the supplied error code. This utility is not available for Windows, but there are variants freely available on the Internet.
Jul 022009
PLAN_TABLE is the default sample output table into which the EXPLAIN PLAN statement inserts rows describing execution plans. This post summarizes the metadata information of this table for Oracle8/8i, 9i, 10g, and 11g; read post, to know the methods for obtaining the formatted explain plan output from plan_table. Column Datatype Description Notes STATEMENT_ID VARCHAR2(30) Value [...]
Statspack is a set of SQL, PL/SQL and SQL*Plus scripts which allow the collection, automation, storage and viewing of performance data. A user, PERFSTAT, is automatically created by the installation; owns all objects needed by this package. This user is granted limited query-only privileges on the V$views required for performance tuning. A Statspack report is generated from two snapshots, which is nothing but a collection of contents from various dynamic performance tables.
The DBMS_PROFILER package provides an interface to profile existing PL/SQL applications and identify performance bottlenecks. You can then collect and persistently store the PL/SQL profiler data.
You can have multiple instances on the same machine, each with their own data files, either sharing the ORACLE_HOME or each with different ORACLE_HOME. ORACLE_HOME and ORACLE_SID are the key environment variables used by Oracle to identify an instance. In addition $ORACLE_HOME/bin must be in your PATH environment variable. To check the value of these [...]