Category: Oracle
-
Checking for NOT NULL Values in a BLOB Column
This post discusses the ‘ORA-01405: fetched column value is NULL’ error encountered in a query that executes a function where NULL values are passed as input. The initial solution was to add a NOT NULL filter, but it did not resolve the issue. However, adding the suggested DBMS_LOB.GET_LENGTH() function to filter out NULL BLOB columns ultimately solved the problem.
-
Moving Oracle Database Objects to Another Tablespace in 19c (Table, Index, LOB Column)
The post discusses the process of moving and changing nearly 50,000 database objects (tables, indexes, LOB columns) across tablespaces within an Oracle database. It highlights the challenges posed by inconsistent storage practices and the transition to a more structured approach based on object types and applications to improve performance and management. Key considerations, such as moving tables, indexes, and LOBs, are detailed, along with specific commands for various operations. The importance of strategic planning and utilizing Oracle features to minimize downtime and ensure data integrity during the transition is also emphasized.
-
Oracle Database 19c APEX Upgrade: 19.2 to 24.1.7
This post outlines the process for upgrading Oracle Application Express (APEX) from version 19.2 to 24.1. APEX is a low-code platform for developing web applications with Oracle Database. The upgrade process is independent of the database version but requires Oracle Database version 19c or later. The steps include downloading the new version, creating dedicated tablespaces, executing installation scripts, modifying request-handling procedures, and dropping the old APEX user and tablespace. Finally, the latest cumulative patch, released on December 9, 2024, is applied to ensure a secure, reliable, and efficient APEX environment.
-
Oracle Incrementally Updated Backups : Detect and Delete Orphaned (Expired) Datafile Copies
Oracle recommends adopting a merged incremental disk backup strategy to ensure efficient database protection and faster recovery times. This includes keeping an up-to-date image copy as the base while applying incremental backups. Key practices involve setting a REDUNDANCY 1 retention policy, utilizing the Fast Recovery Area, and employing Block Change Tracking for efficiency. DBAs must manage leftover datafiles after tablespace deletion to ensure that the base image copy remains in sync with the database, as detailed throughout the blog post.
-
CPU-intensive query to simulate CPU load in Oracle database
This blog post provides a PL/SQL script that utilizes Oracle’s DBMS_SCHEDULER to create and execute CPU-bound jobs. Also validates the CPU usage with AWR Report and docker stats command. Additionally, it addresses constraints of the Oracle Database 23ai Free version, limiting CPU usage to two cores, despite available resources.
-
Finding Row Counts for All Tables in Oracle Database
This post explores the debate between using table statistics and executing SELECT COUNT(*) queries for retrieving row counts for all tables in Oracle databases. It highlights the efficiency of relying on table statistics, which require minimal buffer gets (20–29) compared to the substantial overhead of directly counting rows, as observed in the test case with buffer gets reaching up to 492,252. Online statistics(12c) and Real-Time Statistics(19c) features are also highlighted. Overall, it recommends only using table statistics for row count retrieval when querying all tables in the database or schema.
Osman’s DBlog 