Category: Oracle
-
NVL and DECODE: Lazy vs Eager Evaluation
The blog post discusses the behaviors of Oracle’s NVL and DECODE functions regarding NULL values. While NVL employs eager evaluation, executing all arguments regardless of necessity, DECODE uses lazy evaluation, only evaluating the second argument when needed. This difference can lead to performance issues or unexpected errors.
-
Common SQL and PL/SQL Pitfalls: Insights from Real-World Experience
The content highlights key Oracle SQL and PL/SQL behaviors that, while seemingly simple, often result in unexpected issues in production environments. It emphasizes the importance of revisiting foundational topics to avoid bugs and performance problems. Through a series of blog posts, real-world examples are shared to assist others and enhance understanding of crucial functionalities, aiming to support the Oracle community effectively.
-
Oracle Hash Partitioning : Benefits and Best Practices
Hash partitioning enhances database management and performance by evenly distributing data across partitions, crucial for handling high concurrency. However, choosing an appropriate partition key is vital to avoid skewed data distribution. Proper use of partition keys allows for efficient query performance and reduces contention in both single-instance and RAC environments.
-
DBMS_METADATA.GET_DDL Does Not Return Hashed Passwords Anymore
Recent Oracle Data Pump patches (19.17 and later, involving bug fix 33517865) have modified the behavior of DBMS_METADATA.GET_DDL, removing hashed passwords from its output as a security enhancement. Previously, users with the EXP_FULL_DATABASE role could easily access this data. Now, even with that privilege, hashed passwords are inaccessible unless explicit access to SYS.USER$ is granted. Oracle has introduced a fix (bug fix 35018026) that allows access to hashed passwords under strict conditions. Alternatively, a definer-rights PL/SQL function can be used to provide controlled access to this information.
-
Ansible Playbooks for Oracle Grid and Database Release Update (19.26 with MRP & One-Off Fixes)
In this post, I’m sharing the latest Ansible playbooks for patching Oracle Database and Grid Infrastructure, specifically for the Oracle 19.26 Release Update, including MRPs and recommended one-off fixes. Ansible simplifies the patching process, providing repeatability and ease.
-
Disabling the Oracle Compression Advisor
A recent incident regarding tablespace usage occurred during the daily maintenance window, leading to an ORA-1652 error. The issue stemmed from the Compression Advisor creating large CMP3$ tables, causing space pressure in the USERS_XXX tablespace. The problem resolved itself once the advisor completed, freeing up space. With Oracle 12.2+, users can now disable the Compression Advisor specifically without turning off the Segment Space Advisor, offering better management options and addressing similar future issues effectively.
Osman’s DBlog 