Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Oracle SQL Articles

Oracle 9i

ANYDATA Type - This article presents an overview of the ANYDATA type.

ANSI ISO SQL Support In Oracle9i - Check out the new SQL enhancements to bring Oracle in line with the ANSI/ISO SQL: 1999 standards.

CASE Expressions And Statements - Learn how to use CASE expressions in both SQL and PL/SQL. In addition, learn how to use the CASE statement in PL/SQL.

DBMS_XPLAN - Easily format the output of an explain plan with this replacement for the utlxpls.sql script.

Flashback Query - Get a consistent view of the your data at a previous point in time.

MERGE Statement - Use the MERGE statement to quickly upsert data into tables.

Metadata API (DBMS_METADATA) - Extract DDL or XML definitions of all database objects using this simple API.

SQL New Features In Oracle9i - Check out the new SQL features with emphasis on those relevant for the Oracle9i Database: New Features For Administrators OCP exam.

SQL*Plus Web Reports - Generate HTML reports directly from SQL*Plus.

SQL/XML (SQLX) : Generating XML using SQL - Oracle9i Release 2 includes functionality to support the emerging SQL/XML standard to simplify XML generation from SQL queries.

XMLSEQUENCE - Use this operator to split multi-value results from XMLTYPE queries into multiple rows.

XMLType Datatype - Store XML documents in tables and query them using SQL.

Oracle 10g

Automatic SQL Tuning in Oracle Database 10g - This article the discusses the new features which automate the tuning of SQL statements in Oracle 10g.

Commit Enhancements in Oracle 10g Database Release 2 - Use the WRITE clause of the COMMIT command or the COMMIT_WRITE parameter to influence the way redo information is processed by commit operations.

DML Error Logging in Oracle 10g Database Release 2 - Use this new feature to capture errors and allow DML operations to complete successfully.

MERGE Statement Enhancements in Oracle Database 10g - Learn about the enhancements to the merge statement in Oracle Database 10g.

Flashback New Features and Enhancements in Oracle Database 10g - Use the new and enhanced features of flashback technology to view old data, track data changes and recover from accidental data loss without reverting to LogMiner or point in time recoveries.

SQL Access Advisor in Oracle Database 10g - Use this advisor to get suggestions for indexes and materialized views that might help system performance.

SQL*Plus Enhancements in Oracle Database 10g - Check out enhancements to SQL*Plus in Oracle Database 10g.

SQL trace, 10046, trcsess and tkprof in Oracle - An article that combines all previous SQL Trace, event 10046 and tkprof information, along with information on trcsess and DBMS_MONITOR from Oracle 10g Onward.

XMLTABLE : Query XML Data From SQL - Use the XMLTABLE operator to project columns on to XML data, allowing you to query it directly from SQL.

Oracle 11g

Automatic SQL Tuning in Oracle Database 11g Release 1 - Take advantage of the Automatic SQL Tuning features of Oracle 11g Release 1.

DDL With the WAIT Option (DDL_LOCK_TIMEOUT) - Avoid unnecessary "ORA-00054: resource busy" errors in 11g.

Enhanced Finer Grained Dependency Management in Oracle Database 11g Release 1 - See how finer grained dependency management in 11g reduces the extent of invalidations associated with schema changes.

Invisible Indexes in Oracle Database 11g Release 1 - Take control of the indexes available to the optimizer by using invisible indexes in 11g.

Miscellaneous New Features in Oracle Database 11g Release 1 - A collection of new features and enhancements listed in the miscellaneous section of

PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1 - Learn how to use the new PIVOT and UNPIVOT operators in Oracle Database 11g Release 1.

Query Result Cache in Oracle Database 11g Release 1 - Improve the performance of SQL across the whole database instance by caching query results.

Real-Time SQL Monitoring using DBMS_SQLTUNE (REPORT_SQL_MONITOR, REPORT_SQL_MONITOR_LIST and REPORT_SQL_DETAIL) - Monitor the performance of SQL queries while the are running.

SQL Access Advisor in Oracle Database 11g Release 1 - Use this advisor to get suggestions for indexes, materialized views and partitioning schemes to improve system performance.

SQL Performance Analyzer in Oracle Database 11g Release 1 - Compare the performance of the statements in an SQL tuning set before and after database changes.

SQL Plan Management in Oracle Database 11g Release 1 - Maintain consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.

UTL_MATCH : String Matching by Testing Levels of Similarity/Difference - Use the UTL_MATCH package to determine the similarity between two strings.

Oracle 12c

Code Based Access Control (CBAC) : Granting Roles to PL/SQL Program Units in Oracle Database 12 Release 1 (12.1) - Discover how Oracle Database 12c allows you to grant roles directly to PL/SQL program units.

Control Invoker Rights Privileges for PL/SQL Code in Oracle Database 12c Release 1 (12.1) (INHERIT [ANY] PRIVILEGES) - Learn how to plug security holes related to invoker rights PL/SQL code in Oracle Database 12c Release 1.

Control Invoker Rights Privileges in Views in Oracle Database 12c Release 1 (12.1) (BEQUEATH CURRENT_USER) - This article describes support for invoker rights function calls in views in Oracle Database 12c Release 1.

Data Redaction (DBMS_REDACT) in Oracle Database 12c Release 1 (12.1) - Use data redaction to protect sensitive data in Oracle Database 12c Release 1 (12.1).

JSON Support in Oracle Database 12c Release 1 (12.1.0.2) - This article describes the server side support for JSON in the database.

Indexing JSON Data in Oracle Database 12c Release 1 (12.1.0.2) - This article provides an overview of how to index JSON data in Oracle Database 12c Release 1 (12.1.0.2).

Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c Release 1 (12.1) - Learn about the new pattern matching analytic function clause available in Oracle 12c onward.

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1) - Simplify the syntax of Top-N queries using the row limiting clause of Oracle Database 12c.

WITH Clause Enhancements in Oracle Database 12c Release 1 (12.1) - Define PL/SQL functions and procedures in the WITH clause of SQL statements.

Multitenant : PDB CONTAINERS Clause in Oracle Database 12c Release 1 (12.1.0.2) - The PDB CONTAINERS clause allows data to be queried across multiple PDBs in Oracle Database 12c Release 1 (12.1.0.2).

PL/SQL-to-SQL Interface Enhancements for PL/SQL-Only Data Types in Oracle Database 12c Release 1 (12.1) - Oracle 12c now supports the binding of additional PL/SQL-only data types to SQL.

Temporal Validity in Oracle Database 12c Release 1 (12.1) - Make querying of effective date ranges simpler using temporal validity in Oracle database 12c.

Miscellaneous

ALL, ANY and SOME Comparison Conditions in SQL - A quick look at some comparison conditions you may not be used to seeing in SQL against an Oracle database.

Analytic Functions - An introduction to analytic functions in Oracle.

APPEND Hint - Use the APPEND hint to improve the performance of load operations.

Deadlocks - This article shows the steps necessary to identify the offending application code when a deadlock is detected.

DML RETURNING INTO Clause - This article describes the use of the RETURNING INTO clause in DML.

Dynamic IN-Lists - This article presents a number of methods for parameterizing the IN-list of a query.

Efficient Function Calls From SQL - This article presents a number of solutions for making calls to PL/SQL functions from SQL more efficient.

Efficient SQL Statements - A brief non-version specific guide to writing efficient SQL statements.

FIRST_VALUE and LAST_VALUE Analytic Functions - Simple examples of how to use these analytic functions.

Identifying Host Names and IP Addresses - This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

Join Elimination - This article describes how join elimination can improve performance of queries by removing tables from the plan.

LAG and LEAD Analytic Functions - Simple examples of how to use these analytic functions.

Literals, Substitution Variables and Bind Variables - The article compares the affect of using literals, substitution variables and bind variables on memory and CPU usage.

Naming Conventions - Some of the typical naming conventions used for objects in Designer and the database.

NULL-Related Functions (NVL, DECODE, NVL2, COALESCE, NULLIF, LNNVL, NANVL, SYS_OP_MAP_NONNULL) - A summary of the functions available for handling NULL values.

Oracle Dates, Timestamps and Intervals - An overview of the usage of dates, timestamps and intervals in Oracle databases.

Overlapping Date Ranges - This article presents simple methods to test for overlapping date ranges.

RANK, DENSE_RANK, FIRST and LAST Analytic Functions - Simple examples of how to use these analytic functions.

Recompiling Invalid Schema Objects - This article presents several methods for recompiling invalid schema objects.

Regular Expression Support in Oracle (REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_LIKE) - Using regular expressions to solve some questions I've been asked over the years.

ROLLUP, CUBE, GROUPING Functions and GROUPING SETS - An overview of some functionality available for aggregation in data warehouses.

Schema Owners and Application Users - Protect your schema objects by defining application users with the necessary privileges on those objects, rather than allowing direct connections to the schema owner.

String Aggregation Techniques - Several methods of combining multiple rows of data into a single row using aggregate functions.

Top-N Queries - Top-N queries provide a method for limiting the number of rows returned from ordered sets of data.

Using Ref Cursors To Return Recordsets - Return recordsets from Oracle stored procedures.

Virtual Indexes - Use virtual indexes to test alternate indexing strategies with no impact on the rest of your system.

WITH Clause - Use the WITH clause to reduce repetition and simplify complex SQL statements.