Databases@Powered by Passion
This post illustrates how to use AutoUpgrade to patch an Oracle Database to in environments without internet access, making it also suitable for isolated systems. It details steps such as creating necessary directories, copying setup files, running prechecks, applying patches, and performing post-upgrade operations. The AutoUpgrade utility automates many tasks traditionally handled manually by DBAs.
In this blog post, I walk you through how to download Oracle patches using AutoUpgrade on a Windows host behind a proxy requiring authentication. The steps include configuring proxy environment variables, creating the necessary directory structure, importing SSL certificates into the Java truststore and setting up a keystore for My Oracle Support (MOS) credentials. Additionally, I share a workaround for a language-specific issue encountered when the Windows display language is set to Turkish, which will be fixed on the next release.
The post explores hidden transformations and transltion framework in Oracle databases. It introduces tools like DBMS_SQL_TRANSLATOR.SQL_ID and DBMS_UTILITY.EXPAND_SQL_TEXT, which help analyze and uncover underlying operations, highlighting the intricate nature of database queries and their optimizations.
The use of LEFT JOIN in SQL queries is common but often misapplied. This post highlights two pitfalls: using LEFT JOIN when INNER JOIN is appropriate, and applying filter conditions incorrectly. Proper usage of joins enhances clarity and performance for optimizers.
SQL aggregation queries behave differently with and without a GROUP BY clause, particularly when no rows match. A query with GROUP BY returns no rows, while without it, a NULL value appears. To align results with expectations, using COALESCE() can return 0 instead of NULL for sums in such cases.
Many Oracle developers overlook that a PL/SQL function’s SELECT INTO statement will raise a NO_DATA_FOUND exception in PL/SQL but silently return NULL in SQL when no rows are found. This difference can lead to logic bugs. Understanding this behavior is essential for developing robust code and avoiding debugging challenges.
Osman’s DBlog 