Osman’s DBlog

Moving Oracle Database Objects to Another Tablespace in 19c (Table, Index, LOB Column)

3 Jan 2025 23:47

—

by

Osman DİNÇ

Databases, Oracle

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

21 Dec 2024 17:31

—

by

Osman DİNÇ

Databases, Oracle, Update

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

15 Dec 2024 21:16

—

by

Osman DİNÇ

Backup, Databases, Oracle

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 PostgreSQL database

13 Dec 2024 21:48

—

by

Osman DİNÇ

Databases, PostgreSQL

This blog post provides a PL/pgSQL script that utilizes pg_background extension to create and execute CPU-bound background worker processes. Also validates the CPU usage with docker stats.
CPU-intensive query to simulate CPU load in Oracle database

13 Dec 2024 21:45

—

by

Osman DİNÇ

Databases, Oracle, Performance

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

7 Dec 2024 13:47

—

by

Osman DİNÇ

Databases, Oracle, Performance

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.