Databases@Powered by Passion

  • alter index enable

    ORA-30554 errors on Function-Based Spatial Indexes after Spatial Bundle Patch

    —

    by

    Osman DİNÇ
    ,

    After applying the Spatial Bundle Patch, ORA-30554 errors may occur on functional indexes that use spatial operators. Functional indexes provide the benefit of spatial indexing but can become disabled after changes to dependent functions. Specific commands are required to re-enable them. This blog post provides details on handling spatial operations, managing indexes, and troubleshooting issues that arise after updates.

  • Internal flashback query fails

    ORA-00942 Errors from Wnnn Slave Processes working on Behalf of FBDA: Solutions Provided

    —

    by

    Osman DİNÇ
    , ,

    This post discusses troubleshooting ORA-00942 errors from Wnnn Processes working on Behalf of FBDA (Flashback Data Archive) process. By enabling error tracing, numerous ORA-00942 entries appeared in the alert log. The focus is on the relationship between Wnnn slave processes and Flashback Data Archive management, highlighting that untriggered archive history table creation leads to parsing failures. The absence of SYS_FBA_* tables due to lack of DML operations led to failed parsing attempts by FBDA, adversely affecting performance metrics. Two solutions are suggested: reconsidering Flashback Data Archive necessity for these tables or triggering the creation of SYS_FBA_* tables through minor modifications.

  • Ansible Oracle Database RU 24

    Automate Oracle Database Patching with Ansible Playbooks – 19.24

    —

    by

    Osman DİNÇ
    , ,

    This post discusses the introduction of a new patching feature (one-button patching) in the AutoUpgrade tool, now at version 24.7, which simplifies the database release update process. Although currently, it only supports single-instance homes and Oracle Database 19c on Linux, the Oracle Database Upgrade Team will be addressing additional requirements in future versions. Since automating patching is essential for large environments to ensure updates and compliance, I have provided a comprehensive set of Ansible playbooks for database patching(19.24) on my GitHub repo, which can help promote efficiency and reduce errors in the patching process.

  • SYSAUX Tablespace Growth Analysis

    SYSAUX Tablespace Growing Rapidly due to SCHEDULER$_JOB_OUTPUT Table and ORA-01031 Error in Automatic segment advisor task

    —

    by

    Osman DİNÇ
    ,

    The SYSAUX tablespace unexpectedly grew, primarily due to the SYS.SCHEDULER$_JOB_OUTPUT table, which consumed 14 GB. The V$SYSAUX_OCCUPANTS view was utilized to identify space usage issues, revealing excessive data generated from the DBMS_OUTPUT.PUT_LINE command in a loop. After performing purge and truncate operations, log records generated with DBMS_OUTPUT.PUT_LINE ceased; however, a log record related to the Automatic Segment Advisor task showed that insufficient privileges (ORA-01031) occurred daily. Granting explicit CREATE TABLE permission resolved the issue, ensuring the proper functionality of the Automatic Segment Advisor.

  • ORA-13029

    A 3D data with a 2D SRID may result with ORA-13029 #JoelKallmanDay

    —

    by

    Osman DİNÇ
    , ,

    This post discusses the importance of correctly storing 3D geometry with an appropriate 3D Spatial Reference Identifiers (SRIDs) following Oracle’s upgrade to version 19c. It details the ORA-13029 error encountered when a 3D data stored with a 2D SRID and provides solutions, including applying patch 31022826 and setting event 54713. Alternatively, it suggests updating metadata with an equivalent 3D SRID and recreating the spatial index as another solution.

  • Send email using notify and listen in PostgreSQL

    How to send mail from PostgreSQL database

    —

    by

    Osman DİNÇ
    ,

    This content discusses methods for sending emails directly from a PostgreSQL database. Three approaches are highlighted: using PostgreSQL extensions like pgMail or pgSmtp, creating custom stored procedures with untrusted languages, and employing the LISTEN/NOTIFY mechanism to communicate with external services. The LISTEN/NOTIFY method is implemented in detailed steps: configuring Postfix for email delivery, creating a PostgreSQL table for email records, setting up a trigger, writing a Python listener, creating a systemd service, and managing logs. This solution provides a reliable method for managing emails directly from the database.